From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Jon Dufresne <jon.dufresne@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: extract(year from date) doesn't use index but maybe could?
Date: Sun, 19 Apr 2015 23:12:28 +0200
Message-ID: <55341A3C.1080101@2ndquadrant.com> (raw)
In-Reply-To: <CADhq2b67QTAD7AkhGm3GZMwCUDU6kR70mpKEEnM=S5h1yRtdgg@mail.gmail.com>
References: <CADhq2b51G0QrjNz_vH6e_SLyu=tR0A0G0zHhFsP7rqRr+6FA3w@mail.gmail.com>
<5533E8F4.2050400@2ndquadrant.com>
<CADhq2b67QTAD7AkhGm3GZMwCUDU6kR70mpKEEnM=S5h1yRtdgg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 04/19/15 22:10, Jon Dufresne wrote:
> On Sun, Apr 19, 2015 at 10:42 AM, Tomas Vondra
> <tomas.vondra@2ndquadrant.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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, jon.dufresne@gmail.com
Subject: Re: extract(year from date) doesn't use index but maybe could?
In-Reply-To: <55341A3C.1080101@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox