From: Jona <jonanews@oismail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad choice of query plan from PG 7.3.6 to PG 7.3.9
Date: Sat, 07 May 2005 14:00:19 +0200
Message-ID: <427CADD3.6050106@oismail.com> (raw)
In-Reply-To: <13858.1115387983@sss.pgh.pa.us>
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>
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jonanews@oismail.com, tgl@sss.pgh.pa.us, chriskl@familyhealth.com.au
Subject: Re: Bad choice of query plan from PG 7.3.6 to PG 7.3.9
In-Reply-To: <427CADD3.6050106@oismail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox