Re: partitioned tables query not using indexes

On Wednesday 24 February 2010 07:55:36 A. Kretschmer wrote:
> In response to Kevin Kempter :
> > 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:
>
> Have you set constraint_exclusion = on?

Yes.

>
> > explain select distinct cust_id from fact
> > where timezone('EST'::text, insert_dt) between '2010-01-01'::date
> > and '2010-01-02'::date;
>
> Can you show the table definition? I'm not sure about the
> timezone()-function and index...

Table "fact_20100101"
Column | Type | Modifiers
-----------------------------+---------------------------------------+-----------
insert_dt | timestamp with time zone |
cust_order_id | integer |
user_row_id | integer |
cust_id | character varying(40) |
order_items | integer |
catalog_id | integer |
online_order_id_num | character varying(255) |
order_id | integer |
promotion_key | integer |
sales_region_id | integer |
country_id | integer |
Indexes:
index_fact_20100101_on_insert_dt btree (insert_dt)
index_fact_20100101_on_catalog_id btree (catalog_id)
index_fact_20100101_on_promotion_key btree (promotion_key)
index_fact_20100101_on_order_id btree (order_id)
index_fact_20100101_on_cust_order_id btree (cust_order_id)
index_fact_20100101_on_user_row_id btree (user_row_id)
index_fact_20100101_on_cust_id btree (cust_id)
Check constraints:
fact_20100101_insert_dt_check CHECK (timezone('EST'::text, insert_dt) >=
'2010-01-01'::date
AND timezone('EST'::text, insert_dt) < '2010-01-02'::date)
Foreign-key constraints:
fk_country_id" FOREIGN KEY (country_id) REFERENCES country_dim(id)
fk_catalog_id" FOREIGN KEY (catalog_id) REFERENCES catalog_dim(id)
fk_promotion_key" FOREIGN KEY (promotion_key) REFERENCES promotion_dim(id)
fk_order_id" FOREIGN KEY (order_id) REFERENCES order_dim(id)
Inherits: fact

>
> Maybe you should try to rewrite your code to:
>
> between '2010-01-01 00:00'::timestamp and ...
This (and other date variations gives me index scans however each time I get
the planner to do an index scan it also refuses to do partition exclusion. The
original query above gives me partition exclusion but table scans (no index
scans)

>
>
> Andreas
>