11

查 Postgres 的 index 使用情况

 2 years ago
source link: https://blog.niclin.tw/2018/06/09/%E6%9F%A5-postgres-%E7%9A%84-index-%E4%BD%BF%E7%94%A8%E6%83%85%E5%86%B5/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Nic Lin's Blog

喜歡在地上滾的工程師

一般來說，打了 index 都會想看成效，使用的情況如何，在 postgreSQL 內可以直接下這行指令

SELECT * FROM pg_stat_user_indexes;

就可以拿到完整的資料去分析每個 index 的使用情況。

SELECT * FROM pg_stat_user_indexes;

 relid | indexrelid | schemaname |  relname   |      indexrelname       | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+------------+-------------------------+----------+--------------+---------------
 16454 |      16491 | public     | categories | categories_pkey         |        0 |            0 |             0
 16463 |      16493 | public     | customers  | customers_pkey          |        0 |            0 |             0
 16470 |      16495 | public     | inventory  | inventory_pkey          |        0 |            0 |             0
 16478 |      16497 | public     | orders     | orders_pkey             |        0 |            0 |             0
 16484 |      16499 | public     | products   | products_pkey           |        0 |            0 |             0
 16458 |      16501 | public     | cust_hist  | ix_cust_hist_customerid |        0 |            0 |             0
 16463 |      16502 | public     | customers  | ix_cust_username        |        0 |            0 |             0
 16478 |      16503 | public     | orders     | ix_order_custid         |        0 |            0 |             0
 16473 |      16504 | public     | orderlines | ix_orderlines_orderid   |        0 |            0 |             0
 16484 |      16505 | public     | products   | ix_prod_category        |        0 |            0 |             0
 16484 |      16506 | public     | products   | ix_prod_special         |        0 |            0 |             0
(11 rows)

值得注意的是，有些數據為 0 並不代表真的沒用到，因為

  • unique 也是一種 index
  • primary key 也是一種 index

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK