Subject: Re: Same SQL, 104296ms of difference between 7.4.12 and
From: Rafael Martinez <r.m.guerrero@usit.uio.no>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <14669.1144431418@sss.pgh.pa.us>
References: <1144413933.24060.253.camel@bbking.uio.no>
	<12251.1144423772@sss.pgh.pa.us>  <14669.1144431418@sss.pgh.pa.us>
Content-Type: text/plain
Organization: University of Oslo
Date: Fri, 07 Apr 2006 21:04:51 +0200
Message-Id: <1144436691.6928.23.camel@linux.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2006-04-07 at 13:36 -0400, Tom Lane wrote:
> 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
> 
[..................]

Tom, thank you very much for your help. As I suspected this was a more
complicated problem than the configuration of some parameters :( . Good
that we have found out this now and not after the upgrade.

All our upgrade plans and testing for all our databases have been done
for/with 8.0.x (yes, I know 8.1.x is much better, but I am working in a
conservative place from the sysadm point of view). We will have to
change our plans and go for 8.1 if we want this to work. 

> My recommendation is to update to 8.1.4 when it comes out.

Any idea about when 8.1.4 will be released?
Thanks again.

-- 
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/