Re-Create Table make Faster.

From: "Yudha Setiawan" <inas_husband(at)bonbon(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Re-Create Table make Faster.
Date: 2003-05-24 07:44:36
Message-ID: 006801c321c8$576af8f0$ea00a8c0@yudha
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)
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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Daniel Carlsson 2003-05-24 10:08:48 Trigger in transaction
Previous Message Bruce Momjian 2003-05-24 01:53:24 Re: Building 7.3.2 on HP-UX w/out zlib/readline