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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Kempter | 2010-02-24 15:18:41 | Re: partitioned tables query not using indexes |
Previous Message | Igor Neyman | 2010-02-24 14:46:31 | Re: Internal operations when the planner makes a hash join. |