|
From: Ho-Sheng H. <qa...@ne...> - 2002-01-06 09:31:49
|
Hi I noticed that my install of PostgreSQL didn't add indexes to the tables. I have heard that those indexes were automatically created... but I listed them with \dt on the psql tool, and there were only one index, the one for the chart table. I ran an informal test on an "end-of-day" module using data from this client. There were 13 transactions. The stock install of RedHat PostgreSQL 7.1 install took approx 8 to 9 seconds for it to load. I followed the optimization hints on PHPBuilder and increased the shared buffer and sort memory, which shaved it down to 7 seconds. (Shared memory from 1 MB to 50 MB. There were postings on there that says that all that really does was force the kernel to use less memory for disk cache. I do not know.) Then I added the indexes: unique indexes for ar, ap, parts, customer, vendor, and various integer and text fields in those and acc_trans, the taxes, etc. etc. including one that indexes the lower-cased version of the customer/vendor/partnumber names. That same app went from 7 seconds to 0.7 seconds the _first_ time. The subsequent queries were 0.2 seconds. This was all done on a PII 200 with only 64 MB of ram, running the Apache and Samba, during off-business hours. A difference of 10 seconds down to less than 1 second is pretty significant, especially considering that the people up front doing the point-of-sale need to access the data quick. The bookkeeper working here gets frustrated at the "slowness" of the "new system" which discouraged the adoption. I'll see how the psychological impact tommorrow ... I figure that the extra time it takes to do inserts was less noticable than the improvements on select queries. Another informal and non-rigourous test used about 1 year worth of backdata in accounts receivable to do the Aging function. Before indexes = timing out the browser (the operation could not be completed). Now, it's completed in about 20 seconds. What I am wondering is if anyone has ran into performance problems and have fixed/optimized/tuned/tweaked and is willing to share. -- -Qaexl- |