Re: Understanding how partial indexes work? - Mailing list pgsql-general

From Scott Marlowe
Subject Re: Understanding how partial indexes work?
Date
Msg-id dcc563d10712060739ldc5dbf6tc01e313a1977b8fb@mail.gmail.com
Whole thread Raw
In response to Understanding how partial indexes work?  ("Chris Velevitch" <chris.velevitch@gmail.com>)
Responses Re: Understanding how partial indexes work?
List pgsql-general
On Dec 6, 2007 1:44 AM, Chris Velevitch <chris.velevitch@gmail.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 ...

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Understanding how partial indexes work?
Next
From: "Bryan Murphy"
Date:
Subject: Re: pg_dump and server responsiveness