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 Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; 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: [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? Thanks, Reece -- 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
