Re: Indexes

[Roland S. <sql...@st...>]

Ehhh guys????

Indexes makes search-queries go from O(n) to O log(n)
Read O as order
That's about the biggest performance difference you can get in a database!!!!!

Say you have a milion records in a table, the search query goes from 1 
million compares to 20 or less depending on the index-type of the db.

That's some kick-ass speed difference.....
I assumed they were in place.

Roland

On Sunday 6 January 2002 10:31, you wrote:
> 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.