Sorry, I forgot to mention: This is 8.1.4, with a fairly ordinary configuration
on a 4 GB system.
Craig
Craig James wrote:
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
