Indexes

[Ho-Sheng H. <qa...@ne...>]

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-