po 28. 8. 2023 v 13:00 odesílatel Les <nagylzs@xxxxxxxxx> napsal:
=# select * from pgstatindex('media.idx_block_unused');
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
4 | 2 | 389677056 | 546 | 114 | 23069 | 0 | 24384 | 90.03 | 0
(1 row)After reindex:=# select * from pgstatindex('media.idx_block_unused');
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
4 | 0 | 8192 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
(1 row)explain analyze select id from media.block b where nrefs =0 limit 1QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------------------------------+
Limit (cost=0.14..0.46 rows=1 width=16) (actual time=0.010..0.011 rows=0 loops=1) |
-> Index Only Scan using idx_block_unused on block b (cost=0.14..698.91 rows=2231 width=16) (actual time=0.008..0.009 rows=0 loops=1)|
Heap Fetches: 0 |
Planning Time: 0.174 ms |
Execution Time: 0.030 ms |It is actually empty.Now I only need to figure out why autovacuum did not work on the index.
Autovacuum doesn't reindex.
Regards
Pavel
Thank youLaszlo
