DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default;
	d=consistentstate.com;
	h=Received:From:Organization:To:Subject:Date:User-Agent:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-Id:X-Identified-User;
	b=Al2NY8tt1MqB0aFdIEMPz/sb0EjmokEPXOt7Vt8SMhhgMR8pzwvEKW2uIWadkZHOvh1n3wKTJXNY9TRiqOxv2Sb4ZtNSFDTQqPKjeLvokDDADmTdPIRHr0e5u+FLeYSn;
From: Kevin Kempter <kevink@consistentstate.com>
Organization: Consistent State
To: pgsql-performance@postgresql.org
Subject: partitioned tables query not using indexes
Date: Wed, 24 Feb 2010 07:36:36 -0700
User-Agent: KMail/1.12.4 (Linux/2.6.31.12-174.2.22.fc12.x86_64; KDE/4.3.5;
	x86_64; ; )
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201002240736.36341.kevink@consistentstate.com>

Hi All;

I have a table that has daily partitions. 

The check constraints look like this:
CHECK (timezone('EST'::text, insert_dt) >= '2010-01-01'::date 
AND timezone('EST'::text, insert_dt) < '2010-01-02'::date)

each partition has this index:
    "fact_idx1_20100101_on_cust_id" btree (cust_id)

If I run an explain hitting an individual partition I get an index scan:

explain select distinct cust_id from children.fact_20100101;

              QUERY PLAN                                                      
--------------------------------------------------------------------------------------------------------------
 Unique  (cost=0.00..136891.18 rows=70296 width=38)
   ->  Index Scan using fact_idx1_20100101_on_cust_id on fact_20100101  
(cost=0.00..133112.0


However the same query against the base table when specifying the check 
constraint key in the where clause produces sequential scans:


explain  select distinct cust_id from fact 
where timezone('EST'::text, insert_dt) between  '2010-01-01'::date 
and '2010-01-02'::date;

              QUERY PLAN                                                             
--------------------------------------------------------------------------------------
 HashAggregate  (cost=97671.06..97673.06 rows=200 width=38)
   ->  Result  (cost=0.00..97638.26 rows=13120 width=38)
         ->  Append  (cost=0.00..97638.26 rows=13120 width=38)
               ->  Seq Scan on fact  (cost=0.00..10.60 rows=1 width=98)
                     Filter: ((timezone('EST'::text, insert_dt) >= 
'2010-01-01'::date) AND (timezone('EST'::text, insert_dt) <= 
'2010-01-02'::date))
               ->  Seq Scan on fact_20100101 fact  (cost=0.00..56236.00 
rows=7558 width=38)
                     Filter: ((timezone('EST'::text, insert_dt) >= 
'2010-01-01'::date) AND (timezone('EST'::text, insert_dt) <= 
'2010-01-02'::date))
               ->  Seq Scan on fact_20100102 fact  (cost=0.00..41391.66 
rows=5561 width=38)
                     Filter: ((timezone('EST'::text, insert_dt) >= 
'2010-01-01'::date) AND (timezone('EST'::text, insert_dt) <= 
'2010-01-02'::date))


Thoughts?


Thanks in advance