Look at it like this(this is how this affected me):
I had a table that use to be the primary home for my data(6 gigs worth).  I copied out 
and copied to another table.  I purged and then I 'vacuum full''d the database.  After 
a day things really started going to hell.  SLOOOW.. like 30 minutes to run my 
software versus the 1-5 seconds it normally takes.

The old table is still used but I use it to queue up data.  After the data is 
processed, it is deleted.  Mind you that the repurposed 'queue' table usually has no 
more than 3000-10000 entries in it.  Guess what the index size was.....  all told I 
had 7 gigs of indexes.  Why?  Because vacuum doesn't reoptimize the indexes.  If 
postgresql can't use a deleted row's index entry, it creates a new one.  The docs make 
it sound that if the difference between the values of the deleted rows vs the new row 
aren't close, it can't use the old index space.  Look in the docs about reindexing to 
see their explanation.  So back to my example, my table should maybe be 100K w/ 
indexes but it was more like 7 gigs.  I re-indexed and BAM!  My times were sub-second. 

Based on the information you have below, you have 3 gigs worth of indexes.  Do you 
have that much data(in terms of rows)?

-----Original Message-----
From:   Reece Hart [mailto:[EMAIL PROTECTED]
Sent:   Wed 7/23/2003 1:07 PM
To:     Guthrie, Jeremy
Subject:        RE: [PERFORM] slow table updates
On Wed, 2003-07-23 at 10:47, Guthrie, Jeremy wrote:

> Have you checked the sizes of your indexes?  You may need to rebuild them...
> Multiply the relpages colum by 8192.

So, what does this tell me? I'm guessing that you're implying that I
should expect 8192 keys per page, and that this therefore indicates the
sparseness of the key pages. Guessing that, I did:

[EMAIL PROTECTED]> SELECT c2.relname, c2.relpages, c2.relpages*8192 as "*8192",
   43413476::real/(c2.relpages*8192) FROM pg_class c, pg_class c2, pg_index i
   where c.oid = i.indrelid AND c2.oid = i.indexrelid and c2.relname~'^p2th|^papro'
   ORDER BY c2.relname;

             relname             | relpages |   *8192    |      ?column?
 p2thread_p2params_id            |   122912 | 1006895104 | 0.0431161854174633
 p2thread_pmodel_id              |   123243 | 1009606656 | 0.0430003860830331
 paprospect2_redundant_alignment |   229934 | 1883619328 | 0.0230479032332376

What do you make of 'em apples?


Reece Hart, Ph.D.                       [EMAIL PROTECTED], http://www.gene.com/
Genentech, Inc.                         650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93                        http://www.in-machina.com/~reece/
South San Francisco, CA  94080-4990     [EMAIL PROTECTED], GPG: 0x25EC91A0

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to