From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alessandro Gagliardi <alessandro@path.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: timestamp with time zone
Date: Thu, 09 Feb 2012 14:46:06 -0500
Message-ID: <27573.1328816766@sss.pgh.pa.us> (raw)
In-Reply-To: <CAAB3BBJnD+dcaOQ_T8g+y==zen9yuOj2NvXrE5vhVe=RP28hMw@mail.gmail.com>
References: <CAAB3BBJnD+dcaOQ_T8g+y==zen9yuOj2NvXrE5vhVe=RP28hMw@mail.gmail.com>
Alessandro Gagliardi <alessandro@path.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, alessandro@path.com
Subject: Re: timestamp with time zone
In-Reply-To: <27573.1328816766@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox