From: Craig James <craig_james@emolecules.com>
To: pgsql-performance@postgresql.org
Subject: Equivalent queries produce different plans
Date: Tue, 10 Jul 2007 17:53:17 -0700
Message-ID: <469429FD.6080504@emolecules.com> (raw)
In-Reply-To: <87hcom5p41.fsf@oxford.xeocode.com>
References: <46898AB3.7090409@emolecules.com>
<87hcom5p41.fsf@oxford.xeocode.com>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig_james@emolecules.com
Subject: Re: Equivalent queries produce different plans
In-Reply-To: <469429FD.6080504@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox