Skip site navigation (1) Skip section navigation (2)

Re: problem on table statistics

From: Silvio Brandani <silvio(dot)brandani(at)tech(dot)sdb(dot)it>
To: Szymon Guz <mabewlun(at)gmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: problem on table statistics
Date: 2012-01-09 15:08:57
Message-ID: 4F0B0309.6020709@tech.sdb.it (view raw or flat)
Thread:
Lists: pgsql-admin

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(dot)brandani(at)tech(dot)sdb(dot)it 
> <mailto:silvio(dot)brandani(at)tech(dot)sdb(dot)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.
--

In response to

pgsql-admin by date

Next:From: Kevin GrittnerDate: 2012-01-09 15:48:00
Subject: Re: problem on table statistics
Previous:From: Szymon GuzDate: 2012-01-09 14:59:18
Subject: Re: problem on table statistics

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group