RE: Possible DB performance increase

["Albertson, Chris" <CAlbertson@primeadvantage.com>]

When I said "slow", query speed wasn't the problem.  As you
say query speed can be helped be "tuning" a bit.  You can
build indexes to help specific queries.  As many indexes as
required.  You can even build an index on a _function_ of multiple
columns such as the difference of MagV and MagI.  Searches on
Mag[V-I] would then be very fast.  There is no end to the number
of special purpose indexes you can build.  If you have the space
to hold the indexes, go for it.

What was slow was _building_ the database from the star lists.
In this case Index file slow you down.  Every time you add more
data all of the existing indexes are updated.  The more indexes
you have the slower goes the build.  So, my software "drops"
all the indexes before importing data.  Even so, importing the
star lists was to slow by a large factor.

The problem is that every time you add a photometric observation
to the database, you have to figure out which catalog star was
observed.  If the observed star is not in the catalog then you
must add a new entry to the catalog.  It gets tricky with blends
that sometimes resolve and sometimes don't.  Also if you add 
non-matches to your catalog, over time the catalog fills with
"junk" that must be cleared out.  

The other thing my software does that IMO is unacceptable is to
completely re-build the whole database whenever new data is added.
We can't do this for the Mk IV.  I want to keep the data on-line
and accessible to the Internet while at the same time new data
are added and indexes updated.

I think the solution is to off load the catalog lookups to computers
other then the main database computer.  The lookups should be done
at the camera sites before the data are sent.  A catalog lookup is
a job that scales well when you add more computers to the job.

> -----Original Message-----
> From: Creager, Robert S [mailto:CreagRS@LOUISVILLE.STORTEK.COM]
> Sent: Monday, February 19, 2001 4:57 PM
> To: 'Tass Mailing List'
> Subject: Possible DB performance increase
> 
> 
> 
> 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
> 
> 
>