Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 3EA8B9FB42B for ; Fri, 16 Feb 2007 20:08:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 99159-02 for ; Fri, 16 Feb 2007 20:07:54 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from web57101.mail.re3.yahoo.com (web57101.mail.re3.yahoo.com [216.252.111.114]) by postgresql.org (Postfix) with SMTP id 68BD49FB40C for ; Fri, 16 Feb 2007 20:07:58 -0400 (AST) Received: (qmail 10927 invoked by uid 60001); 17 Feb 2007 00:07:57 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=sMSd/Tlt8y5XPnuvwE+5LSEgktJ5m4UnBX/4LnMIeETkl2XmPU/ZskQq46VARETuKrdtk68hz64Gw9CWIlv3aWro1nMNKEGAsv84L5gURMv4urb8BcjgDSaau9Q1d1Q9EUU6jLTFO15yogF15nPB0JztESr+ZzQTb5wkqWFbr44=; X-YMail-OSG: y4siUnEVM1mbivUtGSJLHrUtshV5_Olwqy9A39LJf6qMuG9ztGVBIk22L_x60JtLiYoNAt3lu7P8EywpsdZEFjHRnfaDWukE71_4JDU0D1mkpUHF1fiJOClg65Uloe0- Received: from [64.237.4.140] by web57101.mail.re3.yahoo.com via HTTP; Fri, 16 Feb 2007 16:07:56 PST Date: Fri, 16 Feb 2007 16:07:56 -0800 (PST) From: Abu Mushayeed Subject: What is wrong ? To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1383368277-1171670876=:9977" Content-Transfer-Encoding: 8bit Message-ID: <952896.9977.qm@web57101.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.108 tagged_above=0 required=5 tests=BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_10_20, HTML_MESSAGE X-Spam-Level: ** X-Archive-Number: 200702/252 X-Sequence-Number: 24717 --0-1383368277-1171670876=:9977 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit I have created a rule on a table as folows: CREATE OR REPLACE RULE edw_item_avail_200606 AS ON INSERT TO edw_item_avail WHERE "substring"(new.amc_week_id::text, 1, 6) = '200606'::text DO INSTEAD INSERT INTO mpartition.edw_item_avail_200308 (item_avail_key, prod_dim_id, amc_week_id, edw_prod_dim_id, zl_store_nbr, ticketed_retail, owned_retail, qty_sales_units, qty_return_units, qty_new_rcv_units, qty_rcv_adj_units, qty_transin_units, qty_transout_units, qty_inv_adj_units, qty_rtv_in_units, qty_rtv_out_units, qty_bal_units, qty_end_stk_units, qty_begin_stk_units, create_date, zl_divn_nbr) VALUES (new.item_avail_key, new.prod_dim_id, new.amc_week_id, new.edw_prod_dim_id, new.zl_store_nbr, new.ticketed_retail, new.owned_retail, new.qty_sales_units, new.qty_return_units, new.qty_new_rcv_units, new.qty_rcv_adj_units, new.qty_transin_units, new.qty_transout_units, new.qty_inv_adj_units, new.qty_rtv_in_units, new.qty_rtv_out_units, new.qty_bal_units, new.qty_end_stk_units, new.qty_begin_stk_units, new.create_date, new.zl_divn_nbr); Now, when I issue the following query: insert into edw_item_avail select * from public."edw_item_avail_ORIG" where substring(amc_week_id::text,1,6) = 200606::text ; The explain is as follows: Seq Scan on "edw_item_avail_ORIG" (cost=0.00..705569.97 rows=81321 width=107) Filter: (("substring"((amc_week_id)::text, 1, 6) = '200606'::text) AND (("substring"((amc_week_id)::text, 1, 6) = '200606'::text) IS NOT TRUE)) Seq Scan on "edw_item_avail_ORIG" (cost=0.00..705569.97 rows=409 width=107) Filter: (("substring"((amc_week_id)::text, 1, 6) = '200606'::text) AND ("substring"((amc_week_id)::text, 1, 6) = '200606'::text)) My problem is when I have more rules all are appended and it is taking a long time to move data from the "ORIG" table to the actually partitioned table. The partitioned table are based on substring(amc_week_id::text,1,6). If someone can shed some light on this, it will be very helpful. Thanks Abu --------------------------------- Never Miss an Email Stay connected with Yahoo! Mail on your mobile. Get started! --0-1383368277-1171670876=:9977 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
I have created a rule on a table as folows:
 
CREATE OR REPLACE RULE edw_item_avail_200606 AS
    ON INSERT TO edw_item_avail
   WHERE "substring"(new.amc_week_id::text, 1, 6) = '200606'::text DO INSTEAD  INSERT INTO mpartition.edw_item_avail_200308 (item_avail_key, prod_dim_id, amc_week_id, edw_prod_dim_id, zl_store_nbr, ticketed_retail, owned_retail, qty_sales_units, qty_return_units, qty_new_rcv_units, qty_rcv_adj_units, qty_transin_units, qty_transout_units, qty_inv_adj_units, qty_rtv_in_units, qty_rtv_out_units, qty_bal_units, qty_end_stk_units, qty_begin_stk_units, create_date, zl_divn_nbr)
  VALUES (new.item_avail_key, new.prod_dim_id, new.amc_week_id, new.edw_prod_dim_id, new.zl_store_nbr, new.ticketed_retail, new.owned_retail, new.qty_sales_units, new.qty_return_units, new.qty_new_rcv_units, new.qty_rcv_adj_units, new.qty_transin_units, new.qty_transout_units, new.qty_inv_adj_units, new.qty_rtv_in_units, new.qty_rtv_out_units, new.qty_bal_units, new.qty_end_stk_units, new.qty_begin_stk_units, new.create_date, new.zl_divn_nbr);
 
Now, when I issue the following query:
 
insert into edw_item_avail
select * from public."edw_item_avail_ORIG"
where  substring(amc_week_id::text,1,6) = 200606::text ;
 The explain is as follows:
 
Seq Scan on "edw_item_avail_ORIG"  (cost=0.00..705569.97 rows=81321 width=107)
  Filter: (("substring"((amc_week_id)::text, 1, 6) = '200606'::text) AND (("substring"((amc_week_id)::text, 1, 6) = '200606'::text) IS NOT TRUE))
Seq Scan on "edw_item_avail_ORIG"  (cost=0.00..705569.97 rows=409 width=107)
  Filter: (("substring"((amc_week_id)::text, 1, 6) = '200606'::text) AND ("substring"((amc_week_id)::text, 1, 6) = '200606'::text))
My problem is when I have more rules all are appended and it is taking a long time to move data from the "ORIG" table to the actually partitioned table. The partitioned table are based on substring(amc_week_id::text,1,6).
 
If someone can shed some light on this, it will be very helpful.
 
Thanks
Abu


Never Miss an Email
Stay connected with Yahoo! Mail on your mobile. Get started! --0-1383368277-1171670876=:9977--