Re: [SQL] Bad update performance?
От | tjk@tksoft.com |
---|---|
Тема | Re: [SQL] Bad update performance? |
Дата | |
Msg-id | 199907210228.TAA04635@uno.tksoft.com обсуждение исходный текст |
Ответ на | Bad update performance? ("Gunnar Ingvi Thorisson" <gunni@if.is>) |
Ответы |
Re: [SQL] Bad update performance?
(Herouth Maoz <herouth@oumail.openu.ac.il>)
|
Список | pgsql-sql |
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; > > > > >
В списке pgsql-sql по дате отправления: