Skip site navigation (1) Skip section navigation (2)

Re: pattern matching with dates?

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 (view raw or flat)
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

In response to

pgsql-sql by date

Next:From: Russell GalyonDate: 2011-01-08 04:48:17
Subject: Re: return records with more than one occurrences
Previous:From: pasman pasmańskiDate: 2011-01-07 14:15:03
Subject: Re: pattern matching with dates?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group