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, and that the client only accesses the database through server side functions. So I have the ability to break the start column into 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
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)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(dot)Gardner(at)yucaipaco(dot)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
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
In response to
pgsql-novice by date
|Next:||From: Gary Townsend||Date: 2007-06-08 16:46:14|
|Subject: PGPLSql Select Into problem.|
|Previous:||From: Achilleas Mantzios||Date: 2007-06-08 09:45:01|
|Subject: Re: [NOVICE] Query with tables from 2 different databasesinJava|