Re: extract(year from date) doesn't use index but maybe could?

On 04/19/15 22:10, Jon Dufresne wrote:
> On Sun, Apr 19, 2015 at 10:42 AM, Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>
>> Or you might try creating an expression index ...
>>
>> CREATE INDEX date_year_idx ON dates((extract(year from d)));
>>
>
> Certainly, but won't this add additional overhead in the form of two
> indexes; one for the column and one for the expression?

It will, but it probably will be more efficient than poorly performing
queries. Another option is to use the first type of queries with
explicit date ranges, thus making it possible to use a single index.

>
> 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.

Until then you either have to create an expression index, or use queries
with explicit date ranges (without "extract" calls).

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services