pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpartitioned tables query not using indexes
6+ messages / 4 participants
[nested] [flat]
* partitioned tables query not using indexes
@ 2010-02-24 14:36 Kevin Kempter <kevink@consistentstate.com>
0 siblings, 2 replies; 6+ messages in thread
From: Kevin Kempter @ 2010-02-24 14:36 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: partitioned tables query not using indexes
@ 2010-02-24 14:55 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Kevin Kempter <kevink@consistentstate.com>
1 sibling, 1 reply; 6+ messages in thread
From: A. Kretschmer @ 2010-02-24 14:55 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: partitioned tables query not using indexes
@ 2010-02-24 15:18 Kevin Kempter <kevink@consistentstate.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 6+ messages in thread
From: Kevin Kempter @ 2010-02-24 15:18 UTC (permalink / raw)
To: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: partitioned tables query not using indexes
@ 2010-02-28 20:29 Josh Berkus <josh@agliodbs.com>
parent: Kevin Kempter <kevink@consistentstate.com>
1 sibling, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2010-02-28 20:29 UTC (permalink / raw)
To: Kevin Kempter <kevink@consistentstate.com>; +Cc: pgsql-performance
> However the same query against the base table when specifying the check
> constraint key in the where clause produces sequential scans:
Does the "master" table have the same indexes as the slave partitions?
--Josh Berkus
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: partitioned tables query not using indexes
@ 2010-02-28 21:51 Kenneth Marshall <ktm@rice.edu>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kenneth Marshall @ 2010-02-28 21:51 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Kevin Kempter <kevink@consistentstate.com>; pgsql-performance
On Sun, Feb 28, 2010 at 12:29:14PM -0800, Josh Berkus wrote:
>
> > However the same query against the base table when specifying the check
> > constraint key in the where clause produces sequential scans:
>
> Does the "master" table have the same indexes as the slave partitions?
>
> --Josh Berkus
>
Does this help? I have an empty base table without indexes and partitions
underneath that do have the index. I did not think that an index on the
parent table did anything.
Cheers,
Ken
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: partitioned tables query not using indexes
@ 2010-02-28 22:46 Josh Berkus <josh@agliodbs.com>
parent: Kenneth Marshall <ktm@rice.edu>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2010-02-28 22:46 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: Kevin Kempter <kevink@consistentstate.com>; pgsql-performance
On 2/28/10 1:51 PM, Kenneth Marshall wrote:
> On Sun, Feb 28, 2010 at 12:29:14PM -0800, Josh Berkus wrote:
>>> However the same query against the base table when specifying the check
>>> constraint key in the where clause produces sequential scans:
>> Does the "master" table have the same indexes as the slave partitions?
>>
>> --Josh Berkus
>>
> Does this help? I have an empty base table without indexes and partitions
> underneath that do have the index. I did not think that an index on the
> parent table did anything.
I'm not sure that it does, but "try it and see" is easier than reading
the planner code.
--Josh Berkus
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-02-28 22:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-24 14:36 partitioned tables query not using indexes Kevin Kempter <kevink@consistentstate.com>
2010-02-24 14:55 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2010-02-24 15:18 ` Kevin Kempter <kevink@consistentstate.com>
2010-02-28 20:29 ` Josh Berkus <josh@agliodbs.com>
2010-02-28 21:51 ` Kenneth Marshall <ktm@rice.edu>
2010-02-28 22:46 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox