Обсуждение: Index on timestamp fields
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 pgsqlto 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 changingthe 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
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
--- David Gardner <David.Gardner@yucaipaco.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.
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