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 20B32536B9 for ; Sat, 7 May 2005 08:56:41 -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 05269-08 for ; Sat, 7 May 2005 11:56:35 +0000 (GMT) Received: from oismail.com (unknown [38.117.22.100]) by svr1.postgresql.org (Postfix) with ESMTP id 017F2535D4 for ; Sat, 7 May 2005 08:56:33 -0300 (ADT) Received: from [84.238.28.45] ([84.238.28.45]) (authenticated bits=0) by oismail.com (8.12.11/8.12.11) with ESMTP id j47BuL1C030463 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Sat, 7 May 2005 07:56:23 -0400 Message-ID: <427CADD3.6050106@oismail.com> Date: Sat, 07 May 2005 14:00:19 +0200 From: Jona User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane 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 References: <4279ECC1.4050000@oismail.com> <24106.1115308954@sss.pgh.pa.us> <427B1E0A.3000108@oismail.com> <427B2D9E.2030804@familyhealth.com.au> <427B51AB.4060501@oismail.com> <13858.1115387983@sss.pgh.pa.us> In-Reply-To: <13858.1115387983@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------050902040801090006070600" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.107 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, HTML_30_40, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200505/69 X-Sequence-Number: 12233 This is a multi-part message in MIME format. --------------050902040801090006070600 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Wouldn't the VACUUM have made them equivalent?? anyway, here's the info for relpages: Live Server: 424 Test Server: 338 Please note though that there're more rows on the live server than on the test server due to recent upload. Total Row counts are as follows: Live Server: 12597 Test Server: 11494 When the problems started the tables had identical size though. Cheers Jona Tom Lane wrote: >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 > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > --------------050902040801090006070600 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Wouldn't the VACUUM have made them equivalent??

anyway, here's the info for relpages:
Live Server: 424
Test Server: 338

Please note though that there're more rows on the live server than on the test server due to recent upload.
Total Row counts are as follows:
Live Server: 12597
Test Server: 11494

When the problems started the tables had identical size though.

Cheers
Jona

Tom Lane wrote: 
Jona <jonanews@oismail.com> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

--------------050902040801090006070600--