This seems kinda ugly --- it looks very expensive and unlikely to find
useful optimizations most of the time.  Have you done any benchmarking
to find out what the cost is when the optimizations don't succeed?


Yep, it's a big win with order and limit specified.
Let table (a,b) has index over (a,b), so,
queries with ((a=3 AND b>10) OR a>3) ORDER BY a,b may be done with one pass of index scan with condition a>=3 and filter ((a=3 and b>10) or a>3). And scan out is already sorted. The single way to execute it without patch is bitmap or scan over two index scans and following ordering. If limit is small enough then there is a lot unnecessary work for executor. Thats case may be found by find_common_quals() which is fast enough.

Simplest case of second kind is ( a<3 or a>5 ). If it's possible to prove that set of rows for first condition and set for second are not intersected then output of correlated index scans can be simply joined/appended. In this case, we broaden applicability of Append node. What is more, it's possible to order index scans by conditions, which allows do not use sort node.

I understand, that proving of non-intersecting and ordering by conditions is an expensive operation because of using predicate_implied_by/predicate_refuted_by, but in most cases they aren't even called. For using this optimization all conditions should on single index - and it's first step.

Suggested plan's is very effective when a or b is large values like varchar, not a just integers.

Also, what's with the pull_tlist bit?
pull target list from subplan.

I've add it because query
select b,a from foo where a<3 or a>5 order by a limit 1;
with plan
Result
        Append
                Index Scan
                Index Scan
fails because Result node thinks that it gets (b,a) tuple, but in fact it gets (a,b). So, if pull_tlist is TRUE, then create_append_plan takes target list from first subplan. Currently, that bit set only with OR-optimized plan. And optimizer of OR-clause guarantee that target lists are the same. Sorry, but I didn't find clearer solution...


--
Teodor Sigaev                                   E-mail: [EMAIL PROTECTED]
                                                   WWW: http://www.sigaev.ru/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Reply via email to