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))); regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
