Re: slow table updates - Mailing list pgsql-performance

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
purgedand then I 'vacuum full''d the database.  After a day things really started going to hell.  SLOOOW.. like 30
minutesto 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
therepurposed 'queue' table usually has no more than 3000-10000 entries in it.  Guess what the index size was.....  all
toldI had 7 gigs of indexes.  Why?  Because vacuum doesn't reoptimize the indexes.  If postgresql can't use a deleted
row'sindex entry, it creates a new one.  The docs make it sound that if the difference between the values of the
deletedrows vs the new row aren't close, it can't use the old index space.  Look in the docs about reindexing to see
theirexplanation.  So back to my example, my table should maybe be 100K w/ indexes but it was more like 7 gigs.  I
re-indexedand 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:rkh@gene.COM]
Sent:    Wed 7/23/2003 1:07 PM
To:    Guthrie, Jeremy
Cc:    pgsql-admin@postgresql.org; pgsql-performance@postgresql.org; SF PostgreSQL
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:


rkh@csb=> 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?

Thanks,
Reece


--
Reece Hart, Ph.D.                       rkh@gene.com, 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     reece@in-machina.com, GPG: 0x25EC91A0