database - 如何标识需要创建哪些索引

Question

背景

我正在尝试查找可用于索引的表。程序员提示某些表查询非常慢。

我在 stackoverflow 上找到了这个查询:

SELECT   schemaname, relname, seq_scan, seq_tup_read,
           idx_scan, seq_tup_read / seq_scan AS avg
FROM    pg_stat_user_tables
WHERE   seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

我尝试在我的数据库上运行，果然，我们的程序员提示的其中一个表出现了:

 schemaname |        relname         | seq_scan | seq_tup_read | idx_scan |  avg
------------+------------------------+----------+--------------+----------+-------
 public     | widgets                |      511 |     22834898 |        3 | 44686

根据我读到的内容，如果您有一个表的 seq-scans 多于 idx-scans，这是一个很好的指标，表明该表可以使用更多索引。

问题

我的下一个补救问题是我如何知道要索引哪一列？

我查询了当前存在哪些索引:

testdb=# select * from pg_indexes where tablename='widgets';
 schemaname | tablename |   indexname   | tablespace |                            indexdef
------------+-----------+---------------+------------+----------------------------------------------------------------
 public     | widgets| widgets_pkey |            | CREATE UNIQUE INDEX widgets_pkey ON widgets USING btree (id)
(1 row)

这就是表格的一般情况:

testdb=# \d widgets
                                    Table "public.widgets"
  Column  |            Type             |                       Modifiers
----------+-----------------------------+-------------------------------------------------------
 id       | integer                     | not null default nextval('widgets_id_seq'::regclass)
 id_ts    | timestamp without time zone | default now()
 status   | character varying(32)       |
 class    | character varying(16)       |
 from     | character varying(32)       |
 to       | character varying(32)       |
 start_ts | integer                     |
 end_ts   | integer                     |
 elapsed  | integer                     |
Indexes:
    "widgets_pkey" PRIMARY KEY, btree (id)

如有任何建议，我们将不胜感激。

Answer 1

使用pg_stat_statements找出哪些查询导致顺序扫描。

您必须将其添加到 shared_preload_libraries并重新启动 PostgreSQL。然后你创建 pg_stat_statements View

CREATE EXTENSION pg_stat_statements;

然后等到收集了一些工作负载并查询 View 。您可以使用您的表名(WHERE query ILIKE '%table_name%')过滤语句并首先获取运行时间最长的语句。

这应该会向您显示导致顺序扫描的查询。