Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 66BDF1337BC3
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri,  7 Jan 2011 12:09:58 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 97614-10 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri,  7 Jan 2011 16:09:51 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from ibook.office.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by mail.postgresql.org (Postfix) with ESMTP id 0184B1337BDE
	for <pgsql-sql@postgresql.org>; Fri,  7 Jan 2011 12:09:50 -0400 (AST)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by ibook.office.archonet.com (Postfix) with ESMTP id EAB617C118;
	Fri,  7 Jan 2011 16:09:49 +0000 (GMT)
Message-ID: <4D273ACE.5070508@archonet.com>
Date: Fri, 07 Jan 2011 16:09:50 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.13) Gecko/20101208 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
To: =?UTF-8?B?cGFzbWFuIHBhc21hxYRza2k=?= <pasman.p@gmail.com>
CC: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: pattern matching with dates?
References: 
 <FA041D403EDFCD4397E1F6382360ECFCFDB5697143@SVCMCEMAIL.svcmc.svcmcny.org>
	<4D270993.302@2ndQuadrant.com>
	<AANLkTi=pahSTCHk07nj79KjSoXa8DhqUv5mA7Z3FdcEX@mail.gmail.com>
In-Reply-To: <AANLkTi=pahSTCHk07nj79KjSoXa8DhqUv5mA7Z3FdcEX@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.918 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RDNS_DYNAMIC=0.982
X-Spam-Level: 
X-Archive-Number: 201101/19
X-Sequence-Number: 34966

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