0

I have two tables, test1 and test2, and these tables have around 600M(70GB) and 10k(2MB) records, respectively. when I run a query with left join on site_id, it is taking too long, even though site_id has an index in both tables.table1.rank also indexed.

Added tables structure here db<>fiddle

EXPLAIN Query:

explain SELECT count(*) FROM test1 t1
   left join test2 t2 
   on t1.site_id =t2.site_id where rank=1;

Execution plan:

"  ->  Gather  (cost=273235.26..273235.77 rows=5 width=8)"
"        Workers Planned: 5"
"        ->  Partial Aggregate  (cost=272235.26..272235.27 rows=1 width=8)"
"              ->  Parallel Seq Scan on test t1  (cost=0.00..267015.64 rows=2087846 width=0)"
"                    Filter: (rank = 1)"

Execution plan with EXPLAIN (ANALYZE, BUFFERS):

"Finalize Aggregate  (cost=273235.78..273235.79 rows=1 width=8) (actual time=625.486..625.486 rows=1 loops=1)"
"  Buffers: shared hit=233204"
"  ->  Gather  (cost=273235.26..273235.77 rows=5 width=8) (actual time=624.638..700.069 rows=6 loops=1)"
"        Workers Planned: 5"
"        Workers Launched: 5"
"        Buffers: shared hit=233204"
"        ->  Partial Aggregate  (cost=272235.26..272235.27 rows=1 width=8) (actual time=436.761..436.762 rows=1 loops=6)"
"              Buffers: shared hit=233204"
"              ->  Parallel Seq Scan on test1 t1  (cost=0.00..267015.64 rows=2087846 width=0) (actual time=0.014..336.926 rows=1739383 loops=6)"
"                    Filter: (rank = 1)"
"                    Rows Removed by Filter: 514726"
"                    Buffers: shared hit=233204"
"Planning time: 0.120 ms"
"Execution time: 700.117 ms"

Execution plan with a partition on test1, EXPLAIN (ANALYZE, BUFFERS):

"Finalize Aggregate  (cost=233119.25..233119.26 rows=1 width=8) (actual time=999.316..999.316 rows=1 loops=1)"
"  Buffers: shared hit=179937"
"  ->  Gather  (cost=233118.73..233119.24 rows=5 width=8) (actual time=998.124..1054.495 rows=6 loops=1)"
"        Workers Planned: 5"
"        Workers Launched: 5"
"        Buffers: shared hit=179937"
"        ->  Partial Aggregate  (cost=232118.73..232118.74 rows=1 width=8) (actual time=796.767..796.768 rows=1 loops=6)"
"              Buffers: shared hit=179937"
"              ->  Result  (cost=0.00..226900.55 rows=2087269 width=0) (actual time=0.014..649.061 rows=1739383 loops=6)"
"                    Buffers: shared hit=179937"
"                    ->  Append  (cost=0.00..206027.86 rows=2087269 width=6) (actual time=0.014..515.933 rows=1739383 loops=6)"
"                          Buffers: shared hit=179937"
"                          ->  Parallel Seq Scan on test1_p1 t1  (cost=0.00..206027.86 rows=2087269 width=6) (actual time=0.012..415.959 rows=1739383 loops=6)"
"                                Filter: (rank = 1)"
"                                Buffers: shared hit=179937"
"Planning time: 0.254 ms"
"Execution time: 1054.547 ms"

4
  • The condition rank = 1 returns about 80% of all rows, so the index won't be helpful. You could try a partial index: create index on test1(the_primary_key_column) where rank = 1 to see if that triggers an index only scan that is faster.
    – user1822
    Jun 10, 2020 at 7:14
  • I created a list partition on the table by rank in 1, so both cases are the same right?
    – Rj_N
    Jun 10, 2020 at 7:57
  • A list partition is something different than a partial index
    – user1822
    Jun 10, 2020 at 8:41
  • No luck, created partial index still same behavior :(
    – Rj_N
    Jun 10, 2020 at 16:50

1 Answer 1

0

PostgreSQL optimizes the query so that the left join is completely ignored, so that doesn't factor in the question at all.

You didn't provide EXPLAIN (ANALYZE, BUFFERS) results, but either you don't have an index on test1.rank, or PostgreSQL considers the condition not selective enough.

One thing you can try is to create an index on test1.rank and VACUUM test1, then perhaps you can get a faster index-only scan.

9
  • updated post with EXPLAIN (ANALYZE, BUFFERS) results and the index has on test1.rank. I mentioned in db<>fiddle. And also, I created partition test1.rank with the list partition but didn't see a big difference.
    – Rj_N
    Jun 10, 2020 at 7:15
  • Thanks. So the condition is not selective at all. You can try for an index-only scan as detailed in my edited answer. Jun 10, 2020 at 7:41
  • yes, I did run both VACUUM test1 and also VACUUM ANALYZE test1, there is no improvements.
    – Rj_N
    Jun 10, 2020 at 7:45
  • Try with enable_seqscan = off, see if you get an index-only scan and if that is faster. Jun 10, 2020 at 7:50
  • seems, seq scan is better than index scan, Finalize Aggregate (cost=315410.51..315410.52 rows=1 width=8) (actual time=5554.103..5554.103 rows=1 loops=1). the final cost increased to nearly 30%.
    – Rj_N
    Jun 10, 2020 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.