Re: Same SQL, 104296ms of difference between 7.4.12 and 8.0.7 - Mailing list pgsql-performance

From Tom Lane
Subject Re: Same SQL, 104296ms of difference between 7.4.12 and 8.0.7
Date
Msg-id 12251.1144423772@sss.pgh.pa.us
Whole thread Raw
In response to Same SQL, 104296ms of difference between 7.4.12 and 8.0.7  (Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no>)
Responses Re: Same SQL, 104296ms of difference between 7.4.12 and 8.0.7
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Loading the entire DB into RAM
Next
From: "Merlin Moncure"
Date:
Subject: Re: Loading the entire DB into RAM