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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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