All Questions
Tagged with postgresql-performance join
13 questions
0
votes
2
answers
119
views
Optimize query to get rows with highest, filtered count in another table
I'm trying to create the most optimal query where the database would return the names of readers who often borrow sci-fi books. That's what I'm trying to optimize:
SELECT reader.name,
COUNT (CASE ...
- 83
1
vote
1
answer
67
views
Best way to get distinct count from a query joining two tables (multiple join possibilities)
I have 2 tables, table Actions & table Users. Actions -> Users is many-one association.
Table Actions (has thousands of rows)
id
uuid
name
type
created_by
org_id
Table Users (has a max ...
- 291
1
vote
3
answers
2k
views
Best way to get distinct count from a query joining two tables
I have 2 tables, table A & table B.
Table A (has thousands of rows)
id
uuid
name
type
created_by
org_id
Table B (has a max of hundred rows)
org_id
org_name
I am trying to get the ...
- 291
14
votes
3
answers
11k
views
Are temporary tables in postgresql visible over all client sessions?
I want to create a temp table so as to be able to join it to a few tables because joining those tables with the content of the proposed temporary table takes a lot of time (fetching the content of the ...
- 922
6
votes
1
answer
25k
views
PostgreSQL slow JOIN with CASE statement
In my database I have a table that contains ~3500 records and as a part of more complicated query I've tried to perform inner join on itself using "CASE" condition just as you can see below.
SELECT *
...
- 93
2
votes
2
answers
3k
views
Optimizing SQL query with multiple joins and grouping (Postgres 9.3)
I've browsed around some other posts and managed to make my queries run a bit faster. However, I've come to a loss as to how to further optimize this query. I'm going to be using it on a website where ...
- 71
2
votes
1
answer
636
views
PostgreSQL: FULL OUTER JOIN on RANGE datatypes
I have several tables with partially overlapping TSTZRANGE values and I need to JOIN them such that "breakpoints" are created for each UPPER and LOWER boundary for each range.
Some sample data:
...
- 1,570
1
vote
1
answer
243
views
Optimising Hash And Hash Joins in SQL Query
I have a bunch of tables in PostgreSQL and I run a query as follows:
SELECT DISTINCT ON ...some stuff...
FROM "rent_flats"
INNER JOIN "rent_flats_linked_users"
ON "rent_flats_linked_users"."...
- 741
0
votes
1
answer
2k
views
ORDER BY column from right table of LEFT OUTER JOIN
I'm having serious performance issues when using a LEFT OUTER JOIN and trying to use a column in the right table in Postgres. I have a table of users and a table with online_users that lists user ids ...
- 31
0
votes
1
answer
722
views
Configuring Merge Join in PostgreSQL
I'm using PostgreSQL with big tables, and query takes too much time.
I have two tables. The first one has about 6 million rows (data table), and the second one has about 30000 rows (users table).
...
- 63
2
votes
1
answer
4k
views
Inner join performance
I have a table that has a lot of foreign keys that I will need to inner join so I can search. There might be upwards of 10 of them, meaning I'd have to do 10 inner joins. Each of the tables being ...
- 6,769
2
votes
3
answers
2k
views
Why does the following join increase the query time significantly?
I have a star schema here and I am querying the fact table and would like to join one very small dimension table. I can't really explain the following:
EXPLAIN ANALYZE SELECT
COUNT(impression_id), ...
- 8,662
6
votes
3
answers
1k
views
Postgresql query optimization No inner/outer join allowed
I am given this query to optimize on POSTGRESQL 9.2:
SELECT C.name, COUNT(DISTINCT I.id) AS NumItems, COUNT(B.id)
FROM Categories C INNER JOIN Items I ON(C.id = I.category)
INNER ...
- 145