From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Jon Dufresne <jon.dufresne@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: extract(year from date) doesn't use index but maybe could?
Date: Wed, 22 Apr 2015 11:15:12 -0500
Message-ID: <5537C910.1030507@BlueTreble.com> (raw)
In-Reply-To: <6287.1429479191@sss.pgh.pa.us>
References: <CADhq2b51G0QrjNz_vH6e_SLyu=tR0A0G0zHhFsP7rqRr+6FA3w@mail.gmail.com>
<5533E8F4.2050400@2ndquadrant.com>
<CADhq2b67QTAD7AkhGm3GZMwCUDU6kR70mpKEEnM=S5h1yRtdgg@mail.gmail.com>
<55341A3C.1080101@2ndquadrant.com>
<6287.1429479191@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 4/19/15 4:33 PM, Tom Lane wrote:
> We might still be willing to do it if there were a sufficiently wide range
> of examples that could be handled by the same extra machinery, but this
> doesn't look too promising from that angle: AFAICS only the "year" case
> could yield a useful index restriction.
"date_trunc() op val" is where we'd want to do this. Midnight, month and
quarter boundaries are the cases I've commonly seen. The first is
especially bad, because people got in the habit of doing
timestamptz::date = '2015-1-1' and then got very irritated when that
cast became volatile.
Perhaps this could be better handled by having a way to translate
date/time specifications into a range? So something like
"date_trunc('...', timestamptz) = val" would become "date_trunk('...',
timestamptz) <@ [val, val+<correct interval based on truncation>)".
<,<=,>= and > wouldn't even need to be a range. To cover the broadest
base, we'd want to recognize that extract(year) could transform to
date_trunk('year',...), and timestamp/timestamptz::date transforms to
date_trunc('day',...). I think these transforms would be lossless, so
they could always be made, though we'd not want to do the transformation
if there was already an index on something like date_trunc(...).
I don't readily see any other data types where this would be useful, but
this is so common with timestamps that I think it's worth handling just
that case.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: Jim.Nasby@BlueTreble.com, tgl@sss.pgh.pa.us, tomas.vondra@2ndquadrant.com, jon.dufresne@gmail.com
Subject: Re: extract(year from date) doesn't use index but maybe could?
In-Reply-To: <5537C910.1030507@BlueTreble.com>
* 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