Re: Index on timestamp fields

--- David Gardner <David(dot)Gardner(at)yucaipaco(dot)com> wrote:

> I have a timestamp without timezone field in one of my tables that is used in the where clause
> of one of my queries:
> WHERE date_trunc('day',"backupReports"."start")= current_date
>
> I also built an index on that column, I'm assuming that by using the date_trunc() function in my
> where clause forces pgsql to perform a sequential scan.
> Is there a better way to do this? The query needs to return the full timestamp, although I am
> only selecting by date so changing the data type for the column won't work.
>
> Also would saving the value of current_date to a local variable improve performance?

I am not sure about putting current_date in a local variable but can create a better performing
index. Note that you can create indexes on an expression based on a field(s) in your table.
http://www.postgresql.org/docs/8.2/interactive/sql-createindex.html

CREATE INDEX "backupReportsDailyStart" ON "backupReports"."start" (
date_trunc('day', "backupReports"."start"));

This should run really fast since you will not have to scan an index for a value equal to
current_date.

Regards,
Richard Broersma Jr.