From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: gunni@if.is
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Bad update performance?
Date: Wed, 21 Jul 1999 12:52:31 +0300
Message-ID: <l03130301b3bb40e52243@[147.233.159.109]> (raw)
In-Reply-To: <199907210228.TAA04635@uno.tksoft.com>
References: <000001bed304$78193380$65dea8c0@gunni.gi.hf.is>
At 05:28 +0300 on 21/07/1999, tjk@tksoft.com wrote:
> The table is stored in one chunk of a file, and when
> postgres modifies it, it takes a long time because
> it has to do a heck of a lot of seeking and modifying
> in the file. This ends up being very time consuming.
>
> When you use a separate table for the field you are
> isolating the field to its own file, which means
> that when it's modified only a much smaller file
> needs to be worked on.
This will give a penalty in the selects, however. When you split a table
into two, you have to have a relation between the field you separated and
the original table, and from then on, you can see them together only using
a join. A join takes more time than a search on one table.
I think that update is slow, because it has to read each record, and write
it in its new form in the end of the file. It does sequential scanning plus
jump to the end of the file and writing. That's essentially what you said,
too. In addition, it has to re-index each new entry.
But in my opinion, the chance to make this faster is to select the new
values into a new table, drop the old table, rename the new table, and add
indices after that. If your table is called A:
CREATE TABLE B ...; -- Same schema and constraints as A
INSERT INTO B SELECT f1, f2, 'unknown', f4... FROM A; -- Note new value
DROP TABLE A;
ALTER TABLE B RENAME TO A;
CREATE INDEX .....; -- Recreate the indices
When defining the table, don't use SERIAL and PRIMARY KEY, as these create
sequences and indices. You would do better to control these yourself, so as
not to break the sequences used, and not to create an index before all
values are inserted.
One last comment: indices always accelerate retrieval, at the expense of
slowing inserts and updates.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: herouth@oumail.openu.ac.il, gunni@if.is
Subject: Re: [SQL] Bad update performance?
In-Reply-To: <l03130301b3bb40e52243@[147.233.159.109]>
References: <000001bed304$78193380$65dea8c0@gunni.gi.hf.is> <l03130301b3bb40e52243@[147.233.159.109]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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