From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no>
Cc: pgsql-performance@postgresql.org
Subject: Re: Same SQL, 104296ms of difference between 7.4.12 and 8.0.7
Date: Fri, 07 Apr 2006 13:36:58 -0400
Message-ID: <14669.1144431418@sss.pgh.pa.us> (raw)
In-Reply-To: <12251.1144423772@sss.pgh.pa.us>
References: <1144413933.24060.253.camel@bbking.uio.no>
<12251.1144423772@sss.pgh.pa.us>
I wrote:
> Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no> writes:
>> I have a sql statement that takes 108489.780 ms with 8.0.7 in a
>> RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz /
>> 8GB RAM and only 4193.588 ms with 7.4.12 in a RHEL3/386linux server with
>> 2xIntel(R) Xeon(TM) CPU 2.40GHz / 4GB RAM.
> I think you've discovered a planner regression.
> Simplified test case using the regression database:
> explain select * from tenk1 a, tenk1 b
> where (a.ten = b.ten and (a.unique1 = 100 or a.unique1 = 101))
> or (a.hundred = b.hundred and a.unique1 = 42);
I've repaired the assertion crash in 8.1/HEAD, but I don't think it's
practical to teach 8.0 to optimize queries like this nicely. The reason
7.4 can do it is that 7.4 forces the WHERE condition into CNF, ie
(a.hundred = b.hundred OR a.ten = b.ten) AND
(a.unique1 = 42 OR a.ten = b.ten) AND
(a.hundred = b.hundred OR a.unique1 = 100 OR a.unique1 = 101) AND
(a.unique1 = 42 OR a.unique1 = 100 OR a.unique1 = 101)
from which it's easy to extract the index condition for A. We decided
that forcing to CNF wasn't such a hot idea, so 8.0 and later don't do
it, but 8.0's logic for extracting index conditions from joinquals isn't
up to the problem of handling sub-ORs. Fixing that looks like a larger
change than I care to back-patch into an old release.
My recommendation is to update to 8.1.4 when it comes out.
regards, tom lane
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: tgl@sss.pgh.pa.us, r.m.guerrero@usit.uio.no
Subject: Re: Same SQL, 104296ms of difference between 7.4.12 and 8.0.7
In-Reply-To: <14669.1144431418@sss.pgh.pa.us>
* 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