Re: Understanding how partial indexes work?

On Dec 6, 2007 1:44 AM, Chris Velevitch <chris(dot)velevitch(at)gmail(dot)com> wrote:
> I have a query on a table:-
>
> X between k1 and k2 or X < k1 and Y <> k3
>
> where k1, k2, k3 are constants.
>
> How would this query work, if I created an index on X and a partial
> index on X where Y <> k3?

Ummm. Using AND and OR in the same where clause without parenthesis
is a bad idea, as the logic you might think you're expressing isn't
the exact logic you're actually expressing. Do you mean:

X between k1 and k2 or (X < k1 and Y <> k3)

OR

(X between k1 and k2 or X < k1) and Y <> k3

Those are two different questions.

That plan chosen by the query planner depends on what the data
distribution looks like, and what decisions the planner makes based on
the stats it has about that distribution.

Why not make a table, fill it with test data, analyze it, and see what
you get with your indexes with explain analyze select ...