Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
> On 04/19/15 22:10, Jon Dufresne wrote:
>> My point is, why force the user to take these extra steps or add
>> overhead when the the two queries (or two indexes) are functionally
>> equivalent. Shouldn't this is an optimization handled by the
>> database so the user doesn't need to hand optimize these differences?
> Theoretically yes.
> But currently the "extract" function call is pretty much a black box for
> the planner, just like any other function - it has no idea what happens
> inside, what fields are extracted and so on. It certainly is unable to
> infer the date range as you propose.
> It's possible that in the future someone will implement an optimization
> like this, but I'm not aware of anyone working on that and I wouldn't
> hold my breath.
Yeah. In principle you could make the planner do this. As Adam Williams
notes nearby, there's a problem with lack of exact consistency between
extract() semantics and straight timestamp comparisons; but you could
handle that by extracting indexable expressions that are considered lossy,
much as we do with anchored LIKE and regexp patterns. The problem is that
this would add significant overhead to checking for indexable clauses.
With "x LIKE 'foo%'" you just need to make a direct check whether x is
an indexed column; this is exactly parallel to noting whether x is indexed
in "x >= 'foo'", and it doesn't require much additional machinery or
cycles to reject the common case that there's no match to x. But if you
want to notice whether d is indexed in "extract(year from d) = 2015",
that requires digging down another level in the expression, so it's going
to add overhead that's not there now, even in cases that have nothing to
do with extract() let alone have any chance of benefiting.
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.
So the short answer is that whether it's worth saving users from
hand-optimizing such cases depends a lot on what it's going to cost in
added planning time for queries that don't get any benefit. This example
doesn't look like a case that's going to win that cost/benefit tradeoff
comparison.
regards, tom lane