left join query running too long 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&lt;>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:

"  -&gt;  Gather  (cost=273235.26..273235.77 rows=5 width=8)"
"        Workers Planned: 5"
"        -&gt;  Partial Aggregate  (cost=272235.26..272235.27 rows=1 width=8)"
"              -&gt;  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"
"  -&gt;  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"
"        -&gt;  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"
"              -&gt;  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"
"  -&gt;  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"
"        -&gt;  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"
"              -&gt;  Result  (cost=0.00..226900.55 rows=2087269 width=0) (actual time=0.014..649.061 rows=1739383 loops=6)"
"                    Buffers: shared hit=179937"
"                    -&gt;  Append  (cost=0.00..206027.86 rows=2087269 width=6) (actual time=0.014..515.933 rows=1739383 loops=6)"
"                          Buffers: shared hit=179937"
"                          -&gt;  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"
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.