From: Richard Huxton <dev@archonet.com>
To: pasman pasmański <pasman.p@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: pattern matching with dates?
Date: Fri, 07 Jan 2011 16:09:50 +0000
Message-ID: <4D273ACE.5070508@archonet.com> (raw)
In-Reply-To: <AANLkTi=pahSTCHk07nj79KjSoXa8DhqUv5mA7Z3FdcEX@mail.gmail.com>
References: <FA041D403EDFCD4397E1F6382360ECFCFDB5697143@SVCMCEMAIL.svcmc.svcmcny.org>
<4D270993.302@2ndQuadrant.com>
<AANLkTi=pahSTCHk07nj79KjSoXa8DhqUv5mA7Z3FdcEX@mail.gmail.com>
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@2ndquadrant.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
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-sql@postgresql.org
Cc: dev@archonet.com, pasman.p@gmail.com
Subject: Re: pattern matching with dates?
In-Reply-To: <4D273ACE.5070508@archonet.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