Re: Timestamp indicies not being used! - Mailing list pgsql-general

On Sun, Jul 19, 2009 at 11:15:39AM +0100, Pedro Doria Meunier wrote:
> I have a table ~18M rows with a 'timestamp with time zone' column. It's
> indexed thus:
>
> CREATE INDEX my_table_timestamp_idx
>   ON my_table
>   USING btree
>   (zulu_timestamp);

Based on your query, I think you want a multi-column index---probably on
(id,zulu_timestamp).

The problem with just having an index on either column is that it's
difficult to combine them and PG hence just thinks that it will be
easier to scan backwards in time looking for the first entry for the
identifier you specified.

BTW, if you're concerned about insert performance then the less indexes
you have the better.

--
  Sam  http://samason.me.uk/