DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default;
	d=consistentstate.com;
	h=Received:From:Organization:To:Subject:Date:User-Agent:References:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-Id:X-Identified-User;
	b=A5EFpdLeN/DTA4eYicHNrzmmRLEt6yabqSG0pQBjYiRhDRKG2wj6WW1VGa4mPNEant59lRZ7di9fg9VeFQ5zMBxVPIcbl8OYGIJg6vG1LeA6JSm6wAI3rF38NDrrulGs;
From: Kevin Kempter <kevink@consistentstate.com>
Organization: Consistent State
To: pgsql-performance@postgresql.org
Subject: Re: partitioned tables query not using indexes
Date: Wed, 24 Feb 2010 08:18:41 -0700
User-Agent: KMail/1.12.4 (Linux/2.6.31.12-174.2.22.fc12.x86_64; KDE/4.3.5;
	x86_64; ; )
References: <201002240736.36341.kevink@consistentstate.com>
	<20100224145536.GM22249@a-kretschmer.de>
In-Reply-To: <20100224145536.GM22249@a-kretschmer.de>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201002240818.42010.kevink@consistentstate.com>

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
>