Re: Understanding how partial indexes work?

On Dec 7, 2007 2:39 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Chris Velevitch" <chris(dot)velevitch(at)gmail(dot)com> writes:
> > 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?
>
> Is it worth the trouble? You didn't mention the statistics involved,
> but ordinarily I'd think a non-equal condition is too nonselective
> to justify the cost of maintaining an extra index.

Yes, I did neglect to mention the stats. Y is the status of the record
which represents the life cycle of the record starting at zero and
ending up at k3. So basically the query would retrieving and the
partial index will indexing records which aren't at their end of life.
So the number of records where Y <> k3 will be low and the number of
records where Y = k3 will be growing over time. So the ratio of Y <>
k3 to Y = k3 will be getting smaller over time.

> The real problem here is that "X < k1" is probably too nonselective
> as well, which will likely lead the planner to decide that a plain
> seqscan is the cheapest solution. In principle the above could be
> done with a BitmapOr of two indexscans on X, but unless the constants
> are such that only a small fraction of rows are going to be selected,
> it's likely that a seqscan is the way to go.

How would that compare doing a union the results of two queries:-

X between k1 and k2 UNION X where X < k1 and Y <> k3

where there is index on X and a partial index on X where Y <> k3

Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
m: 0415 469 095
www.flashdev.org.au