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

Re: timestamp with time zone

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alessandro Gagliardi <alessandro(at)path(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: timestamp with time zone
Date: 2012-02-09 19:46:06
Message-ID: 27573.1328816766@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Alessandro Gagliardi <alessandro(at)path(dot)com> writes:
> WHERE ... (created at time zone timezone)::date = 'yesterday'

> created has an index (btree if it matters). timezone does not. I'm
> wondering if the solution to my problem is to create a joint index between
> created and timezone (and if so, if there is a particular way to do that to
> make it work the way I want).

The only way to make that indexable is to create an expression index on
the whole expression "(created at time zone timezone)::date".  Seems
pretty special-purpose, though it might be worthwhile if you do that a
lot.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Alessandro GagliardiDate: 2012-02-09 20:00:56
Subject: Re: timestamp with time zone
Previous:From: Alessandro GagliardiDate: 2012-02-09 18:42:15
Subject: timestamp with time zone

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