Re-Create Table make Faster. - Mailing list pgsql-general
| From | Yudha Setiawan |
|---|---|
| Subject | Re-Create Table make Faster. |
| Date | |
| Msg-id | 006801c321c8$576af8f0$ea00a8c0@yudha Whole thread Raw |
| Responses |
Automatic error rollback can be harmful
Re: Re-Create Table make Faster. |
| List | pgsql-general |
Dear Milist,
One day i got a problem with my table,
It's have 35.000 records and 33 Fields.
I tried to do Update a 30% records from
it. And I also have an Match Index with
my condition on Update. Before Updating
the table, I did the Vacuum and Reindex.
That table, But it's still taken a long
time it's about 30 Minutes. I've tried
Vacuum and Reindex many times for that.
But it still take a long-long time. And
Finaly in the middle of my confusing and
disperate i tried to drop and recreate my
table again. And my "Update" is walking
so fast. But i still confusing of how
could be like that...????, Somebody give
me some explanation please.
Here it is my Structure of My Table
---------------+-----------------------------+----------------
fc_branch | character(6) | not null
fc_stockcode | character(20) | not null
fv_stockname | character varying(40) | not null
fm_valuestock | numeric(30,5) | default 0
fm_hpp | numeric(30,5) | default 0
fn_onhand | numeric(8,0) | default 0
fn_allocated | numeric(8,0) | default 0
fn_valuealloc | numeric(18,0) | default 0
fn_tmpalloca | numeric(8,0) | default 0
fv_colorname | character varying(15) |
fv_colorcode | character varying(15) |
fd_lastupdate | timestamp without time zone |
fd_inputdate | timestamp without time zone |
fv_updateby | character varying(8) |
fd_lastsales | timestamp without time zone |
fd_lastpurch | timestamp without time zone |
fc_divisi | character(2) |
fc_brand | character(2) | not null
fc_group | character(2) | not null
fc_subgrp | character(2) | not null
fc_type | character(2) | not null
fc_pack | character(2) | not null
fn_reorder | numeric(8,0) | default 0
fn_outstpurch | numeric(8,0) | default 0
fn_outstsales | numeric(8,0) | default 0
fn_uninvoiced | numeric(18,0) | default 0
fn_valueuninv | numeric(18,0) | default 0
fn_openblnc | numeric(8,0) | default 0
fn_maxpurch | numeric(8,0) | default 0
fn_minpurch | numeric(8,0) | default 0
fn_maxsales | numeric(8,0) | default 0
fn_minsales | numeric(8,0) | default 0
fn_maxstock | numeric(8,0) | default 0
fn_minstock | numeric(8,0) | default 0
fc_gradeinout | character(6) | default 'SLOW'
fc_hold | character(3) | default 'NO'
fc_pictureclr | character(3) | default 'NO'
fc_report | character(3) | default 'NO'
fn_volume | numeric(9,5) | default 0
fm_lasthpp | numeric(30,5) | default 0
fm_lastprice | numeric(30,5) | default 0
fn_lastdisc1 | numeric(18,0) | default 0
fn_lastdisc2 | numeric(18,0) | default 0
fn_lastdisc3 | numeric(18,0) | default 0
ft_note | text |
Indexes: pk_t_stock1 primary key btree (fc_branch, fc_stockcode),
i_stock01 btree (fc_branch, fc_stockcode),
i_stock02 btree (fc_branch, fv_stockname)
fc_branch | character(6) | not null
fc_stockcode | character(20) | not null
fv_stockname | character varying(40) | not null
fm_valuestock | numeric(30,5) | default 0
fm_hpp | numeric(30,5) | default 0
fn_onhand | numeric(8,0) | default 0
fn_allocated | numeric(8,0) | default 0
fn_valuealloc | numeric(18,0) | default 0
fn_tmpalloca | numeric(8,0) | default 0
fv_colorname | character varying(15) |
fv_colorcode | character varying(15) |
fd_lastupdate | timestamp without time zone |
fd_inputdate | timestamp without time zone |
fv_updateby | character varying(8) |
fd_lastsales | timestamp without time zone |
fd_lastpurch | timestamp without time zone |
fc_divisi | character(2) |
fc_brand | character(2) | not null
fc_group | character(2) | not null
fc_subgrp | character(2) | not null
fc_type | character(2) | not null
fc_pack | character(2) | not null
fn_reorder | numeric(8,0) | default 0
fn_outstpurch | numeric(8,0) | default 0
fn_outstsales | numeric(8,0) | default 0
fn_uninvoiced | numeric(18,0) | default 0
fn_valueuninv | numeric(18,0) | default 0
fn_openblnc | numeric(8,0) | default 0
fn_maxpurch | numeric(8,0) | default 0
fn_minpurch | numeric(8,0) | default 0
fn_maxsales | numeric(8,0) | default 0
fn_minsales | numeric(8,0) | default 0
fn_maxstock | numeric(8,0) | default 0
fn_minstock | numeric(8,0) | default 0
fc_gradeinout | character(6) | default 'SLOW'
fc_hold | character(3) | default 'NO'
fc_pictureclr | character(3) | default 'NO'
fc_report | character(3) | default 'NO'
fn_volume | numeric(9,5) | default 0
fm_lasthpp | numeric(30,5) | default 0
fm_lastprice | numeric(30,5) | default 0
fn_lastdisc1 | numeric(18,0) | default 0
fn_lastdisc2 | numeric(18,0) | default 0
fn_lastdisc3 | numeric(18,0) | default 0
ft_note | text |
Indexes: pk_t_stock1 primary key btree (fc_branch, fc_stockcode),
i_stock01 btree (fc_branch, fc_stockcode),
i_stock02 btree (fc_branch, fv_stockname)
i_stock03 btree (fc_branch, fc_group)
And this is my "Update",
Update t_stock set fc_onhand = 50 where fc_branch = 'ABCDE' and fc_group = 'G1';
Thank's and GOD Bless U all.
pgsql-general by date: