From: Louis-David Mitterrand <vindex+lists-pgsql-performance@apartia.org>
To: pgsql-performance@postgresql.org
Subject: 158x query improvement when removing 2 (noop) WHERE conditions
Date: Wed, 28 Jul 2010 12:27:44 +0200
Message-ID: <20100728102744.GA18521@apartia.fr> (raw)
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
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: vindex+lists-pgsql-performance@apartia.org
Subject: Re: 158x query improvement when removing 2 (noop) WHERE conditions
In-Reply-To: <20100728102744.GA18521@apartia.fr>
* 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