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