Thread: Partial indices

Hi all,

I was reading the PostgreSQL manual when I found the Partial indices' page.
I've read that partial indices are supported by PostgreSQL, but I've not found
any example about how to construct them.

I've check for create index documentation but I've not found any particular
form dedicated to partial indices.

Are Partial Indices still supported into latest release of PostgreSQL?
If yes, how can I use of them?


Thank you in advance for any reply.          \fer

Ferruccio Zamuner wrote:
>
> Hi all,
>
> I was reading the PostgreSQL manual when I found the Partial indices' page.
> I've read that partial indices are supported by PostgreSQL, but I've not
> found any example about how to construct them.
>
> I've check for create index documentation but I've not found any particular
> form dedicated to partial indices.
>
> Are Partial Indices still supported into latest release of PostgreSQL?
> If yes, how can I use of them?

I think they have suffered some serious bit-rot over the years
and are no longer functional, although some of the core code
still exists in the backend. It seems without bitmapped-indexes,
and the current state of partial indices, its impossible to index
on rare-occuring instances of an attribute against otherwise
regularly occuring data. :-(

Hope that helps,

Mike Mascari

Mike Mascari <mascarm@mascari.com> writes:
>> Are Partial Indices still supported into latest release of PostgreSQL?

> I think they have suffered some serious bit-rot over the years
> and are no longer functional, although some of the core code
> still exists in the backend.

Actually, quite a LOT of partial-index support code still exists in the
backend.  Someone removed support for the "WHERE predicate" clause of
CREATE INDEX from the grammar, but I don't know when or why that was
done.  AFAICT everything is still there to support the feature
downstream, and it doesn't look particularly bit-rotted.

Someday when we don't have any more-pressing problems, I'd like to try
to re-enable that support.  At the very least I might learn why it
was disabled ;-)

(Hey Bruce, do we have a TODO item for "Fix partial indices"?)

            regards, tom lane

Added to TODO:

    * Re-enable partial indexes

> Mike Mascari <mascarm@mascari.com> writes:
> >> Are Partial Indices still supported into latest release of PostgreSQL?
>
> > I think they have suffered some serious bit-rot over the years
> > and are no longer functional, although some of the core code
> > still exists in the backend.
>
> Actually, quite a LOT of partial-index support code still exists in the
> backend.  Someone removed support for the "WHERE predicate" clause of
> CREATE INDEX from the grammar, but I don't know when or why that was
> done.  AFAICT everything is still there to support the feature
> downstream, and it doesn't look particularly bit-rotted.
>
> Someday when we don't have any more-pressing problems, I'd like to try
> to re-enable that support.  At the very least I might learn why it
> was disabled ;-)
>
> (Hey Bruce, do we have a TODO item for "Fix partial indices"?)
>
>             regards, tom lane
>


--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026