Date: Wed, 28 Jul 2010 12:27:44 +0200
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
Message-ID: <20100728102744.GA18521@apartia.fr>
Mail-Followup-To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.20 (2009-06-14)

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