Re: Questions regarding indexes

On Fri, Jan 16, 2009 at 8:47 AM, Bruno Lavoie <bruno(dot)lavoie(at)gmail(dot)com> wrote:
> Hello,
>
> are these statements true:

Got interrupted by a coworker... The other two questions:

> «Create an index if you frequently want to retrieve less than about ~15% of
> the rows in a large table»

PostgreSQL tends to switch to seq scans faster than other dbs due to
the fact that tuple visibility isn't stored in the indexes. The
switchover point tends to be lower than 15% where it changes from
index to seq scan. Typically it's in the 2 to 5% range, depending
very much on how you've configured postgresql.conf.

> «Index only columns with unique values, or only with a few duplicates»

You can also look at partial and / or functional indexes for some
apps. For instance, if you have a table with a boolean field, and
it's 99.9% true, you can index only the false entries. If you get a
lot of another field when the bool is false, then you can create an
index on that field. i.e.:

create index xyz on abc (intfield) where boolfield is false;

> Is it necessary to run ANALYZE on the table after any index creation to
> gather statistics about the index/column?

The order doesn't matter. Analyze doesn't know anything about the
indexes, it knows about the fields / tables. I.e. if you run analyze,
then create the index, you get the same basic result as if you create
the index then run analyze. The time to run analyze is when a good
percentage of the table values have been changed. Autovacuum will do
this for you, btw, but it might take a few minutes to kick in and see
the change in your query plans.