PostgreSQL Index Usage Analysis

Question

Is there a tool or method to analyze Postgres and determine which missing indexes should be created and which unused indexes should be deleted? I have little experience using the profiler tool for SQLServer, but I don’t know a similar tool included with Postgres.

Answer 1

I like this to find the missing indexes:

SELECT schemaname, relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(format('%I.%I', schemaname, relname)::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_user_tables WHERE pg_relation_size(format('%I.%I', schemaname, relname)::regclass)>80000 ORDER BY too_much_seq DESC; 

This checks if there are more sequence scans, and then an index scan. If the table is small, it is ignored, because Postgres seems to prefer sequence scanning for them.

The above query detects missing indexes.

The next step will be to find missing combined indexes. I think this is not easy, but doable. Perhaps analyzing slow queries ... I heard that pg_stat_statements can help ...

Answer 2

Check the statistics. pg_stat_user_tables and pg_stat_user_indexes are those that start with.

See " Statistics Collector ".

Answer 3

On identifying missing indexes approach .... None. But there are some plans to make this easier in a future release, for example, pseudo-indices and machine-readable EXPLAIN.

Currently, you will need EXPLAIN ANALYZE poorly performing queries, and then manually determine the best route. Some log analyzers, such as pgFouine , can help identify queries.

As for the unused index, you can use something like the following to identify it:

 select * from pg_stat_all_indexes where schemaname <> 'pg_catalog'; 

This will help determine reading, scanning, retrieving tuples.

Answer 4

Another new and interesting PostgreSQL analysis tool is PgHero . He is more focused on tuning the database and does numerous analyzes and suggestions.

Answer 5

There are several script links to help you find unused indexes in the PostgreSQL wiki . The main method is to look at pg_stat_user_indexes and look for those where idx_scan , the number of times how many times this index was used to answer queries, is zero or at least very low. If the application has changed and the previously used index is probably not right now, you sometimes need to run pg_stat_reset() to return all the data to 0 and then collect new data; you can save the current values ​​for everything and calculate the delta instead to figure it out.

There are no good tools available to suggest missing indexes. One approach is to log executed queries and analyze which ones take a lot of time using a query log analysis tool such as pgFouine or pqa. See "Running Complex Queries " for more information.

Another approach is to look at pg_stat_user_tables and search for tables with lots of sequential scans against them, where seq_tup_fetch is large. When an index is used, the idx_fetch_tup counter is idx_fetch_tup . This may tell you if the table is not indexed enough to respond to queries against it.

By figuring out which columns you should index? This usually returns query log analysis data again.

Answer 6

You can use the query below to search for index usage and index size:

Link taken from this blog.

 SELECT pt.tablename AS TableName ,t.indexname AS IndexName ,pc.reltuples AS TotalRows ,pg_size_pretty(pg_relation_size(quote_ident(pt.tablename)::text)) AS TableSize ,pg_size_pretty(pg_relation_size(quote_ident(t.indexrelname)::text)) AS IndexSize ,t.idx_scan AS TotalNumberOfScan ,t.idx_tup_read AS TotalTupleRead ,t.idx_tup_fetch AS TotalTupleFetched FROM pg_tables AS pt LEFT OUTER JOIN pg_class AS pc ON pt.tablename=pc.relname LEFT OUTER JOIN ( SELECT pc.relname AS TableName ,pc2.relname AS IndexName ,psai.idx_scan ,psai.idx_tup_read ,psai.idx_tup_fetch ,psai.indexrelname FROM pg_index AS pi JOIN pg_class AS pc ON pc.oid = pi.indrelid JOIN pg_class AS pc2 ON pc2.oid = pi.indexrelid JOIN pg_stat_all_indexes AS psai ON pi.indexrelid = psai.indexrelid )AS T ON pt.tablename = T.TableName WHERE pt.schemaname='public' ORDER BY 1; 

Answer 7

PoWA seems like an interesting tool for PostgreSQL 9.4+. He collects statistics, visualizes them and offers indexes. It uses the pg_stat_statements extension.

PoWA is a PostgreSQL Workload Analyzer that collects performance statistics and provides real-time graphs and graphs that help you monitor and configure your PostgreSQL servers. It is similar to Oracle AWR or SQL Server MDW.

Answer 8

This should help: Pratical Query Analysis