Message-ID: <1429477427.11066.4.camel@whitemice.org>
Subject: Re: extract(year from date) doesn't use index but maybe
 could?
From: Adam Tauno Williams <awilliam@whitemice.org>
Reply-To: awilliam@whitemice.org
To: pgsql-performance@postgresql.org
Date: Sun, 19 Apr 2015 17:03:47 -0400
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>
Face: 
 iVBORw0KGgoAAAANSUhEUgAAADAAAAAwCAIAAADYYG7QAAAAA3NCSVQICAjb4U/gAAAJjElEQVRYw70Za0gUXXvmrLraqmmmpmVt2IWUlSLIyhATDLt4CRLqR1lUWJSFIdHNhEgsNwwl0TDLLBBLKUOlqNTUvBKUlZaXvLSVqK0puro7c2a+H0/f6bwzs5vv9+N7fogz+5znPPfbsIIgMDZBFMWcnBy1Wn348GHm/wCiKIqiKAiCqAQY48bGRjs7u6SkJI7jxL+BIAhAyhpBOb7kjZ0oiizLAnOCIPA8jxBSqVQsywJ2eno6xrirqwshJIoikYRlWfqRBkEQ4DihbANf+hJjLPwXMMZms9lsNgMrPM8XFRWpVCqGYdzc3CYnJwkmUQZ5BHXaQCBo9D9wKa0whpyZmJjgOI6m0tHR4ezsTORLSUnheV7xPgnQ9/1bfIbGlmjr1KlToB4AtVo9NDQkp06fsnafbc3RRBCxIsuytMl//fpVVFSEMSZvLBZLYWEhSEMbnT6l6Bb0o7Wf/hCRmxke8/LyJDexLOvj46NoNYmNFH0F/pF4jByfkXucIAgWiyU4OFgxgm7duiVnQiKM5I6/mpIOe0ZCDoLlzJkzckMAaDSagoICid/YdmGO46qrq/v6+iTalYeeIAiMPE319PTYSKQsy6pUqsrKytlkPyJheHi4g4NDeXn5X3MjI9fk5cuX/5rfFy1a9PTpU8X4ktvo5cuXarWaYRhHR8cTJ078+PGDqEoOjERvPM+vXLmS1oe9vf3q1asDAgJ8fHwcHBxYlkUIwd+HDx9ijGk/lcs9NTUVGBhICCKEQkJCTCYTz/OKSmIkGaWqqgohBId9fX3T0tL6+/s5jsMY8zw/ODh4+/bt2NhYjUbDsqyLi8vx48e/fPmiKCvwmpycLDe6Tqdrb29XzOz/KK4Wi2Xp0qVElHv37tFykLi1WCwtLS3z58+H1OXn51dVVUVLCaR5nq+qq
 rLmiOvWr
 TOZTHIlMfRN9fX1JDWvWrXKZDIphjTA+Pj41atXXV1dAT84ODg7O7uysvLt27e1tbX379+PjY11dHS04YinTp2CYkUTZ2gNHzx4kKgnPz/fdkEAhY+NjR04cICuMP8K9u7dOzY2JnVqUquXLFlCGOrt7aV1w/M8z/Ny/wXOjhw5YoMnlmU1Gs3evXuzsrKysrLi4+OdnZ0Bn2XZ69evKzg1xri6upoQDQoKoiu/2Wy+efPmyZMnr1y5Mjw8DEFL69lisZw7d06eS6HapKenf/36FeSBu4xGY0pKCuAvXry4u7ubODhDWp+IiAhCKC0tjU4VycnJRKD169ePjo7Kcw/HcfJqo1KpXr9+rZiuOI7z9/cHtMDAwOnp6X/UsoGBAQcHB2Kvuro6iC9BENra2uzs7Gih/fz8Ghoa5E1Fc3Ozu7s7raejR48SbiT4GOP09HRy4507d8Amvxl6/PgxST8ODg60KHq9Xu4Wy5Yts1gscNm7d+/6+/sB+ePHj6Sn02g0g4ODNordwMAAEXXNmjV/GMIYnzt3js6H9OGEhAQ5QwihuLg4yAtmszkiIqK3txdIpaamQiq3EackUIiVEUKZmZk8zzNgzoiICKJqrVZLH4uJiVGMHZVKVVFRAUzcuHFj9+7dEIMTExNeXl5arRbyO50qR0ZGDAaDxWIhxLOzs4knqNVqg8Hwm6EFCxaQm7RaLe2G4eHh1uJ53759EDtGo9Hb27utrQ3k7ujouHjxIq2Szs7OyMhIR0dHlUoVGRk5Pj4O79+/f0/73LVr1xhBEMrLy+m33t7eNK2oqChrDLm6ukLEiaIYHR0dFhYGjiUp2AaDgVQkUEZ4eDhJK9AIAKxYsYIRBGHPnj00Qwghmtz+/futMYQQKi4uBszz588zDFNUVCQpfxzHbdu2Td4NDw8Pyxlyd3dHHMfV19dLGu+urq7fWYFhaOHkA+Hjx49BJatWrWIY5tixYwaDgWaos7Pz+f
 Pn1kZEsB
 15s3XrVjQ8PPzt2zfJYNDW1kYm2oCAABvFqLm5GWq+VqtlGGZycvLBgwdkhmFZtra2luM4a2MJ9DZEbdHR0ejz589y9uvq6si4s3bt2jlz5thYDQDf/v7+MCyXlZXRtamjo0NeUpycnMBSHz58INyEhobu2LGDKSgokF8zf/78mZkZEq7Hjh2z1vMvXryYdD+QyRBCb968IQxt3rxZfiooKAhK086dO+HNvHnzRkZGRFFEQ0ND8gNGo7Gnp4dIf/bsWV9fX0WeXF1dwQmgjoLOMjMzSayNjY3JK25MTAzLslNTU69evYKUlpyc7ObmJooio7j1QQjl5OSQbIQxLisrU2wwMjIyCNrq1avJ+w8fPkALu3HjRskRDw8P6IGKi4vBfHq9HrKlIAhoZGREMXygfIJjIoRiY2Nzc3MlPKnV6vj4eDKDz507l/zU398PB0lpI+qJiopycXERRbG2thYhlJSUlJSUZGdn95tOSEiIonN4eXnBXoY0hxjjwsJCcivLsgkJCXTbRI8rer0eTJaRkUGqJJi4oaEBYzwzM7N8+fLDhw+TrP27H7KWZliWbW1tlQwGGONPnz4dOnRIp9NdunTJZDIBAtwNkQ9w+vRpODU5OblhwwbgSaPRNDU1wZFnz54lJiZK6p0oiszChQutMXThwgXSHNL9g2RrAwhTU1N0zt23bx+9ecrNzU1LS2tpaSFny8vLf/78KW9ObHXgOp0OLMLzfFNTE20d+RDS3t5On42PjxdmAfJNCLLBUGdn55MnT4Dr+vr6xMREKAuKa56SkhL6jZubm+0FAb1ytbarVAj+OXPmQL7q6+tTqVQ+Pj7d3d3ytWtNTQ0dYgzDpKamKq4irO2H/jT5er3e3t7eBls5OTnQM5WUlLi4uHh4eOzatSsvL+/FixfNzc2lpaVHjhyhvQegtLRU+J+AEQShtbV106ZNdHBKisP3799hts/NzbVWQ2hzeHl5gV7lKynFXZb
 CFtZoNK5
 du9YaT1u2bCGpaPv27fQQomhoGPVnuQZV0BDcND09nZ6e7uzsLNcBQujRo0dQCvr6+uLi4qzxBIlYsl6e/b72zxgELHMcNzo6GhYWJr8pPDwcchLGmOM4MlJJwNvb22Aw0Nlr9p8ZAFSpqam0JpycnHbu3BkYGGg2m7u7u8lPBoMBYxwaGooQQgitW7cuKirK09NzZmaGZVmMsU6nS0hIuHv3rqenJ2362YY0+fYgF4KoNz8/38PDgxRUhFBNTY3ikhXqkbVvMXJ8az4uiiKruNUmX154nm9sbGxqamptbe3s7PT396+oqAAFSA4SzwNB6U8t8JP8keDQyP8BCciofEZhq20AAAAASUVORK5CYII=
Content-Type: text/plain; charset="ISO-8859-1"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Sun, 2015-04-19 at 13:10 -0700, Jon Dufresne wrote:
> On Sun, Apr 19, 2015 at 10:42 AM, Tomas Vondra
> <tomas.vondra@2ndquadrant.com> wrote:
> > On 04/19/15 19:16, Jon Dufresne wrote:
> >> Given the table:
> >> CREATE TABLE dates (id SERIAL, d DATE NOT NULL, t TEXT NOT NULL)
> >> With an *index* on field d. The following two queries are functionally
> >> equivalent:
> >> 1. SELECT * FROM dates WHERE d >= '1900-01-01'
> >> 2. SELECT * FROM dates WHERE EXTRACT(year from d) >= 1900'
> >> By functionally equivalent, they will return the same result set.
> >> Query 2 does not use the index, adding a performance cost. It seems
> >> there is an opportunity for optimization to handle these two queries
> >> equivalently to take advantage of the index.
> > 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?
> 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. 

But they aren't functionally equivalent.  One is an index on a
datetime/date, the other is an index just on the year [a DOUBLE].
Date/datetimes potentially have time zones, integer values do not - in
general time values are an order of magnitude more complicated than
people expect.

> Shouldn't this is an optimization handled by the database
> so the user doesn't need to hand optimize these differences?

Sometimes "d >= '1900-01-01'" and "EXTRACT(year from d) >= 1900" may be
equivalent; but not always.

-- 
Adam Tauno Williams <mailto:awilliam@whitemice.org> GPG D95ED383
Systems Administrator, Python Developer, LPI / NCLA


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance