Re: Bundle of patches

> 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: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/