您好,登錄后才能下訂單哦!
這篇文章主要講解了“分析PostgreSQL中用于索引維護(hù)的查詢”,文中的講解內(nèi)容簡單清晰,易于學(xué)習(xí)與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學(xué)習(xí)“分析PostgreSQL中用于索引維護(hù)的查詢”吧!
查看表&索引大小
SELECT CONCAT(n.nspname,'.', c.relname) 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.indrelid JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = ANY (ARRAY['r', 't']) AND n.oid NOT IN (99, 11, 12375); [local:/data/run/pg12]:5120 pg12@testdb=# SELECT CONCAT(n.nspname,'.', 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.indrelid pg12@testdb-# JOIN pg_class i ON i.oid = x.indexrelid pg12@testdb-# LEFT JOIN pg_namespace n ON n.oid = c.relnamespace pg12@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)
索引定義
SELECT pg_get_indexdef(indexrelid) AS index_query FROM pg_index WHERE indrelid = 'test'::regclass; [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_get_indexdef(indexrelid) AS index_query FROM pg_index WHERE indrelid = 'test'::regclass; index_query --------------------------------------------------------------- CREATE UNIQUE INDEX test_pkey ON public.test USING btree (id) (1 row)
識別未使用的Index
SELECT s.relname AS table_name, indexrelname AS index_name, i.indisunique, idx_scan AS index_scans FROM pg_catalog.pg_stat_user_indexes s, pg_index i WHERE 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, pg12@testdb-# idx_scan AS index_scans pg12@testdb-# FROM pg_catalog.pg_stat_user_indexes s, pg12@testdb-# pg_index i pg12@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)
檢索重復(fù)的索引
SELECT indrelid::regclass table_name, att.attname column_name, amname index_method FROM pg_index i, pg_class c, pg_opclass o, pg_am a, pg_attribute att WHERE o.oid = ALL (indclass) AND att.attnum = ANY(i.indkey) AND a.oid = o.opcmethod AND att.attrelid = c.oid AND c.oid = i.indrelid GROUP BY table_name, att.attname, indclass, amname, indkey HAVING 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_method pg12@testdb-# FROM pg_index i, pg12@testdb-# pg_class c, pg12@testdb-# pg_opclass o, pg12@testdb-# pg_am a, pg12@testdb-# pg_attribute att pg12@testdb-# WHERE o.oid = ALL (indclass) pg12@testdb-# AND att.attnum = ANY(i.indkey) pg12@testdb-# AND a.oid = o.opcmethod pg12@testdb-# AND att.attrelid = c.oid pg12@testdb-# AND c.oid = i.indrelid pg12@testdb-# GROUP BY table_name, pg12@testdb-# att.attname, pg12@testdb-# indclass, pg12@testdb-# amname, indkey pg12@testdb-# HAVING count(*) > 1; table_name | column_name | index_method ------------+-------------+-------------- test | id | btree (1 row)
感謝各位的閱讀,以上就是“分析PostgreSQL中用于索引維護(hù)的查詢”的內(nèi)容了,經(jīng)過本文的學(xué)習(xí)后,相信大家對分析PostgreSQL中用于索引維護(hù)的查詢這一問題有了更深刻的體會,具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。