I wrote: > Rafael Martinez Guerrero <r.m.guerrero@xxxxxxxxxxx> 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
