Planner estimates and VACUUM/autovacuum

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

 

I am not sure if it is bug or not but I found some strange behaviour. Maybe it is the same as described on http://www.postgresql.org/message-id/14616.1244317490@xxxxxxxxxxxxx ?). If yes – I’m sorry for the trouble, but I think that my example is more obvious.

 

Tested on PostgreSQL 9.2.4 and 9.2.6.

 

Console 1:

BEGIN;

DECLARE a CURSOR FOR SELECT * FROM tab;

--- Keep cursor open for disallow full vacuum of tab

 

Console 2:

SELECT count(*) FROM tab;    

---- Result: 3588;

select reltuples from pg_class where relname='table’; 

--- Result: 3588

UPDATE tab SET id=id;

UPDATE tab SET id=id;

UPDATE tab SET id=id;

VACUUM ANALYZE tab;

select reltuples from pg_class where relname='table’;

--- Result: 3588

 

Now wait few seconds J

 

select reltuples from pg_class where relname='table’;

--- Result: 12560

 

VACUUM ANALYZE tab;

select reltuples from pg_class where relname='table’;

--- Result: 3588

 

There is 3588 live records and 12560 live+dead records in table.

That is strange for me. VACUUM updates pg_class.reltuples differently (only live roiws count) than autovacuum (live and dead rows). Why?

 

Also in planning:

 

explain SELECT id FROM tab;

                              QUERY PLAN

----------------------------------------------------------------------

Seq Scan on tab  (cost=0.00..1074.60 rows=12560 width=4)

 

Estimation is done with the use of current pg_class.reltuples value.  This value includes dead rows count after autovacuum so estimation is bad, especially in more complex planner tree, for example:

 

Explain SELECT a.id FROM tab AS a JOIN tab AS b USING (id);

 

                                               QUERY PLAN

---------------------------------------------------------------------------------------------------------

Nested Loop  (cost=0.00..6410.70 rows=12560 width=4)

   ->  Seq Scan on tab a  (cost=0.00..1074.60 rows=12560 width=8)

   ->  Index Only Scan using tab_pkey on tab b  (cost=0.00..0.41 rows=1 width=4)

         Index Cond: (id = a.id)

 

PostgreSQL estimates 12560 records in query result. This is wrong estimation if dead tuples are removed during seq scan or index scan (I suppose that it is).

 

I don’t think that AUTOVACUUM and VACUUM ANALYZE should behave differently L

 

--------------------------------------------------------------------------

Artur Zajac

 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux