Re: slow table updates

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:rkh(at)gene(dot)COM]
Sent: Wed 7/23/2003 1:07 PM
To: Guthrie, Jeremy
Cc: pgsql-admin(at)postgresql(dot)org; pgsql-performance(at)postgresql(dot)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(at)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(at)gene(dot)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(at)in-machina(dot)com, GPG: 0x25EC91A0