On Mon, Aug 28, 2023 at 5:33 PM jayaprabhakar k wrote:
> REINDEX requires a full table scan
>
> Roughly create a new index, rename index, drop old index.
> REINDEX is not incremental. running reindex frequently does not reduce the
> future reindex time.
You didn't say which Postgres version you'
Hi,
TL;DR:
Observations:
1. REINDEX requires a full table scan
- Roughly create a new index, rename index, drop old index.
- REINDEX is not incremental. running reindex frequently does not
reduce the future reindex time.
2. REINDEX does not use the index itself
3. VACUU
>
>
>
> > More important question is, how can I find out why the index was not
> auto vacuumed.
>
> You should have a look at pg_stat_user_tables. It'll let you know if
> the table is being autovacuumed and how often. If you're concerned
> about autovacuum not running properly, then you might wan
On Mon, 28 Aug 2023 at 19:21, Les wrote:
> More important question is, how can I find out why the index was not auto
> vacuumed.
You should have a look at pg_stat_user_tables. It'll let you know if
the table is being autovacuumed and how often. If you're concerned
about autovacuum not running
po 28. 8. 2023 v 13:00 odesílatel Les 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
>>
>> -+-
>
>
> =# 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
>
> -+++---++---
>
>>
> All right, I started pgstattuple() and I'll also do pgstatindex(), but it
> takes a while. I'll get back with the results.
>
=# select * from pgstattuple('media.block');
table_len | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | f
