Re: [PERFORM] partitioned tables query not using indexes
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] partitioned tables query not using indexes
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] partitioned tables query not using indexes
> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 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 > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] partitioned tables query not using indexes
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] partitioned tables query not using indexes
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance