Re: Will partial index creation use existing index?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 07/24/07 14:48, Gregory Stark wrote:
[snip]
>
> 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.

I believe you, but it's totally counter to prima-fascia logic.

Scanning the whole table means that you have to read in a whole
bunch of columns that you don't really give a rat's arse about, and
thus is a waste, whereas directly reading an existing index means
that you've got perfect locality of data, since you're only reading
what you care about.

> 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.

That, of course, is an excellent point.

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGpqHkS9HxQb37XmcRAu93AKC04YXUWvMI6YiLfBNoy2BYtQw28ACdHqE/
kVqHiPwBONv0Tudy5OnA/SE=
=Fbuw
-----END PGP SIGNATURE-----