Re: Can Postgres use an INDEX over an OR?

2009/7/27 Віталій Тимчишин <tivv00(at)gmail(dot)com>:
>
>
> 27 липня 2009 р. 13:53 Robert Haas <robertmhaas(at)gmail(dot)com> написав:
>>
>> Hmm.  What you're suggesting here is that we could consider
>> implementing OR conditions by rescanning the inner side for each index
>> qual and then unique-ifying the results on the index column.  That's
>> probably possible, but it doesn't sound easy, especially since our
>> selectivity-estimation code for OR conditions is not very good, so we
>> might choose to do it this way when that's not actually the best plan.
>>
>> ...Robert
>
> Actually what I am talking about is to make OR with UNION (or UNION-like
> because it's a little different depending on input rows uniqueness) as an
> option. All of OR parts can use/not use different strategies (including
> multiple different idexes or hash joins).
> In cases when conditions are complex this can drastically increase
> performance by winning over sequence scan.

That's exactly what I was talking about.

> As of selectivity, I'd say this is general problem - sometimes it is
> estimated OK, sometimes not, but this should not prevent from trying
> different plans. (From my current work: it does wrong estimations of filter
> selectivity, introduces HASH join and kills the server with OOM).

Yep. I think the two things that would help the most with this are
multi-column statistics and some kind of cache, but AFAIK nobody is
actively developing code for either one ATM.

The problem, though, is that it won't ALWAYS be right to implement OR
using UNION, so you have to have some way of deciding which is better.

...Robert