From: Silvio Brandani <silvio.brandani@tech.sdb.it>
To: Szymon Guz <mabewlun@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: problem on table statistics
Date: Mon, 09 Jan 2012 16:08:57 +0100
Message-ID: <4F0B0309.6020709@tech.sdb.it> (raw)
In-Reply-To: <CAFjNrYsporzEQHtRE_wNQbQrukHDxRKdXqQXWTTXCDcQNCGnVg@mail.gmail.com>
References: <4F0AFC85.5050908@tech.sdb.it>
<CAFjNrYsporzEQHtRE_wNQbQrukHDxRKdXqQXWTTXCDcQNCGnVg@mail.gmail.com>
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 <silvio.brandani@tech.sdb.it
> <mailto:silvio.brandani@tech.sdb.it>> 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
> <tel: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 <tel: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.
--
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-admin@postgresql.org
Cc: silvio.brandani@tech.sdb.it, mabewlun@gmail.com
Subject: Re: problem on table statistics
In-Reply-To: <4F0B0309.6020709@tech.sdb.it>
* 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