158x query improvement when removing 2 (noop) WHERE conditions

От: Louis-David Mitterrand
Тема: 158x query improvement when removing 2 (noop) WHERE conditions
Дата: ,
Msg-id: 20100728102744.GA18521@apartia.fr
(см: обсуждение, исходный текст)
Ответы: Re: 158x query improvement when removing 2 (noop) WHERE conditions  (Andres Freund)
Список: pgsql-performance


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.


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.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;


В списке pgsql-performance по дате сообщения:

От: Yeb Havinga
Сообщение: Re: Testing Sandforce SSD
От: Josh Berkus
Сообщение: Re: Pooling in Core WAS: Need help in performance tuning.