From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | pasman pasmański <pasman(dot)p(at)gmail(dot)com> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: pattern matching with dates? |
Date: | 2011-01-07 16:09:50 |
Message-ID: | 4D273ACE.5070508@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 07/01/11 14:15, pasman pasmański wrote:
> Sorry, but this examples not use index.
Looks like it does here (oh, and please try to post your replies at the
bottom of the message)
> On 1/7/11, Susanne Ebrecht<susanne(at)2ndquadrant(dot)com> wrote:
>> The more proper way is:
>> SELECT * from tab WHERE EXTRACT(YEAR FROM log_date) = 2011 and
>> EXTRACT(MONTH FROM log_date) = 1;
CREATE TABLE d_table (d date);
INSERT INTO d_table SELECT '2001-01-01'::date + generate_series(1,9999);
CREATE INDEX d_extract_idx ON d_table ( extract('day' FROM d) );
EXPLAIN ANALYSE SELECT * FROM d_table WHERE extract('day' FROM d) = 3;
That ends up doing a bitmap index scan for me.
Of course, it's entirely possible an index on year+month returns too
many rows to be useful.
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Russell Galyon | 2011-01-08 04:48:17 | Re: return records with more than one occurrences |
Previous Message | pasman pasmański | 2011-01-07 14:15:03 | Re: pattern matching with dates? |