Re: Will partial index creation use existing index?

"Jim Nasby" <decibel(at)decibel(dot)org> writes:

> Check the source code, but I'm 99% certain that CREATE INDEX doesn't consider
> any existing indexes. While what you're describing is theoretically possible,
> it's not a very common use-case, so it's rather unlikely to get worked on
> unless other folks show up with *real life* examples of where this would be
> useful.

Yeah we don't support this feature. It is something useful that I even though
of doing a while back. I think it's on the TODO somewhere.

The main use case for it is actually REINDEX. Since you already have an index
which contains precisely the records you want to index and already in order
too.

The main disadvantage is that it's not clear when it would actually be faster.
Generally index scans are slower than reading the whole table and sorting.
Probably it would have to run an SPI query to use the planner to find the best
way to get the rows it wants.

Another problem is that presumably you're reindexing because the existing
index *isn't* in such good shape. You may even be doing it because the
existing index is corrupt.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com