Re: problem on table statistics

On 9 January 2012 15:41, Silvio Brandani <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 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