Postgres，简单查询不使用索引 [英] Postgres, Simple Queries not using index
问题描述
PostgreSQL 9.5.0
PostgreSQL 9.5.0
我有一个名为message_attachments的表，它具有1931964行.
I have a table called message_attachments it has 1931964 rows.
我要在该表中搜索一个键，即message_id.
There's one key that I search for in that table, that's message_id.
我也总是包含deleted_at is NULL语句(例如软删除).
I also, always include the deleted_at is NULL statement (e.g. soft delete).
创建了一个索引:
CREATE INDEX message_attachments_message_id_idx
ON message_attachments (message_id)
WHERE deleted_at IS NULL;
因此它应直接与此查询匹配:
So it should directly match this query:
EXPLAIN ANALYZE
select *
from "message_attachments"
where "deleted_at" is null
and "message_id" = 33998052;
但是最终的查询计划如下:
But the resulting query plan looks like this:
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
我正在整个数据库中使用这样的索引，但是以某种方式似乎不喜欢该特定表上的索引.
I'm using such indices through out my database, but somehow it seems that it doesn't like it on that specific table.
关于基数，最多有5列具有相同的值.
Regarding cardinality, there's at most 5 columns with the same value.
还在该表上运行了ANALYZE和VACUUM ANALYZE.
Also a ANALYZE and VACUUM ANALYZE was run on that table.
编辑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
这现在将在该表的第二个索引上运行，它看起来像这样:(绝对不应该使用)
This would be running now on the second index on that table, which looks like that: (and should definitely NOT be used)
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)
Edit3
在热备用主机上的行为完全相同. (现在是up2date)
Exactly the same behaviour on a hot standby host. (it is up2date)
Edit4
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
推荐答案
好的，我刚刚解决了这个问题.
Okay, I just solved this.
对于某种在php中被杀死的查询，我们以某种方式挂起了LOCK，但是几天前从未退出过postgres上的进程.
We had somehow a hanging LOCK for a query that was killed in php, but never exited the process on postgres from a few days ago.
因此，对于遇到相同问题的每个人，请检查您的锁:
So, for everyone expiriencing the same issues, check you LOCKS:
SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;
而且，如果几天前以来没有任何连接打开:
And also, if there are any connections open since a few days ago:
select * from pg_stat_activity order by query_start limit 10;
这篇关于Postgres，简单查询不使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
