Re: Can Postgres use an INDEX over an OR?

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.

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).

Best regards, Vitaliy Tymchyshyn.