The two queries below produce different plans.
select r.version_id, r.row_num, m.molkeys from my_rownum r
join my_molkeys m on (r.version_id = m.version_id)
where r.version_id >= 3200000
and r.version_id < 3300000
order by r.version_id;
select r.version_id, r.row_num, m.molkeys from my_rownum r
join my_molkeys m on (r.version_id = m.version_id)
where r.version_id >= 3200000
and r.version_id < 3300000
and m.version_id >= 3200000
and m.version_id < 3300000
order by r.version_id;
I discovered this while looking at the plans for the first query. It seemed to be ignoring the fact that it
could push the "between" condition along to the second table, since the condition and the join are
on the same indexed columns. So, I added a redundant condition, and bingo, it was a lot faster. In the
analysis shown below, the timing (about 1.0 and 1.5 seconds respectively) are for a "hot" database
that's been queried a couple of times. In real life on a "cold" database, the times are more like
10 seconds and 21 seconds, so it's quite significant.
Thanks,
Craig
db=> explain analyze
db-> select r.version_id, r.row_num, m.molkeys from my_rownum r
db-> join my_molkeys m on (r.version_id = m.version_id)
db-> where r.version_id >= 3200000
db-> and r.version_id < 3300000
db-> order by r.version_id;
Sort (cost=264979.51..265091.06 rows=44620 width=366) (actual
time=1424.126..1476.048 rows=46947 loops=1)
Sort Key: r.version_id
-> Nested Loop (cost=366.72..261533.64 rows=44620 width=366) (actual
time=41.649..1186.331 rows=46947 loops=1)
-> Bitmap Heap Scan on my_rownum r (cost=366.72..41168.37 rows=44620
width=8) (actual time=41.616..431.783 rows=46947 loops=1)
Recheck Cond: ((version_id >= 3200000) AND (version_id < 3300000))
-> Bitmap Index Scan on i_chm_rownum_version_id_4998
(cost=0.00..366.72 rows=44620 width=0) (actual time=21.244..21.244 rows=46947
loops=1)
Index Cond: ((version_id >= 3200000) AND (version_id <
3300000))
-> Index Scan using i_chm_molkeys_version_id on my_molkeys m
(cost=0.00..4.93 rows=1 width=362) (actual time=0.009..0.010 rows=1 loops=46947)
Index Cond: ("outer".version_id = m.version_id)
Total runtime: 1534.638 ms
(10 rows)
db=> explain analyze
db-> select r.version_id, r.row_num, m.molkeys from my_rownum r
db-> join my_molkeys m on (r.version_id = m.version_id)
db-> where r.version_id >= 3200000
db-> and r.version_id < 3300000
db-> and m.version_id >= 3200000
db-> and m.version_id < 3300000
db-> order by r.version_id;
Sort (cost=157732.20..157732.95 rows=298 width=366) (actual
time=985.383..1037.423 rows=46947 loops=1)
Sort Key: r.version_id
-> Hash Join (cost=41279.92..157719.95 rows=298 width=366) (actual
time=502.875..805.402 rows=46947 loops=1)
Hash Cond: ("outer".version_id = "inner".version_id)
-> Index Scan using i_chm_molkeys_version_id on my_molkeys m
(cost=0.00..115717.85 rows=47947 width=362) (actual time=0.023..117.270 rows=46947
loops=1)
Index Cond: ((version_id >= 3200000) AND (version_id < 3300000))
-> Hash (cost=41168.37..41168.37 rows=44620 width=8) (actual
time=502.813..502.813 rows=46947 loops=1)
-> Bitmap Heap Scan on my_rownum r (cost=366.72..41168.37
rows=44620 width=8) (actual time=41.621..417.508 rows=46947 loops=1)
Recheck Cond: ((version_id >= 3200000) AND (version_id <
3300000))
-> Bitmap Index Scan on i_chm_rownum_version_id_4998
(cost=0.00..366.72 rows=44620 width=0) (actual time=21.174..21.174 rows=46947
loops=1)
Index Cond: ((version_id >= 3200000) AND (version_id
< 3300000))
Total runtime: 1096.031 ms
(12 rows)
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
