Skip site navigation (1) Skip section navigation (2)

Re: partitioned tables query not using indexes

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: partitioned tables query not using indexes
Date: 2010-02-24 14:55:36
Message-ID: 20100224145536.GM22249@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-performance
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?



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

Maybe you should try to rewrite your code to:

between  '2010-01-01 00:00'::timestamp and ...


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

In response to

Responses

pgsql-performance by date

Next:From: Kevin KempterDate: 2010-02-24 15:18:41
Subject: Re: partitioned tables query not using indexes
Previous:From: Igor NeymanDate: 2010-02-24 14:46:31
Subject: Re: Internal operations when the planner makes a hash join.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group