Back to Blog
Pg commander index5/4/2023 ![]() Also it is possible to reset stats periodically and recheck indexes usage. Perhaps there are indexes which aren’t used often, per month, for example, when doing analytics reports. Also, I’d recommend before index deletion to make sure that the stats from pg_stat_user_indexes are collected over a prolonged period. Thus, indexes with zero idx_scan are the main candidates for removal. ![]() pg_statio_user_indexes contains details on IO related to the indexes – number of pages read from shared buffers or from disk – this information might be useful in cases when several tablespaces are used with different performance characteristics and you should decide what indexes to place and where they should be placed. The pg_stat_user_indexes contains idx_scan field – information on number of times particular index has been used. These views used for estimation of indexes usage. The main tool used here is the pg_stat_user_indexes and accessory pg_statio_user_indexes. One of the many duties of a DBA is timely detection of unused indexes and their removal. Don’t build indexes for every columns or “popular” columns – look at real queries, explain them and if they really require an index, build it. ![]() It’s important to understand that these aspects might significantly influence performance, so general recommendation is to avoid unused indexes. Every index should be maintained, old version of entries must be cleaned hence, VACUUM operations take longer.Īll these reasons related to tables with high number of data modification operations, like INSERT/UPDATE/DELETE and read-intensive tables are less susceptible to those side-effects. This creates additional work for VACUUM, of course. For some indexes, GIN for example, update operations are very expensive.ĭue to these reasons, data modification transactions on tables with indexes become slower, and then the more indexes table has, the slower its operations. Another, less obvious disadvantage is that indexes existence implies additional write operations when data are updated within tables – all index entries that point to the affected table’s rows must be updated too.Obviously, every index consumes disk space and for large tables indexes might be large too, especially indexes based on whole columns, however even in this case PostgreSQL offers special BRIN indexes, but unfortunately they aren’t as general purpose as btree indexes and don’t fit for majority of use cases.However, with these great benefits, there are also some disadvantages: One of the major strengths of PostgreSQL are indexes – they are flexible and cover almost all use cases – different access methods allow indexing wide range of data types. Everything you always wanted to know about Postgres stats
0 Comments
Read More
Leave a Reply. |