pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: tjk@tksoft.com <tjk@tksoft.com>
To: gunni@if.is
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Bad update performance?
Date: Tue, 20 Jul 1999 19:28:40 -0700 (PDT)
Message-ID: <199907210228.TAA04635@uno.tksoft.com> (raw)
In-Reply-To: <000001bed304$78193380$65dea8c0@gunni.gi.hf.is>

Gunnar,
You would be better off separating the field to its
own table. 

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.

It might be a nice idea to split large tables into 
smaller pieces within postgres, in order to speed
up updates. (This is a really a question to 
somebody more knowleadgeable about the backend
as to whether this would make sense.)

Because you are indexing the field, searches are fast,
as postgres knows where to look inside the file (using seek)
using the index as a guide.



Troy

Troy Korjuslommi                Tksoft OY, Inc.
tjk@tksoft.com                  Software Development
                                Open Source Solutions
                                Hosting Services







> 
> Hi there,
> 
> I´ve a table with about 142000 rows like shown below and I want to set field
> "divis" to "unknown" by executing following update command:
> update ipacct set divis = 'unknown';
> 
> However this seems to take hours, I´ve a PII 350MHz with 192Mb memory and
> Quantum UIDE disk, is there a way for me to speed up the update process
> except  for switching to a SCSI hard drive? Does indexing the field "divis"
> speed up the update performance?
> 
> The load on the machine is about 1,5 to 2 all the time and it´s hacking on
> the hard drive like hell.
> I´ve the same problem when I do "UPDATE ipacct SET traf = (src+dst)", it
> takes hours. Is this normal? Is there another way to do this?
> 
> However reading from the database doing select is very fast.
> 
> Many thanks in advance for any hint,
> best regards,
> Gunnar Ingvi Thorisson
> 
> CCIE 4826
> Iceland
> gunni@if.is
> 
> 
> 
> venus:~$ psql ipacct
> Welcome to the POSTGRESQL interactive sql monitor:
>   Please read the file COPYRIGHT for copyright terms of POSTGRESQL
> [PostgreSQL 6.5.0 on i686-pc-linux-gnulibc1, compiled by gcc 2.7.2.3]
> 
> [cut]
> 
> ipacct=> select * from ipacct where id = 1;
> id| datetime|pcol|saddr            |sport|daddr       |dport|pcnt| size|if
> |login  |mac         |src|dst|traf|depart|divis
> --+---------+----+-----------------+-----+------------+-----+----+-----+----
> +-------+------------+---+---+----+------+-----
>  1|928368167|   6|209.85.127.151/32|   80|10.10.2.3/32| 2448|
> 57|74856|eth0|unknown|00805FC1525C|100|  3| 103|      |
> (1 row)
> 
> ipacct=> update ipacct set divis = 'unknown';
> 
> The table:
>    ID               INT4  UNIQUE,
>    DATETIME         TEXT,
>    PCOL             INTEGER,
>    SADDR            CIDR,
>    SPORT            INTEGER,
>    DADDR            CIDR,
>    DPORT            INTEGER,
>    PCNT             INT4
>    SIZE             INT4;
>    IF               TEXT;
>    LOGIN            TEXT;
>    MAC              TEXT;
>    SRC              INTEGER;
>    DST              INTEGER;
>    TRAF             INTEGER;
>    DEPART           TEXT;
>    DIVIS            TEXT;
> 
> 
> 
> 
> 




view thread (4+ messages)  latest in thread

Message-ID: <199907210228.TAA04635@uno.tksoft.com>
Permalink:  ../199907210228.TAA04635@uno.tksoft.com/
Also on:    postgresql.org/message-id/199907210228.TAA04635@uno.tksoft.com

 · 

reply

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: tjk@tksoft.com, gunni@if.is
  Subject: Re: [SQL] Bad update performance?
  In-Reply-To: <199907210228.TAA04635@uno.tksoft.com>
  References: <000001bed304$78193380$65dea8c0@gunni.gi.hf.is> <199907210228.TAA04635@uno.tksoft.com>

* 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