Message-ID: <5537C910.1030507@BlueTreble.com>
Date: Wed, 22 Apr 2015 11:15:12 -0500
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>, Tomas Vondra <tomas.vondra@2ndquadrant.com>
CC: Jon Dufresne <jon.dufresne@gmail.com>, <pgsql-performance@postgresql.org>
Subject: Re: extract(year from date) doesn't use index but maybe
 could?
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>
In-Reply-To: <6287.1429479191@sss.pgh.pa.us>
Content-Type: text/plain; charset="windows-1252"; format=flowed
Content-Transfer-Encoding: 7bit
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 22 Apr 2015 16:15:12.4386 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY1PR11MB0683
X-OriginatorOrg: bluetreble.com
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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