Re: Equivalent queries produce different plans - Mailing list pgsql-performance
From | Craig James |
---|---|
Subject | Re: Equivalent queries produce different plans |
Date | |
Msg-id | 46942D17.1030103@emolecules.com Whole thread Raw |
In response to | Equivalent queries produce different plans (Craig James <craig_james@emolecules.com>) |
List | pgsql-performance |
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) >
pgsql-performance by date: