From: Shaun Thomas <sthomas@optionshouse.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Possible Performance Regression with Transitive Comparisons vs. Constants
Date: Fri, 28 Sep 2012 14:22:56 -0500
Message-ID: <5065F910.5040504@optionshouse.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@optionshouse.com
Subject: Re: Possible Performance Regression with Transitive Comparisons vs. Constants
In-Reply-To: <5065F910.5040504@optionshouse.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox