Re: Indexes

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

On Mon, Jan 07, 2002 at 11:25:13AM -0700, Dieter Simader wrote:
> Indices are only useful for an exact search not a fuzzy search as is used
> on most of the queries looking for a name or partnumber.

That is certainly true. I think that's where the other tweak --
increasing the memory available to PG from 1 MB to 50 MB seems to help
the most.  The figures I tossed around in the first email were for
ar/ap reports; the figures I tossed around in the second email were
for parts. The actual time savings weren't very much, but to human
psychology it was different, it was smoother, it was closer to being
more responsive like a desktop GUI app. Can I prove it? Probably not.
I just know that I don't feel like throwing a boot through the monitor
due to waiting.

I'm tempted to have the queries logged onto a file for a day while the
staff does their normal stuff, and then run explains/benchmarks
against the log.

> If you run a database with 10,000+ tuples in the parts table it certainly
> pays off to change the backend from a fuzzy search to an exact search. You
> lose a lot of flexibility but the performance increase makes it all worth
> while.

Sure, and the business process requires a fuzzy search which made
those text indices a liability. It's those other operations like
getting an AR aging report or a AR transaction list sliced by dates
that saw the best improvements. I've written another report module
that summerizes "today's sales" which requires joins against the
customer/vendor tables. While those queries didn't touch the parts
table, they definately saw some speed improvements.  Probably because
there were 1000 rows in the vendor table and 22,000 rows in the
customer table. 

-Qaexl-