Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 393ACAF82E7 for ; Mon, 9 Jan 2012 11:09:18 -0400 (AST) Received: from mail2.sdb.it ([85.37.147.67]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RkGqV-0008DE-Jp for pgsql-admin@postgresql.org; Mon, 09 Jan 2012 15:09:17 +0000 Received: by mail2.sdb.it (Postfix, from userid 500) id 3966C8040; Mon, 9 Jan 2012 16:08:58 +0100 (CET) Received: from geronimo.sdb.it (geronimo.sdb.it [10.101.1.20]) by mail2.sdb.it (Postfix) with ESMTP id EBA25803F; Mon, 9 Jan 2012 16:08:57 +0100 (CET) Received: from mailtech.sdb.it (localhost [127.0.0.1]) by geronimo.sdb.it (Postfix) with ESMTP id 6F77338057D; Mon, 9 Jan 2012 16:08:47 +0100 (CET) Received: from [10.118.101.88] (silviob.sdb.it [10.118.101.88]) by mailtech.sdb.it (Postfix) with ESMTP id A23C160063; Mon, 9 Jan 2012 16:08:57 +0100 (CET) Message-ID: <4F0B0309.6020709@tech.sdb.it> Date: Mon, 09 Jan 2012 16:08:57 +0100 From: Silvio Brandani User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; it; rv:1.9.2.25) Gecko/20111213 Thunderbird/3.1.17 MIME-Version: 1.0 To: Szymon Guz Cc: pgsql-admin@postgresql.org Subject: Re: problem on table statistics References: <4F0AFC85.5050908@tech.sdb.it> In-Reply-To: Content-Type: multipart/alternative; boundary="------------000906030600090109010504" X-Private-Material: Please read notice X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201201/41 X-Sequence-Number: 38953 This is a multi-part message in MIME format. --------------000906030600090109010504 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit There was a table set statistics changing the default value, now I remove and run analyze the stats are update correctly but the problem of different plan still stand. I set enable_nestloop off and the query plan is the following, the query is fast now: Hash IN Join (cost=56574.39..118874.17 rows=117295 width=620) Hash Cond: (t1.number = t2.number) -> Index Scan using mov_con_x10 on mov_con t1 (cost=0.00..52469.07 rows=351884 width=620) -> Hash (cost=55108.20..55108.20 rows=117295 width=11) -> Index Scan using mov_con_x10 on mov_con t2 (cost=0.00..55108.20 rows=117295 width=11) Filter: (abs((amount - total_amo)) > 0.1::double precision) Il 09/01/2012 15.59, Szymon Guz ha scritto: > > > On 9 January 2012 15:41, Silvio Brandani > wrote: > > > In the last few hours we get a problem with following query in > Production database : > > select * from "001".mov_con where number in ( select number from > "001".mov_con where abs(amount-total_amo)>0.1) ; > > The correct plan should be > > QUERY PLAN > ------------------------------ ------------------------------ > ------------------------------ ----------- > Nested Loop (cost=541763.01..584606.03 rows=1249640 > width=360) > -> HashAggregate (cost=541763.01..541807.55 rows=4454 width=10) > -> Index Scan using mov_con_x9 on mov_con t2 > (cost=0.00..538639.38 rows=1249452 width=10) > Filter: (abs((amount - total_amo)) > 0.1::double > precision) > -> Index Scan using mov_con_pkey on mov_con t1 > (cost=0.00..6.10 rows=281 width=360) > Index Cond: (t1.number = t2.number) > (6 rows) > > > instead we get the following WRONG one: > > QUERY PLAN > ------------------------------ ------------------------------ > ------------------------------ ----- > Nested Loop IN Join (cost=0.00..52906.16 rows=117499 width=620) > -> Index Scan using mov_con_x10 on mov_con t1 > (cost=0.00..52483.90 rows=352486 width=620) > -> Index Scan using mov_con_x10 on mov_con t2 (cost=0.00..0.72 > rows=3 width=11) > Index Cond: (t2.number = t1.number) > Filter: (abs((t2.amount - t2.total_amo)) > 0.1::double > precision) > > So I go to see statistics and try to change the > default_statistics_target from 10 to 100 , reload the > configuration and vacuum the table and the result is that while > the other tables have now 100 values on pg_stats the mov_con > table still have the same values > > SELECT * FROM pg_stats > WHERE tablename='mov_con' AND attname='number' ; > > So there is something wrong with table statistics. How can I reset > the pg_statistics for this table??? > > Any comment higly appreciated. > > > > > > > Hi, > did you make only vacuum, or vacuum analyze? Simple vacuum does not > change stats, analyze does (or vacuum analyze). > > > regards > Szymon -- Silvio Brandani Infrastructure Administrator SDB Information Technology Phone: +39.055.3811222 Fax: +39.055.5201119 --- Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalitą amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/privacy.html Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.html L'Azienda non si assume alcuna responsabilitą giuridica qualora pervengano da questo indirizzo messaggi estranei all'attivitą lavorativa o contrari a norme. -- --------------000906030600090109010504 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

There was a table set statistics=C2=A0 changing the default value, no= w I remove and run analyze the stats are update correctly but the problem of different plan still stand.
I set enable_nestloop off and the query plan is the following, the query is fast now:

=C2=A0Hash IN Join=C2=A0 (cost=3D56574.39..118874.17 rows=3D117295 wi= dth=3D620)
=C2=A0=C2=A0 Hash Cond: (t1.number =3D t2.number)
=C2=A0=C2=A0 ->=C2=A0 Index Scan using mov_con_x10 on mov_con t1=C2= =A0 (cost=3D0.00..52469.07 rows=3D351884 width=3D620)
=C2=A0=C2=A0 ->=C2=A0 Hash=C2=A0 (cost=3D55108.20..55108.20 rows=3D= 117295 width=3D11)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Index Sc= an using mov_con_x10 on mov_con t2=C2=A0 (cost=3D0.00..55108.20 rows=3D117295 width=3D11)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 Filter: (abs((amount - total_amo)) > 0.1::double precision)







Il 09/01/2012 15.59, Szymon Guz ha scritto:


On 9 January 2012 15:41, Silvio Brandani <silvio.brandani@t= ech.sdb.it> wrote:

In the last few hours we get a problem with following =C2=A0que= ry in Production database =C2=A0:

select * from "001".mov_con =C2=A0where number in ( select numb= er from "001".mov_con where abs(amount-total_amo)>0.1) ;

The correct plan should be

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 QUERY PLAN
------------------------------ ------------------------------ ------------------------------ -----------
=C2=A0Nested Loop =C2=A0(cost=3D541763.01..584606.03 rows=3D1249640 width=3D360)
=C2=A0 -> =C2=A0HashAggregate =C2=A0(cost=3D541763.01..54180= 7.55 rows=3D4454 width=3D10)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Index Scan using mov_co= n_x9 on mov_con t2 =C2=A0(cost=3D0.00..538639.38 rows=3D1249452 width=3D10)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: (abs((= amount - total_amo)) > 0.1::double precision)
=C2=A0 -> =C2=A0Index Scan using mov_con_pkey on mov_con t1 =C2=A0(cost=3D0.00..6.10 rows=3D281 width=3D360)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: (t1.number =3D t2.numbe= r)
(6 rows)


instead we get the following WRONG one:

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0QUERY PLAN
------------------------------ ------------------------------ ------------------------------ -----
=C2=A0Nested Loop IN Join =C2=A0(cost=3D0.00..52906.16 rows=3D1= 17499 width=3D620)
=C2=A0 -> =C2=A0Index Scan using mov_con_x10 on mov_con t1 =C2=A0(cost=3D0.00..52483.90 rows=3D352486 width=3D620)
=C2=A0 -> =C2=A0Index Scan using mov_con_x10 on mov_con t2 =C2=A0(cost=3D0.00..0.72 rows=3D3 width=3D11)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: (t2.number =3D t1.numbe= r)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 Filter: (abs((t2.amount - t2.total_= amo)) > 0.1::double precision)

So I go to see statistics and try to change the default_statistics_target from 10 to 100 , reload the configuration and vacuum the table and the result is that while
the other tables have now 100 values on =C2=A0pg_stats the mov_= con table still have the same values

=C2=A0SELECT * FROM pg_stats
WHERE tablename=3D'mov_con' AND attname=3D'number' ;

So there is something wrong with table statistics. How can I reset the pg_statistics for this table???

Any comment higly appreciated.






Hi,
did you make only vacuum, or vacuum analyze? Simple vacuum does not change stats, analyze does (or vacuum analyze).


regards
Szymon


--=20
Silvio Brandani
Infrastructure Administrator
SDB Information Technology
Phone: +39.055.3811222
Fax:   +39.055.5201119
=0D
=0D =0D =0D =0D disclaimer-sdb=0D =0D =0D
=0D =0D Utilizziamo i dati personali che la riguardano esclusivamente per nostre = finalità amministrative e contabili, anche quando li comunichiamo a= terzi. Informazioni dettagliate, anche in ordine al Suo diritto di acces= so e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/privac= y.html=0D
=0D Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo = al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 6= 16 codice penale http://www.savinodelbene.com/privacy/codice_penale_616.ht= ml=0D
=0D L'Azienda non si assume alcuna responsabilità giuridica qualora per= vengano da questo indirizzo messaggi estranei all'attività lavorati= va o contrari a norme.=0D =0D
=0D =0D =0D =0D

=0D =0D =0D
=0D --------------000906030600090109010504--