Re: Same SQL, 104296ms of difference between 7.4.12 and 8.0.7

Rafael Martinez Guerrero <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)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