Re: problem on table statistics - Mailing list 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.brandani@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

-- 
Silvio Brandani
Infrastructure Administrator
SDB Information Technology
Phone: +39.055.3811222
Fax:   +39.055.5201119