Thanks to Tom, and Richard for the advice. I will build an index against the expression.
I thought of a third possible solution. What I forgot to mention was that the query was part of a pl/pgsql function,
andthat the client only accesses the database through server side functions. So I have the ability to break the start
columninto start_date and start_time columns as long as I concatenate the two on the return value.
However the advice of creating an index against the expression is more elegant, and less intrusive on the existing
database.
---
David Gardner, IT
The Yucaipa Companies
(310) 228-2855
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, June 07, 2007 6:29 PM
To: David Gardner
Cc: Novice Postgresql-list
Subject: Re: [NOVICE] Index on timestamp fields
David Gardner <David.Gardner@yucaipaco.com> writes:
> 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
If you can change the query, it'd be better/more efficient to spell this
as
WHERE "backupReports"."start"::date = current_date
Either way, you need to build an index on the expression, not just the
raw column, to make this search fast.
regards, tom lane
