pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed158x query improvement when removing 2 (noop) WHERE conditions
2+ messages / 2 participants
[nested] [flat]
* 158x query improvement when removing 2 (noop) WHERE conditions
@ 2010-07-28 10:27 Louis-David Mitterrand <vindex+lists-pgsql-performance@apartia.org>
2010-07-28 10:49 ` Re: 158x query improvement when removing 2 (noop) WHERE conditions Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 2+ messages in thread
From: Louis-David Mitterrand @ 2010-07-28 10:27 UTC (permalink / raw)
To: pgsql-performance
Hi,
On a hunch I removed two (legacy) WHERE conditions from the following
query I obtained a 158x speed improvement. Yet these condiditions do not
filter anything. Does that make any sense?
The EXPLAIN ANALYSE output is attached with, first the fast version and
then the slow one.
I'd like to understand what is at play here to explain such a dramatic
difference. This is with pg 8.4.4.
Thanks,
select p3.price as first_price,
p4.price as second_price,
p5.price as third_price,
t.id_cabin_category, t.id_currency, t.id_alert_cruise, t.id_cruise,
t.created_by, t.cabin_name, t.cabin_cat_code, t.cabin_type_name,
cr.login, cr.email, fx.currency_symbol, fx.currency_code,
c.saildate, ct.id_cruise_type, ct.package_name, s.id_ship, s.ship_name
from (select
first_value(max(p.id_price)) over w as first_id_price,
nth_value(max(p.id_price),2) over w as second_id_price,
p.id_cabin_category, p.id_currency,
p.created_on > ac.modified_on as is_new_price,
ac.id_alert_cruise, ac.id_cruise, ac.cabin_name, ac.created_by,
ac.cabin_cat_code, ac.cabin_type_name
from alert_to_category ac
join price p on (ac.id_cabin_category=p.id_cabin_category and
p.id_cruise=ac.id_cruise and (p.id_currency=ac.id_currency or
ac.id_currency is null))
-- XXX: removing these speeds up query by 158x !
-- where (ac.created_by=0 or nullif(0, 0) is null)
-- and (p.id_cruise=0 or nullif(0, 0) is null)
group by ac.id_cruise,ac.created_by,ac.id_alert_cruise,ac.cabin_name,
ac.cabin_cat_code, ac.cabin_type_name,
p.id_cabin_category,p.id_currency,p.id_cruise,
p.created_on > ac.modified_on
window w as (partition by
p.id_currency,p.id_cabin_category,p.id_cruise order by
p.created_on > ac.modified_on desc
rows between unbounded preceding and unbounded following)
order by p.id_cabin_category,p.id_currency) as t
join cruiser cr on (t.created_by=cr.id_cruiser)
join cruise c using (id_cruise)
join cruise_type ct using (id_cruise_type)
join ship s using (id_ship)
join currency fx using (id_currency)
join price p3 on (t.first_id_price=p3.id_price)
left join price p4 on (t.second_id_price=p4.id_price)
left join price p5 on (p5.id_price=(select id_price from price
where id_cruise=p3.id_cruise and id_cabin_category=p3.id_cabin_category
and id_currency=p3.id_currency and id_price < t.second_id_price
order by id_price desc limit 1))
where t.is_new_price is true and p3.price <> p4.price;
--
http://www.cruisefish.net
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: 158x query improvement when removing 2 (noop) WHERE conditions
2010-07-28 10:27 158x query improvement when removing 2 (noop) WHERE conditions Louis-David Mitterrand <vindex+lists-pgsql-performance@apartia.org>
@ 2010-07-28 10:49 ` Andres Freund <andres@anarazel.de>
0 siblings, 0 replies; 2+ messages in thread
From: Andres Freund @ 2010-07-28 10:49 UTC (permalink / raw)
To: pgsql-performance; +Cc: Louis-David Mitterrand <vindex+lists-pgsql-performance@apartia.org>
On Wednesday 28 July 2010 12:27:44 Louis-David Mitterrand wrote:
> The EXPLAIN ANALYSE output is attached with, first the fast version and
> then the slow one.
I think you forgot to attach it.
Andres
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-07-28 10:49 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-28 10:27 158x query improvement when removing 2 (noop) WHERE conditions Louis-David Mitterrand <vindex+lists-pgsql-performance@apartia.org>
2010-07-28 10:49 ` Andres Freund <andres@anarazel.de>
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