Postgres，简单查询未使用索引。

Question

Postgres，简单查询未使用索引。

postgresqlindexing

3

PostgreSQL 9.5.0

我有一个名为message_attachments的表，它有1931964行。

在该表中，有一个关键字是我要查找的，那就是message_id。

同时，我总是包含deleted_at为NULL的语句（例如软删除）。

这里创建了一个索引：

CREATE INDEX message_attachments_message_id_idx 
   ON message_attachments (message_id) 
WHERE deleted_at IS NULL;

因此，它应该直接匹配这个查询：

EXPLAIN ANALYZE 
select * 
from "message_attachments" 
where "deleted_at" is null 
  and "message_id" = 33998052;

但是生成的查询计划如下所示：

Seq Scan on message_attachments  (cost=0.00..69239.91 rows=4 width=149) (actual time=1667.850..1667.850 rows=0 loops=1)
   Filter: ((deleted_at IS NULL) AND (message_id = 33998052))
   Rows Removed by Filter: 1931896
 Planning time: 0.114 ms
 Execution time: 1667.885 ms

我在整个数据库中都使用这样的索引，但不知道为什么在这个特定的表上无法使用。

就基数而言，最多有5列具有相同的值。

此外，该表还运行了ANALYZE和VACUUM ANALYZE。 编辑1 SET enable_seqscan to off

SET enable_seqscan to off; EXPLAIN ANALYZE select * from "message_attachments" where "deleted_at" is null and "message_id" = 33998052;
SET
                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on message_attachments  (cost=36111.83..105378.49 rows=4 width=149) (actual time=2343.361..2343.361 rows=0 loops=1)
   Recheck Cond: (deleted_at IS NULL)
   Filter: (message_id = 33998052)
   Rows Removed by Filter: 1932233
   Heap Blocks: exact=45086
   ->  Bitmap Index Scan on message_attachments_deleted_at_index  (cost=0.00..36111.82 rows=1934453 width=0) (actual time=789.836..789.836 rows=1933784 loops=1)
         Index Cond: (deleted_at IS NULL)
 Planning time: 0.098 ms
 Execution time: 2343.425 ms

现在这个操作会在表的第二个索引上运行，该索引看起来像这样：（绝对不应该使用）

CREATE INDEX message_attachments_deleted_at_index ON message_attachments USING btree (deleted_at)

编辑2

\d+ message_attachments
                                                         Table "public.message_attachments"
   Column   |            Type             |                            Modifiers                             | Storage  | Stats target | Description
------------+-----------------------------+------------------------------------------------------------------+----------+--------------+-------------
 id         | bigint                      | not null default nextval('message_attachments_id_seq'::regclass) | plain    |              |
 created_at | timestamp without time zone | not null                                                         | plain    |              |
 updated_at | timestamp without time zone | not null                                                         | plain    |              |
 deleted_at | timestamp without time zone |                                                                  | plain    |              |
 name       | character varying(255)      | not null                                                         | extended |              |
 filename   | character varying(255)      | not null                                                         | extended |              |
 content    | bytea                       |                                                                  | extended |              |
 hash       | character varying(255)      | not null                                                         | extended |              |
 mime       | character varying(255)      | not null                                                         | extended |              |
 size       | bigint                      | not null                                                         | plain    |              |
 message_id | bigint                      | not null                                                         | plain    |              |
Indexes:
    "message_attachments_pkey" PRIMARY KEY, btree (id)
    "message_attachments_deleted_at_index" btree (deleted_at)
    "message_attachments_message_id_idx" btree (message_id) WHERE deleted_at IS NULL
Foreign-key constraints:
    "message_attachments_message_id_foreign" FOREIGN KEY (message_id) REFERENCES messages(id)

编辑3

在热备份主机上出现完全相同的行为（它已经是最新状态）。

编辑4

select seq_scan,seq_tup_read,idx_scan,idx_tup_fetch,n_live_tup,pg_stat_all_tables.n_dead_tup,last_analyze,pg_stat_all_tables.analyze_count,pg_stat_all_tables.last_autoanalyze from pg_stat_all_tables where relname = 'message_attachments';
 seq_scan |  seq_tup_read  | idx_scan | idx_tup_fetch | n_live_tup | n_dead_tup |         last_analyze          | analyze_count |       last_autoanalyze
----------+----------------+----------+---------------+------------+------------+-------------------------------+---------------+-------------------------------
 18728036 | 26379554229720 |  1475541 |     808566894 |    1934435 |      28052 | 2017-04-12 09:48:34.638184+02 |            68 | 2017-02-02 18:41:05.902214+01

select * from pg_stat_all_indexes where relname = 'message_attachments';
 relid  | indexrelid | schemaname |       relname       |             indexrelname             | idx_scan | idx_tup_read | idx_tup_fetch
--------+------------+------------+---------------------+--------------------------------------+----------+--------------+---------------
 113645 |     113652 | public     | message_attachments | message_attachments_pkey             |  1475563 |    804751648 |     802770401
 113645 |     113659 | public     | message_attachments | message_attachments_deleted_at_index |        3 |      5801165 |             0
 113645 |   20954507 | public     | message_attachments | message_attachments_message_id_idx   |        0 |            0 |             0

- Thomas Rosenstein

尝试运行SET enable_seqscan to off，然后再次运行分析以检查成本。 - Vao Tsun

你使用的是哪个Postgres版本？你确定已经分析了这张表吗？Postgres估计只有4行（而表格实际上有近200万行），这似乎表明统计数据不是最新的。 - user330315

@VaoTsun在帖子中添加了完整的输出。 - Thomas Rosenstein

reindex index message_attachments_message_id_idx 会改变什么吗？（还有：您应该真的升级到最新的9.5修补程序版本，即9.5.6） - user330315

好的，我会尝试在今晚完成这个任务，重新索引不会有任何改变！是否已知有某个bug存在？！ - Thomas Rosenstein

显示剩余8条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Thomas Rosenstein · Accepted Answer

好的，我刚刚解决了这个问题。

我们之前在php中有一个被杀死但从几天前开始从未退出postgres进程的挂起查询。

因此，对于遇到相同问题的每个人，请检查你的锁定：

SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;

此外，如果有几天前打开的连接：

select * from pg_stat_activity order by query_start limit 10;