From: | Les <nagylzs(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Slow query, possibly not using index |
Date: | 2023-08-28 07:21:01 |
Message-ID: | CAKXe9UCafrDi7o9-O3cTBZB5ZCb5Voe=Z2WGjuO=8UrWCN9aBQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
>
>>
> 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 | free_space |
free_percent
--------------+-------------+--------------+---------------+------------------+----------------+--------------------+-------------+--------------
372521984000 | 39652836 | 299148572428 | 80.3 |
3578319 | 26977942540 | 7.24 | 44638265312 | 11.98
(1 row)
=# 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)
As far as I understand these numbers, the media.block table itself is in
good shape, but the index is not. Should I vacuum the whole table? Or would
it be better to REINDEX INDEX media.idx_block_unused CONCURRENTLY ?
More important question is, how can I find out why the index was not auto
vacuumed.
Thank you,
Laszlo
From | Date | Subject | |
---|---|---|---|
Next Message | Les | 2023-08-28 10:59:40 | Re: Slow query, possibly not using index |
Previous Message | Les | 2023-08-28 06:04:28 | Re: Slow query, possibly not using index |