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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
