Index on timestamp fields

From: David Gardner <David(dot)Gardner(at)yucaipaco(dot)com>
To: Novice Postgresql-list <pgsql-novice(at)postgresql(dot)org>
Subject: Index on timestamp fields
Date: 2007-06-08 00:55:56
Message-ID: C975BFE03CC5DE4999143A5BEA9FB515021DC0C51F@yucex.lax.yucwin
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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?

---
David Gardner, IT
The Yucaipa Companies
(310) 228-2855

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2007-06-08 01:28:31 Re: Index on timestamp fields
Previous Message Lonni J Friedman 2007-06-07 19:43:11 Re: querying the age of a row