X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D593114B2800 for ; Tue, 10 Aug 2004 15:56:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30228-08 for ; Tue, 10 Aug 2004 18:56:35 +0000 (GMT) Received: from snowwhite.lulu.com (mail.lulu.com [66.193.5.50]) by svr1.postgresql.org (Postfix) with ESMTP id 6CA2A14B2869 for ; Tue, 10 Aug 2004 15:56:32 -0300 (ADT) Received: from [10.0.0.32] (meatwad.rdu.lulu.com [10.0.0.32]) (authenticated bits=0) by snowwhite.lulu.com (8.12.8/8.12.8) with ESMTP id i7AIuJQR007420 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NO); Tue, 10 Aug 2004 14:56:19 -0400 Message-ID: <41191A5C.7020409@lulu.com> Date: Tue, 10 Aug 2004 14:56:28 -0400 From: Bill Montgomery User-Agent: Mozilla Thunderbird 0.7 (X11/20040615) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Paul Langard Cc: pgsql-performance@postgresql.org Subject: Re: Slow select, insert, update References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/130 X-Sequence-Number: 7766 Paul, Paul Langard wrote: > Having trouble with one table (see time to count records below!). > > Fairly new to postgres so any help much appreciated. > > It only contains 9,106 records - as you can see from: > > > select count(id) from project > > *count > *9106 > 1 row(s) > Total runtime: 45,778.813 ms > ... the database is regularly vaccuumed. Have you tried doing a VACUUM FULL, CLUSTER, or drop/restore on the table? This sounds symptomatic of a table with a bunch of dead tuples not in the FSM (free space map). Only tuples in the FSM are reclaimed by a regular VACUUM. If your FSM parameters in postgresql.conf are not big enough for your ratio of UPDATE/DELETE operations to VACUUM frequency, you will end up with dead tuples that will only be reclaimed by a VACUUM FULL. To prevent this problem in the future, look at increasing your FSM size and possibly vacuuming more frequently or using pg_autovacuum. Good Luck, Bill Montgomery