Re: Indexing timestamps

On Thu, 6 Jun 2002, Josh Berkus wrote:

> Andre,
>
> I'm not even going to try to deal with the seq_scan vs. index_scan
> issues on now(). This has been brought up in the list archives. Test
> your actual response times as well as the query plan; you may find that
> you don't have a real problem.
>
> Something I can help you with:
>
> > explain select * from test_table where date_trunc('month',time_stamp)
> > = date_trunc('month',datetime('2002-01-01'));
> > NOTICE: QUERY PLAN:
> >
> > Seq Scan on test_table (cost=0.00..2441.41 rows=584 width=16)
>
> Try doing a:
> CREATE INDEX idx_test_month ON test_table(extract(month FROM
> time_stamp));
>
> Which should help.

Unfortunately you can't do that in that sort of syntax. You'll
need to create a function that returns the month and is marked
as iscachable and use that function in the index creation and
query.