Possible Performance Regression with Transitive Comparisons vs. Constants

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(at)optionshouse(dot)com

______________________________________________

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