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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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