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 or flat)
Thread:
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
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


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-2014 The PostgreSQL Global Development Group