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
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 |