Lists: | pgsql-novice |
---|
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 |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | David Gardner <David(dot)Gardner(at)yucaipaco(dot)com> |
Cc: | Novice Postgresql-list <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Index on timestamp fields |
Date: | 2007-06-08 01:28:31 |
Message-ID: | 11650.1181266111@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
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
From: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com> |
---|---|
To: | David Gardner <David(dot)Gardner(at)yucaipaco(dot)com>, Novice Postgresql-list <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Index on timestamp fields |
Date: | 2007-06-08 01:33:07 |
Message-ID: | 760204.2971.qm@web31805.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
--- David Gardner <David(dot)Gardner(at)yucaipaco(dot)com> wrote:
> 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?
I am not sure about putting current_date in a local variable but can create a better performing
index. Note that you can create indexes on an expression based on a field(s) in your table.
http://www.postgresql.org/docs/8.2/interactive/sql-createindex.html
CREATE INDEX "backupReportsDailyStart" ON "backupReports"."start" (
date_trunc('day', "backupReports"."start"));
This should run really fast since you will not have to scan an index for a value equal to
current_date.
Regards,
Richard Broersma Jr.
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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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