Possible DB performance increase

["Creager, Robert S" <CreagRS@LOUISVILLE.STORTEK.COM>]

Hey Chris,

After you saying that the database is slow and a redesign is needed, I
tackled the first problem, in case the redesign was because of speed.

I'm not sure if older versions of Postgres will support a single index on
multiple columns, but 7.03 does.  Since I'm not running your software yet, I
created a simple Tycho2 database, with ID, RA, DECL, Bt, sBt, Vt, sVt.  It
came out to around 280Mb.

With no indexes, a query with a box of 4 degrees in both RA and DEC
benchmarked 11 times took 13 minutes.  When I added single indexes on RA and
DEC, the queries actually took a little longer (note I probably screwed this
up, as on the Ultra, this does increase performance by about 20x).  I then
removed all indexes, create a single BTREE index on both RA and DECL,
vacuumed the database, and then was able to complete the benchmark with 450
sets in 13 minutes.  The machine I'm running on is an AMD K6-2 500, with
640Mb memory.

This was duplicated on an Ultra5 with 256Mb memory with similar results,
adding about a 10x speed increase with the dual column index vs two
independent column indexes, which was a 20x increase over no index.

Of course, YMMV.

I've attached a SQL command file...

Later,
Rob

 <<create_new_indexes.sql>> 

Robert Creager
Senior Software Engineer
Client Server Library
303.673.2365 V
303.661.5379 F
888.912.4458 P
StorageTek
INFORMATION made POWERFUL

create_new_indexes.sql