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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
