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

partitioned tables query not using indexes

From: Kevin Kempter <kevink(at)consistentstate(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: partitioned tables query not using indexes
Date: 2010-02-24 14:36:36
Message-ID: 201002240736.36341.kevink@consistentstate.com (view raw or flat)
Thread:
Lists: pgsql-performance
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



Responses

pgsql-performance by date

Next:From: Igor NeymanDate: 2010-02-24 14:46:31
Subject: Re: Internal operations when the planner makes a hash join.
Previous:From: Dave CrookeDate: 2010-02-24 08:32:40
Subject: Re: SSD + RAID

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