The following bug has been logged online:

Bug reference: 1697
Logged by: Bahadur Singh
Email address: bahadursingh (AT) yahoo (DOT) com
PostgreSQL version: 8.0
Operating system: Windows 2000 server
Description: Select getting slower on continously updating data
Details:

Hello,

I found situtation that, when I am selecting data from a table of 200
records, getting slower as I do continous update to the same existing data.



CREATE TABLE salesarticle
(
articlenumber char(20) NOT NULL,
price int4 NOT NULL,
eodid int4 NOT NULL,
departmentnumber char(4) NOT NULL,
keycounter int4 NOT NULL,
scancounter int4 NOT NULL,
grosssalescounter int8 NOT NULL,
grosssalesamount int8 NOT NULL,
discountcounter int8 NOT NULL,
discountamount int8 NOT NULL,
reductioncounter int8 NOT NULL,
reductionamount int8 NOT NULL,
transactioncounter int4 NOT NULL,
promotionamount int8 NOT NULL,
promotioncounter int8 NOT NULL,
datelastsale char(14) NOT NULL,
CONSTRAINT salesarticle_pkey PRIMARY KEY (articlenumber, price, eodid),
CONSTRAINT salesarticle_eodid_fkey FOREIGN KEY (eodid) REFERENCES eodinfo
(eodid) ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH OIDS;

This is my select statement:

EXPLAIN ANALYZE
SELECT ArticleNumber, Price, EodId FROM SalesArticle WHERE ArticleNumber IN
(' 9502',
' 9500',' 9501',' 9505','
9506',' 9507',' 9515',
' 9516',' 9518',' 9520','
9472',' 9508',' 9546',
' 3322',' 9521' ) AND EodId = 12
"Index Scan using salesarticle_pkey, salesarticle_pkey, salesarticle_pkey,
salesarticle_pkey, salesarticle_pkey, salesarticle_pkey, salesarticle_pkey,
salesarticle_pkey, salesarticle_pkey, salesarticle_pkey, salesarticle_pkey,
salesarticle_pkey, salesarticl (..)"
" Index Cond: ((articlenumber = ' 9502'::bpchar) OR
(articlenumber = ' 9500'::bpchar) OR (articlenumber = '
9501'::bpchar) OR (articlenumber = ' 9505'::bpchar)
OR (articlenumber = ' (..)"
" Filter: (eodid = 12)"
"Total runtime: 47.000 ms"

The first iteration(400 times selects and update that selected data ) say
400 are within 2 sec, then it keep on increasing at the end, it take 9
seconds to execute 100 selects and updates on the database. No new records
are added during this operation.

perfromace of above select degrade as follows
= 16 ms ==> yealds 1600 ms for 100 iteration.
= 32 ms ==> yealds 3200 ms for 100 it...
= 47 ms ==> yealds 4700 ms for 100 it...
= 80 ms ==> yealds 80000 ms for 100 it...
= 104 ms ==> yealds 10400 ms for 100 it...

when I create an index on PK of this table, it boosts select performance to
16 ms, but update stmts are slowing down. I do insert only once in begining
and then update them continously as long I recieve same input data. (means
no insert take place in between on this salesArticle table.)

Please advice me some solution or any trick.


Thanks in Advance,
Bahadur

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

This does not belong on the pgsql-bugs list. The pgsql-novice or
pgsql-performance lists seem more appropiate. I have set followups
to the pgsql-novice list.

On Thu, Jun 02, 2005 at 12:05:00 +0100,
Bahadur Singh <bahadursingh (AT) yahoo (DOT) com> wrote:
You need to be vacuuming (and possibly analyzing) the table more often as the
updates will leave dead rows in the table which will bloat the table size and
slow down access, particularly sequential scans. If the updates modify the
data value distributions significantly, then you will also need to
reanalyze the table to help the planner make good decisions.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

--- Bruno Wolff III <bruno (AT) wolff (DOT) to> wrote:

Many thanks for this tip !
But is this good idea to analyse/vacuuming the
database tables while updates are taking place..
Since, I update continuously say (100,000 ) times or
more the same data set.

This is the result of analyze command.

INFO: analyzing "public.salesarticle"
INFO: "salesarticle": scanned 3000 of 20850 pages,
containing 62 live rows and 134938 dead rows; 62 rows
in sample, 431 estimated total rows

Gesamtlaufzeit der Abfrage: 5531 ms.
Total Time Taken : 5531 ms.

Can you suggest me some clever way to so, because I
would prefer to do vaccumming while database is not
loaded with queries/transactions.

Regards
Bahadur




__________________________________
Discover Yahoo!
Find restaurants, movies, travel and more fun for the weekend. Check it out!
http://discover.yahoo.com/weekend.html


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Fri, Jun 03, 2005 at 00:09:00 -0700,
Bahadur Singh <bahadursingh (AT) yahoo (DOT) com> wrote:
While that may be a nice preference, under your usage pattern that does
not appear to be a good idea. As long as your disk I/O isn't saturated
you want to be running vacuums a lot more often than you are. (Analyze should
only be needed if the distrution of values is changing constantly. An example
would be timestamps indicating when an update occured.)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)