Message-ID: <5065F910.5040504@optionshouse.com>
Date: Fri, 28 Sep 2012 14:22:56 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:15.0) Gecko/20120827 Thunderbird/15.0
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Possible Performance Regression with Transitive Comparisons vs.
	Constants
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

Hey guys,

I ran into this while we were working on an upgrade project. We're 
moving from 8.2 (don't ask) to 9.1, and started getting terrible 
performance for some queries. I've managed to boil it down to a test case:

create temp table my_foo as
select a.id, '2012-01-01'::date + (random()*365)::int AS created_dt
   from generate_series(1,5000) as a(id);

create temp table my_bar as
select b.id, (random()*4999)::int + 1 as aid,
        '2012-01-01'::date + (random()*365)::int AS created_dt
   from generate_series(1,500000) as b(id);

analyze my_foo;
analyze my_bar;

create index idx_foo_id on my_foo (id);
create index idx_foo_const on my_foo (created_dt);

create index idx_bar_id on my_bar(id);
create index idx_bar_aid on my_bar(aid);
create index idx_bar_const on my_bar (created_dt);


Ok, simple enough, right? Now do this:


explain analyze
select b.*
   from my_foo a, my_bar b
  where a.created_dt = '2012-05-05'
    and b.created_dt between a.created_dt
        and a.created_dt + interval '1 month';

explain analyze
select b.*
   from my_foo a, my_bar b
  where a.created_dt = '2012-05-05'
    and b.created_dt between '2012-05-05'
        and '2012-05-05'::date + interval '1 month';


These do not create the same query plan, which itself is odd. But the 
other thing, is that query 1 is about 4-8x slower than query 2, but only 
when I test it on PostgreSQL 9.1. When I test it on 8.2 (eww) they're 
about equal in performance. I should note that the plan for both cases 
in 8.2, performs better than query 1 in 9.1.

So I've got two questions:

1. Is it normal for trivially equal values to be non-optimal like this?
2. What on earth happened between 8.2 and 9.1 that made performance 
worse for this test case?

Just to address any questions, I've tested this in multiple 
environments, and it's always consistent. 9.1 performs worse than 8.2 
here, so long as you rely on PostgreSQL to make the equivalence instead 
of doing it manually.


-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email