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

problem on table statistics

From: Silvio Brandani <silvio(dot)brandani(at)tech(dot)sdb(dot)it>
To: pgsql-admin(at)postgresql(dot)org
Subject: problem on table statistics
Date: 2012-01-09 14:41:09
Message-ID: 4F0AFC85.5050908@tech.sdb.it (view raw or flat)
Thread:
Lists: pgsql-admin
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.





-- 
Silvio Brandani


---






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.
--

Responses

pgsql-admin by date

Next:From: Szymon GuzDate: 2012-01-09 14:59:18
Subject: Re: problem on table statistics
Previous:From: Jan-Peter SeifertDate: 2012-01-06 22:06:12
Subject: Re: List archives dead?

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