Skip site navigation (1) Skip section navigation (2)

Re: 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: Re: Index on timestamp fields
Date: 2007-06-08 14:58:13
Message-ID: C975BFE03CC5DE4999143A5BEA9FB515021DC0C520@yucex.lax.yucwin (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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
(310) 228-2855

-----Original Message-----
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 TownsendDate: 2007-06-08 16:46:14
Subject: PGPLSql Select Into problem.
Previous:From: Achilleas MantziosDate: 2007-06-08 09:45:01
Subject: Re: [NOVICE] Query with tables from 2 different databasesinJava

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group