pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedEquivalent queries produce different plans
3+ messages / 2 participants
[nested] [flat]
* Equivalent queries produce different plans
@ 2007-07-11 00:53 Craig James <craig_james@emolecules.com>
0 siblings, 2 replies; 3+ messages in thread
From: Craig James @ 2007-07-11 00:53 UTC (permalink / raw)
To: pgsql-performance
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Equivalent queries produce different plans
@ 2007-07-11 01:06 Craig James <craig_james@emolecules.com>
parent: Craig James <craig_james@emolecules.com>
1 sibling, 0 replies; 3+ messages in thread
From: Craig James @ 2007-07-11 01:06 UTC (permalink / raw)
To: 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)
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Equivalent queries produce different plans
@ 2007-07-11 01:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig James <craig_james@emolecules.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2007-07-11 01:25 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> 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;
Yeah, the planner does not make any attempt to infer implied
inequalities, so it will not generate the last two clauses for you.
There is machinery in there to infer implied *equalities*, which
is cheaper (fewer operators to consider) and much more useful across
typical queries such as multiway joins on the same keys. I'm pretty
dubious that it'd be worth the cycles to search for implied
inequalities.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-07-11 01:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-11 00:53 Equivalent queries produce different plans Craig James <craig_james@emolecules.com>
2007-07-11 01:06 ` Craig James <craig_james@emolecules.com>
2007-07-11 01:25 ` Tom Lane <tgl@sss.pgh.pa.us>
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