Hi All,
While doing testing of "parallel vacuum" patch, I found that size of index
relation is not reducing even after deleting all the tuples and firing
vacuum command. I am not sure that this is expected behavior or not. For
reference, below I am giving one example.
postgres=# create table test (a int);
CREATE TABLE
postgres=# create index indx1 on test (a);
CREATE INDEX
postgres=# insert into test (select a from generate_series(1,100000) a);
INSERT 0 100000
postgres=# analyze ;
ANALYZE
postgres=# select relpages, relname from pg_class where relname = 'indx1';
relpages | relname
----------+---------
276 | indx1
(1 row)
-- delete all the tuples from table.
postgres=# delete from test ;
DELETE 100000
-- do vacuum to test tables
postgres=# vacuum test ;
VACUUM
-- check relpages in 'indx1' and 'test'
postgres=# select relpages, relname from pg_class where relname = 'indx1';
relpages | relname
----------+---------
276 | indx1
(1 row)
-- do vacuum to all the tables and check relpages in 'indx1'
postgres=# vacuum ;
VACUUM
postgres=# select relpages, relname from pg_class where relname = 'indx1';
relpages | relname
----------+---------
276 | indx1
(1 row)
-- check relpages in 'test' table
postgres=# select relpages, relname from pg_class where relname = 'test';
relpages | relname
----------+---------
0 | test
(1 row)
>From above example, we can see that after deleting all the tuples from
table and firing vacuum command, size of table is reduced but size of index
relation is same as before vacuum.
Please let me your thoughts.
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com
