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 ACF515334D for ; Fri, 6 May 2005 10:59:51 -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 15516-01 for ; Fri, 6 May 2005 13:59:47 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9272853421 for ; Fri, 6 May 2005 10:59:47 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j46DxiqR013859; Fri, 6 May 2005 09:59:44 -0400 (EDT) To: Jona Cc: Christopher Kings-Lynne , pgsql-performance@postgresql.org Subject: Re: Bad choice of query plan from PG 7.3.6 to PG 7.3.9 In-reply-to: <427B51AB.4060501@oismail.com> References: <4279ECC1.4050000@oismail.com> <24106.1115308954@sss.pgh.pa.us> <427B1E0A.3000108@oismail.com> <427B2D9E.2030804@familyhealth.com.au> <427B51AB.4060501@oismail.com> Comments: In-reply-to Jona message dated "Fri, 06 May 2005 13:14:51 +0200" Date: Fri, 06 May 2005 09:59:43 -0400 Message-ID: <13858.1115387983@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/62 X-Sequence-Number: 12226 Jona writes: > Test Server: > comm=# VACUUM ANALYZE VERBOSE StatCon_Tbl; > INFO: --Relation public.statcon_tbl-- > INFO: Pages 338: Changed 0, Empty 0; Tup 11494: Vac 0, Keep 0, UnUsed 0. > Total CPU 0.02s/0.00u sec elapsed 1.98 sec. > INFO: --Relation pg_toast.pg_toast_179851-- > INFO: Pages 85680: Changed 0, Empty 0; Tup 343321: Vac 0, Keep 0, UnUsed 0. > Total CPU 1.75s/0.23u sec elapsed 30.36 sec. > INFO: Analyzing public.statcon_tbl > VACUUM > Live Server: > comm=# VACUUM ANALYZE VERBOSE StatCon_Tbl; > INFO: --Relation public.statcon_tbl-- > INFO: Pages 424: Changed 0, Empty 0; Tup 12291: Vac 0, Keep 0, UnUsed 6101. > Total CPU 0.00s/0.01u sec elapsed 0.01 sec. > INFO: --Relation pg_toast.pg_toast_891830-- > INFO: Pages 89234: Changed 0, Empty 0; Tup 352823: Vac 0, Keep 0, > UnUsed 5487. > Total CPU 3.21s/0.47u sec elapsed 18.03 sec. > INFO: Analyzing public.statcon_tbl > VACUUM Hm, the physical table sizes aren't very different, which suggests that the problem must lie with the indexes. Unfortunately, VACUUM in 7.3 doesn't tell you anything about indexes if it doesn't have any dead rows to clean up. Could you look at pg_class.relpages for all the indexes of this table, and see what that shows? regards, tom lane