Ibrar Ahmed: Useful Queries For PostgreSQL Index Maintenance

栏目: IT技术 · 发布时间: 3年前

内容简介:There is a wiki page that has some queries related toBefore writing the queries I want to introduce a catalog table pg_index. The table contains information about the index. This is the basic catalog table, all the index-based views use the same table.

Ibrar Ahmed: Useful Queries For PostgreSQL Index Maintenance PostgreSQL has a rich set of indexing functionality, and there are many articles explaining the syntax, usage, and value of the index. In this article, I will write basic and useful queries to see the state of database indexes. People develop databases and after some time, when there is a demand to do changes in the architecture of software, they forget to do the previous indexes’ cleanup. This approach creates a mess and sometimes slows down the database because of too many indexes. Whenever we do an update or insert, the index will be updated along with the actual table, therefore there is a need for cleanup.

There is a wiki page that has some queries related to PostgreSQL Index Maintenance .

Before writing the queries I want to introduce a catalog table pg_index. The table contains information about the index. This is the basic catalog table, all the index-based views use the same table.

1 –Sometimes you need to see how many indexes your table has. This query will show the schema-qualified table name and its index names.

db=# SELECT CONCAT(n.nspname,'.', c.relname) AS table,
    i.relname AS index_name 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 c.relname like 'pgbench_accounts';
          table          | index_name       
-------------------------+------------------------
 public.pgbench_accounts | pgbench_accounts_pkey
 public.pgbench_accounts | pgbench_accounts_index
(2 rows)

2 –As we all know, an index is a performance feature, but along with that, it is also used to ensure uniqueness. But to ensure the uniqueness we need a separate type of index called a unique index. To check whether an index is unique or not, pg_index has a column named “indisunique” to identify the uniqueness of the index.

SELECT    i.relname AS index_name,
          indisunique is_unique
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       c.relname LIKE 'pgbench_accounts';
       index_name       | is_unique 
------------------------+-----------
 pgbench_accounts_pkey  | t
 pgbench_accounts_index | f
(2 rows)

3 –There is a pretty simple way to get the size of the index of PostgreSQL. Here is a query to list the PostgreSQL with size.

SELECT pg_size_pretty(pg_relation_size('pgbench_accounts_index'));
 pg_size_pretty 
----------------
 132 MB
(1 row)

4 –Here is a list of the indexes with total table size and size of the index, which is very useful to compare your table size with its corresponding indexes.  It’s very good to know the size of your table, index, and the total size of the table.

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);
          table          | index_name       | table_size | index_size | total_size 
-------------------------+------------------------+------------+------------+------------
 public.pgbench_tellers  | pgbench_tellers_pkey   | 88 kB      | 64 kB  | 152 kB
 public.pgbench_accounts | pgbench_accounts_pkey  | 2561 MB    | 428 MB | 3122 MB
 public.pgbench_accounts | pgbench_accounts_index | 2561 MB    | 132 MB | 3122 MB
 public.pgbench_branches | pgbench_branches_pkey  | 8192 bytes | 16 kB  | 24 kB
(4 rows)

pg_relation_size: Function gives the size of relation. It is used to get the size of the table/index.

pg_total_relation_size:  This is a special function that gives the total size of the table along with its all indexes.

5 –Get the query of the index. This query will show the index creation query.

SELECT pg_get_indexdef(indexrelid) AS index_query
FROM   pg_index WHERE  indrelid = 'pgbench_accounts'::regclass;
                                     index_query
----------------------------------------------------------------------------------------
CREATE UNIQUE INDEX pgbench_accounts_pkey ON public.pgbench_accounts USING btree (aid)
CREATE INDEX pgbench_accounts_index ON public.pgbench_accounts USING btree (bid)
CREATE INDEX pgbench_accounts_index_dup ON public.pgbench_accounts USING btree (bid)
(3 rows)

6 –In case your index becomes corrupted or bloated, you need to build that index again. At the same time, you don’t want to block the operation on your table, so this REINDEX CONCURRENTLY command is your choice for that.

REINDEX INDEX CONCURRENTLY idx;
REINDEX

7 –PostgreSQL has many index methods like BTree, Hash, BRIN, GIST, and GIN. Sometimes we want to create some specific index on a column but are unable to do that. PostgreSQL has limitations that some indexes cannot be created on some data types and operators, and that makes sense too. For example, the Hash index can only be used for equal operators. Here is a query to get the list of the supported data types for a particular index.

SELECT amname,
       opfname
FROM   pg_opfamily,
       pg_am
WHERE  opfmethod = pg_am.oid
AND    amname = 'btree';
 
amname | opfname
--------+--------------------
btree  | array_ops
btree  | bit_ops
btree  | bool_ops
…

8 –This query will find the unused indexes. If index_scans is 0 or close to 0 then you can drop those indexes. But be careful, as maybe those indexes are for unique purposes.

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;
table_name       | index_name            | indisunique | index_scans
------------------+-----------------------+-------------+-------------
pgbench_branches | pgbench_branches_pkey | t           | 0
pgbench_tellers  | pgbench_tellers_pkey  | t           | 0
pgbench_accounts | pgbench_accounts_pkey | t           | 0
(3 rows)

9 –Query used to find a duplicate index. In this example, pgbench_accounts has two of the same indexes. There is no need to have multiple same indexes with a different name on a table. As we already discussed, in case of update/insert, all the indexes get updated along with the actual table, which hurts the performance.

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;
table_name | column_name | index_method
------------+-------------+--------------
foo        | a           | btree
 
(1 row)

Conclusion

PostgreSQL has catalog tables to store the index information, and therefore, we can write as many queries as we need. This blog shows some basic queries and shows how to use the catalog tables to write the queries.


以上所述就是小编给大家介绍的《Ibrar Ahmed: Useful Queries For PostgreSQL Index Maintenance》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

REST实战

REST实战

Jim Webber、Savas Parastatidis、Ian Robinson / 李锟、俞黎敏、马钧、崔毅 / 东南大学出版社 / 2011-10 / 78.00元

为何典型的企业项目无法像你为web所开发的项目那样运行得如此平滑?对于建造分布式和企业级的应用来说,rest架构风格真的提供了一个可行的替代选择吗? 在这本富有洞察力的书中,三位soa专家对于rest进行了讲求实际的解释,并且通过将web的指导原理应用到普通的企业计算问题中,向你展示了如何开发简单的、优雅的分布式超媒体系统。你将会学习到很多技术,并且随着一家典型的公司从最初的小企业逐渐成长为......一起来看看 《REST实战》 这本书的介绍吧!

HTML 编码/解码
HTML 编码/解码

HTML 编码/解码

正则表达式在线测试
正则表达式在线测试

正则表达式在线测试

RGB HSV 转换
RGB HSV 转换

RGB HSV 互转工具