Re: Indexes

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

On Sun, Jan 06, 2002 at 11:26:33AM +0100, Roland Stoker wrote:
> 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!!!!!

Hey, I remember comp sci 101. I think. Maybe I should put that tidbit
into 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.

I've read through the PostgreSQL docs, and PG have three index
types.  Btree seems to be the most common. Doesn't matter though as
you said, since these are "orders". 1 million compares on a 1.1 gz
server is still going to be slow compared to 20 or less on say a 200
mhz server.

> That's some kick-ass speed difference.....

Hell yeah!

> I assumed they were in place.

Well, whether they were or not, they definately are here to stay for
me. I mean, we were using this sucker for updating inventory and
adding in barcodes. Updating a single item required the computer to
search-partnumber, click-to-edit, update, save, callback to
search-partnumber each required a hit on the server. Each on of those
operations _used_ to take about 2 or 3 seconds (13,000 part numbers).
That adds up to at least 12 to 18 seconds per part in that procedure.
Now it's taking less than a second.  So before, the computer was
taking longer than the humans...  which led to some other issues like
employees slacking off (little incentive to be more efficient, since
the computer would still hang you up) or getting frustrated. I watched
how four of the employees worked: they would click and THEN wait until
the browser finishes drawing the screen.  Or they would start clicking
on the submit buttons more often, not understanding that clicking on
submit would just reset the search time and thereby take even longer!

BTW: there's lots of little things you can help someone who has to
enter in volumes of info per day ... things as simple as using the
Javascript to move the keyboard focus on the most commonly filled
field helps tremendously. I stole  ... um, imitated the code from
google.com. We can now just take the barcode scanner and shoot each
item, boom boom boom, and the retail customer can pay us faster :-)

I'm not saying all of this about the users to sneer at or to talk
about the mythical "average dumb (frustrated) user". A business builds
on OPT -- Other People's Time -- so the owner can play golf and get
richer. (Well, at least I do. Heh.)  It's easier to build and tweak
interface systems that automate as much of the stupid, and silly
things than to teach someone how to learn and adapt and think on their
feet. The latter quickly leads to politically incorrect ideas.

The old system this business was using had it's own problems, but the
response time between keypresses and screen updates were probably less
than 0.5s ... much less than that. A trained personnel can fly through
that, knowing all the shortcuts and keystrokes, etc. One reason some
of us use UNIX and CLIs rather than GUIs. (The downside is that the
turnover rate for people here is too high -- a newbie staff member
would come in and gets overwhelmed with the old POS interface. OPT not
effectively used.). 

The owner, though, LIKES SQL-ledger and the software, since it
integrates all the data about cash coming in and cash going out (and
therefore my income) and the software works on his existing Linux
server.  Cool, eh?

If anyone asks, I'll post the sql commands to add the indexes that'll
work on a 1.6.x system and a "off the top of my head" for an 1.8.x
system ... my indices were really ad hoc (fast and sloppy) and I
hadn't taken the time to really analyze and profile each of the
queries to get the optimal mix.  For the SL developers here working on
database apps for the first time and want to find out more about how
PostgreSQL uses indexes, check out their interactive documentation at
http://www.postgresql.org or http://www3.us.postgresql.org (mirror).
Search under "index" or "indices" and look for the document with the
title "Indices". 

-Qaexl-