Re: Slow query, possibly not using index

[Les <nagylzs@xxxxxxxxx>]

 

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