Обсуждение: relpages of btree indexes are not truncating even after deleting allthe tuples from table and doing vacuum

Поиск
Список
Период
Сортировка

relpages of btree indexes are not truncating even after deleting allthe tuples from table and doing vacuum

От
Mahendra Singh
Дата:
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



Re: relpages of btree indexes are not truncating even after deletingall the tuples from table and doing vacuum

От
Peter Geoghegan
Дата:
On Mon, Dec 23, 2019 at 11:05 AM Mahendra Singh <mahi6run@gmail.com> wrote:
> From above example, we can see that after deleting all the tuples from table and firing vacuum command, size of table
isreduced but size of index relation is same as before vacuum.
 

VACUUM is only able to make existing empty pages in indexes recyclable
by future page splits within the same index. It is not possible for it
to reclaim space for the filesystem. Workload characteristics tend to
determine whether or not this limitation is truly important.

You can observe which pages are "free" in this sense (i.e. whether
they've been placed by the FSM for recycling) by using
contrib/pg_freespacemap.

-- 
Peter Geoghegan

Re: relpages of btree indexes are not truncating even after deletingall the tuples from table and doing vacuum

От
Mahendra Singh
Дата:
On Tue, 24 Dec 2019 at 02:41, Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Mon, Dec 23, 2019 at 11:05 AM Mahendra Singh <mahi6run@gmail.com> wrote:
> > From above example, we can see that after deleting all the tuples from table and firing vacuum command, size of
tableis reduced but size of index relation is same as before vacuum.
 
>
> VACUUM is only able to make existing empty pages in indexes recyclable
> by future page splits within the same index. It is not possible for it
> to reclaim space for the filesystem. Workload characteristics tend to
> determine whether or not this limitation is truly important.

Thank you for the clarification.

Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com