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