1: Bad plan: ====================== testjoin=> EXPLAIN ANALYZE SELECT id FROM a JOIN b ON a.id = b.a_id WHERE b.id = 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=122.29..151.29 rows=12 width=12) (actual time=4.854..4.974 rows=1 loops=1) Hash Cond: (archive_a.id = archive_b.a_id) -> HashAggregate (cost=73.05..85.21 rows=1216 width=4) (actual time=3.399..4.139 rows=1000 loops=1) -> Append (cost=0.00..70.01 rows=1216 width=4) (actual time=0.052..2.164 rows=1000 loops=1) -> Seq Scan on archive_a (cost=0.00..39.10 rows=216 width=4) (actual time=0.019..0.019 rows=0 loops=1) Filter: ((start_time <= 1000) AND (end_time > 1000)) Rows Removed by Filter: 1 -> Seq Scan on table_a (cost=0.00..18.75 rows=1000 width=4) (actual time=0.032..1.100 rows=1000 loops=1) ^^^^ |||| Filter: (updated <= 1000) Rows Removed by Filter: 100 -> Hash (cost=49.21..49.21 rows=2 width=8) (actual time=0.172..0.172 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Unique (cost=49.18..49.19 rows=2 width=8) (actual time=0.165..0.168 rows=1 loops=1) -> Sort (cost=49.18..49.18 rows=2 width=8) (actual time=0.162..0.163 rows=1 loops=1) Sort Key: archive_b.id, archive_b.a_id Sort Method: quicksort Memory: 25kB -> Append (cost=0.00..49.17 rows=2 width=8) (actual time=0.067..0.070 rows=1 loops=1) -> Seq Scan on archive_b (cost=0.00..40.98 rows=1 width=8) (actual time=0.041..0.041 rows=0 loops=1) Filter: ((start_time <= 1000) AND (end_time > 1000) AND (id = 1)) Rows Removed by Filter: 1 -> Index Scan using table_b_pkey on table_b (cost=0.15..8.17 rows=1 width=8) (actual time=0.024..0.027 rows=1 loops=1) Index Cond: (id = 1) Filter: (updated <= 1000) Total runtime: 5.455 ms (24 rows) 2: Good plan: ====================== EXPLAIN ANALYZE SELECT a.id FROM table_a a JOIN table_b b ON a.id = b.a_id WHERE b.id = 1 AND a.updated <= 1000 AND b.updated <= 1000 UNION SELECT a.id FROM table_a a JOIN archive_b b ON a.id = b.a_id WHERE b.id = 1 AND a.updated <= 1000 AND b.start_time <= 1000 AND b.end_time > 1000 UNION SELECT a.id FROM archive_a a JOIN table_b b ON a.id = b.a_id WHERE b.id = 1 AND a.start_time <= 1000 AND a.end_time > 1000 AND b.updated <= 1000 UNION SELECT a.id FROM archive_a a JOIN archive_b b ON a.id = b.a_id WHERE b.id = 1 AND a.start_time <= 1000 AND a.end_time > 1000 AND b.start_time <= 1000 AND b.end_time > 1000 ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ HashAggregate (cost=99.39..99.43 rows=4 width=4) (actual time=0.082..0.082 rows=1 loops=1) -> Append (cost=0.00..99.38 rows=4 width=4) (actual time=0.031..0.075 rows=1 loops=1) -> Nested Loop (cost=0.00..16.55 rows=1 width=4) (actual time=0.030..0.032 rows=1 loops=1) -> Index Scan using table_b_pkey on table_b b (cost=0.00..8.27 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=1) Index Cond: (id = 1) Filter: (updated <= 1000) -> Index Scan using table_a_pkey on table_a a (cost=0.00..8.27 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1) Index Cond: (a.id = b.a_id) Filter: (a.updated <= 1000) -> Nested Loop (cost=0.00..16.56 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1) -> Index Scan using ab_idx on archive_b b (cost=0.00..8.27 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1) Index Cond: ((start_time <= 1000) AND (end_time > 1000)) Filter: (id = 1) -> Index Scan using table_a_pkey on table_a a (cost=0.00..8.27 rows=1 width=4) (never executed) Index Cond: (a.id = b.a_id) Filter: (a.updated <= 1000) -> Hash Join (cost=19.06..33.12 rows=1 width=4) (actual time=0.025..0.025 rows=0 loops=1) Hash Cond: (a.id = b.a_id) -> Bitmap Heap Scan on archive_a a (cost=10.77..24.01 rows=216 width=4) (actual time=0.004..0.004 rows=0 loops=1) Recheck Cond: ((start_time <= 1000) AND (end_time > 1000)) -> Bitmap Index Scan on aa_idx (cost=0.00..10.72 rows=216 width=0) (actual time=0.002..0.002 rows=0 loops=1) Index Cond: ((start_time <= 1000) AND (end_time > 1000)) -> Hash (cost=8.27..8.27 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Index Scan using table_b_pkey on table_b b (cost=0.00..8.27 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1) Index Cond: (id = 1) Filter: (updated <= 1000) -> Hash Join (cost=19.06..33.12 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=1) Hash Cond: (a.id = b.a_id) -> Bitmap Heap Scan on archive_a a (cost=10.77..24.01 rows=216 width=4) (never executed) Recheck Cond: ((start_time <= 1000) AND (end_time > 1000)) -> Bitmap Index Scan on aa_idx (cost=0.00..10.72 rows=216 width=0) (never executed)