pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat is wrong ?
3+ messages / 3 participants
[nested] [flat]
* What is wrong ?
@ 2007-02-17 00:07 Abu Mushayeed <abumushayeed@yahoo.com>
2007-02-17 02:21 ` Re: What is wrong ? v13nr <anangr2006@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Abu Mushayeed @ 2007-02-17 00:07 UTC (permalink / raw)
To: pgsql-admin
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!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: What is wrong ?
2007-02-17 00:07 What is wrong ? Abu Mushayeed <abumushayeed@yahoo.com>
@ 2007-02-17 02:21 ` v13nr <anangr2006@gmail.com>
2007-02-17 15:13 ` Re: What is wrong ? Ray Stell <stellr@cns.vt.edu>
0 siblings, 1 reply; 3+ messages in thread
From: v13nr @ 2007-02-17 02:21 UTC (permalink / raw)
To: pgsql-admin
how to unsubscribe from this mailing list
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: What is wrong ?
2007-02-17 00:07 What is wrong ? Abu Mushayeed <abumushayeed@yahoo.com>
2007-02-17 02:21 ` Re: What is wrong ? v13nr <anangr2006@gmail.com>
@ 2007-02-17 15:13 ` Ray Stell <stellr@cns.vt.edu>
0 siblings, 0 replies; 3+ messages in thread
From: Ray Stell @ 2007-02-17 15:13 UTC (permalink / raw)
To: v13nr <anangr2006@gmail.com>; +Cc: pgsql-admin
On Sat, Feb 17, 2007 at 10:21:49AM +0800, v13nr wrote:
> how to unsubscribe from this mailing list
visit here:
http://archives.postgresql.org/pgsql-admin/
there's an unsubscribe section.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-02-17 15:13 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-17 00:07 What is wrong ? Abu Mushayeed <abumushayeed@yahoo.com>
2007-02-17 02:21 ` v13nr <anangr2006@gmail.com>
2007-02-17 15:13 ` Ray Stell <stellr@cns.vt.edu>
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