Shulou(Shulou.com)05/31 Report--

This article mainly explains "analyzing the query used for index maintenance in PostgreSQL". The content of the explanation in this article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "analyze the query used for index maintenance in PostgreSQL".

Look at the table & index size

SELECT CONCAT (n.nspamee) AS table, i.relname AS index_name, pg_size_pretty (pg_relation_size (x.indrelid)) AS table_size, pg_size_pretty (pg_relation_size (x.indexrelid)) AS index_size Pg_size_pretty (pg_total_relation_size (x.indrelid)) AS total_size FROM pg_class c JOIN pg_index x ON c.oid = x.indrelidJOIN pg_class I ON i.oid = x.indexrelidLEFT JOIN pg_namespace n ON n.oid = c.relnamespaceWHERE c.relkind = ANY (ARRAY ['ritual,' t']) AND n.oid NOT IN (99, 11, 12375) [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT CONCAT (n.nspame.camera, c.relname) AS table,pg12@testdb-# i.relname AS index_name, pg_size_pretty (pg_relation_size (x.indrelid)) AS table_size,pg12@testdb-# pg_size_pretty (pg_relation_size (x.indexrelid)) AS index_size Pg12@testdb-# pg_size_pretty (pg_total_relation_size (x.indrelid)) AS total_size FROM pg_class c pg12@testdb-# JOIN pg_index x ON c.oid = x.indrelidpg12@testdb-# JOIN pg_class I ON i.oid = x.indexrelidpg12@testdb-# LEFT JOIN pg_namespace n ON n.oid = c.relnamespacepg12@testdb-# WHERE c.relkind = ANY (ARRAY ['r') 't']) pg12@testdb-# AND n.oid NOT IN (99, 11, 12375) Table | index_name | table_size | index_size | total_size-+-public.test | test_pkey | 0 bytes | 8192 bytes | | 16 kB public.t_pgbench | idx_t_pgbench_c1 | 425 MB | 214 MB | 639 MB public.tbl1 | tbl1_pkey | 5096 kB | 2208 kB | 7312 kB (3 rows) |

Index definition

SELECT pg_get_indexdef (indexrelid) AS index_queryFROM pg_index WHERE indrelid = 'test'::regclass; [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT pg_get_indexdef (indexrelid) AS index_queryFROM pg_index WHERE indrelid =' test'::regclass Index_query-CREATE UNIQUE INDEX test_pkey ON public.test USING btree (id) (1 row)

Identify unused Index

SELECT s.relname AS table_name, indexrelname AS index_name, i.indisunique, idx_scan AS index_scansFROM pg_catalog.pg_stat_user_indexes s, pg_index iWHERE i.indexrelid = s.indexrelid [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT s.relname AS table_name,pg12@testdb-# indexrelname AS index_name,pg12@testdb-# i.indisunique.pg12testdb s.indexrelid # idx_scan AS index_scanspg12@testdb-# FROM pg_catalog.pg_stat_user_indexes smae pg12antitestdblic # pg_index ipg12@testdb-# WHERE i.indexrelid = s.indexrelid Table_name | index_name | indisunique | index_scans-+-test | test_pkey | t | 0 t_pgbench | idx_t_pgbench_c1 | f | 0 tbl1 | tbl1_pkey | t | 0 (3 rows)

Retrieve duplicate indexes

SELECT indrelid::regclass table_name, att.attname column_name, amname index_methodFROM pg_index I, pg_class c, pg_opclass o, pg_am a, pg_attribute attWHERE o.oid = ALL (indclass) AND att.attnum = ANY (i.indkey) AND a.oid = o.opcmethodAND att.attrelid = c.oidAND c.oid = i.indrelidGROUP BY table_name Att.attname, indclass, amname, indkeyHAVING count (*) > 1 [local:/data/run/pg12] 5120 pg12@testdb=# CREATE UNIQUE INDEX test_pkey_dup ON public.test USING btree (id) CREATE INDEX [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT indrelid::regclass table_name,pg12@testdb-# att.attname column_name,pg12@testdb-# amname index_methodpg12@testdb-# FROM pg_index iMagna pg12 testdbmuri # pg_class c Magi pg12classteddb copyright # pg_opclass o Magna pg12classic testdblue # pg_am a Pg12@testdb-# pg_attribute attpg12@testdb-# WHERE o.oid = ALL (indclass) pg12@testdb-# AND att.attnum = ANY (i.indkey) pg12@testdb-# AND a.oid = o.opcmethodpg12@testdb-# AND att.attrelid = c.oidpg12@testdb-# AND c.oid = i.indrelidpg12@testdb-# GROUP BY table_name, pg12@testdb-# att.attname,pg12@testdb-# indclass Pg12@testdb-# amname, indkeypg12@testdb-# HAVING count (*) > 1 Table_name | column_name | index_method-+-test | id | btree (1 row) Thank you for your reading. This is the content of "analyzing queries used for Index maintenance in PostgreSQL". After studying this article I believe that you have a deeper understanding of the problem of analyzing the query used for index maintenance in PostgreSQL, and the specific usage needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.