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
