Re: Firebird and PostgreSQL at the DB Corral.

On Wed, Dec 17, 2003 at 11:08:13AM -0000, Paul Ganainm wrote:
> > > > Functional and Partial indexes
> > > O
> > No partial indexes? Get them to put it on their TODO list ;)
>
>
> What, exactly, is a partial index? A functional index is an index on
> something like ((ColumnX*2)/14)? I think the functional one (is that
> also an expression index?) is on the way.

A partial index is a index on a subset of a table. The case I can think of
is a list of transactions, some of which are yet to be billed. They have a
BillID field which is NULL. since this is the recent set it is queried quite
often, so you can build an index like:

CREATE INDEX x ON table ( customerid ) WHERE billid IS NULL

Now an index can be used on customer when searching for only unbilled things
whereas normally it would also have to search for all historical things as
well.

So, you get the benefit of a smaller index that is more useful to boot.

The other useful situation is it allows you to apply a UNIQUE contraint on
only a subset of a table. I havn't used it for that myself.

I hope this makes it clear.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> (... have gone from d-i being barely usable even by its developers
> anywhere, to being about 20% done. Sweet. And the last 80% usually takes
> 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce