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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
