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 11:29:32 -0400
Message-ID: <12251.1144423772@sss.pgh.pa.us> (raw)
In-Reply-To: <1144413933.24060.253.camel@bbking.uio.no>
References: <1144413933.24060.253.camel@bbking.uio.no>
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);
7.4:
Nested Loop (cost=0.00..2219.74 rows=4 width=488)
Join Filter: ((("outer".hundred = "inner".hundred) OR ("outer".ten = "inner".ten)) AND (("outer".unique1 = 42) OR ("outer".ten = "inner".ten)) AND (("outer".hundred = "inner".hundred) OR ("outer".unique1 = 100) OR ("outer".unique1 = 101)))
-> Index Scan using tenk1_unique1, tenk1_unique1, tenk1_unique1 on tenk1 a (cost=0.00..18.04 rows=3 width=244)
Index Cond: ((unique1 = 42) OR (unique1 = 100) OR (unique1 = 101))
-> Seq Scan on tenk1 b (cost=0.00..458.24 rows=10024 width=244)
(5 rows)
8.0:
Nested Loop (cost=810.00..6671268.00 rows=2103 width=488)
Join Filter: ((("outer".ten = "inner".ten) AND (("outer".unique1 = 100) OR ("outer".unique1 = 101))) OR (("outer".hundred = "inner".hundred) AND ("outer".unique1 = 42)))
-> Seq Scan on tenk1 a (cost=0.00..458.00 rows=10000 width=244)
-> Materialize (cost=810.00..1252.00 rows=10000 width=244)
-> Seq Scan on tenk1 b (cost=0.00..458.00 rows=10000 width=244)
(5 rows)
Note the failure to pull out the unique1 conditions from the join clause
and use them with the index. I didn't bother to do EXPLAIN ANALYZE;
this plan obviously sucks compared to the other.
8.1:
TRAP: FailedAssertion("!(!restriction_is_or_clause((RestrictInfo *) orarg))", File: "indxpath.c", Line: 479)
LOG: server process (PID 12201) was terminated by signal 6
server closed the connection unexpectedly
Oh dear.
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: <12251.1144423772@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