problem on table statistics
От | Silvio Brandani |
---|---|
Тема | problem on table statistics |
Дата | |
Msg-id | 4F0AFC85.5050908@tech.sdb.it обсуждение исходный текст |
Ответы |
Re: problem on table statistics
Re: problem on table statistics |
Список | 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 quandoli 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 eventualiallegati, 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. --
В списке pgsql-admin по дате отправления: