Re: Slow query, possibly not using index

Поиск
Список
Период
Сортировка
От Les
Тема Re: Slow query, possibly not using index
Дата
Msg-id CAKXe9UCafrDi7o9-O3cTBZB5ZCb5Voe=Z2WGjuO=8UrWCN9aBQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Slow query, possibly not using index  (Les <nagylzs@gmail.com>)
Ответы Re: Slow query, possibly not using index  (Les <nagylzs@gmail.com>)
Re: Slow query, possibly not using index  (David Rowley <dgrowleyml@gmail.com>)
Список 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Les
Дата:
Сообщение: Re: Slow query, possibly not using index
Следующее
От: Les
Дата:
Сообщение: Re: Slow query, possibly not using index