Skip site navigation (1) Skip section navigation (2)

Re: Can Postgres use an INDEX over an OR?

From: Віталій Тимчишин <tivv00(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Chris <dmagick(at)gmail(dot)com>, Robert James <srobertjames(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Can Postgres use an INDEX over an OR?
Date: 2009-07-27 11:37:14
Message-ID: 331e40660907270437t63d2c25cva36d8d42c6d0f3f1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2009-07-27 12:02:43
Subject: Re: Can Postgres use an INDEX over an OR?
Previous:From: Robert HaasDate: 2009-07-27 10:53:06
Subject: Re: Can Postgres use an INDEX over an OR?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group