Josh Berkus <[EMAIL PROTECTED]> writes: > Summary: Currently, queries with complex "or group" criteria get devolved by > the planner into canonical and-or filters resulting in very poor execution on > large data sets. We should find better ways of dealing with these queries, > for example UNIONing.
Could we see the actual present query plans for both the TPC-R query and the UNION version? (I'll settle for "explain" on the slow version, but "explain analyze" on the other, please.) In general I am suspicious of proposals to rewrite queries into UNION "equivalents", because the "equivalent" usually isn't exactly equivalent, at least not without conditions that the planner can't easily prove. This proposal looks a lot like the KSQO optimization that we put in and then took out again several years ago --- it also rewrote queries into a UNION form, only the UNION didn't necessarily produce identical results. I am thinking that the guys who do this query fast are probably extracting single-relation subsets of the big OR/AND clause, so that they can do some filtering of the input tables before the join. Our existing planner would think that the OR/AND clause is only usable at the join step, which is why it's seqscanning. But if we pulled out subsets, we could have for instance WHERE t1.a = t2.a AND ( ( t1.c = x AND t1.f IN (m, n, o) AND t2.d = v AND t2.e BETWEEN j AND k ) OR ( t1.c = y AND t1.f IN (n, o, p) AND t2.d = v AND t2.e BETWEEN k AND h ) OR ( t1.c = z AND t1.f IN (p, q) AND t2.d = w AND t2.e BETWEEN k AND h ) ) AND ( t1.c = x OR t1.c = y OR t1.c = z ) which is redundant, but that last clause could enable an indexscan on t1.c. However ... the planner has code in it already that should do something close to that, so there may be something I am missing. Again, could we see EXPLAIN results? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend