postgresql - Postgres更新不使用主键索引

Question

我在测试新的 Postgres 应用程序时遇到问题。当我们使用主键运行更新时，它看起来是在主键而不是索引上使用序列扫描。该表太大，无法保证使用完整扫描，所以我不确定为什么选择这种方法。

pdns=# \d+ pdns.meta_data
Table "schema.table"
  Column   |          Type           | 
 id        | integer                 | 
 block_id  | integer                 | 
 feed_name | character varying(100)  | 
 metadata  | character varying(1000) | 
Indexes:
    "table_pkey" PRIMARY KEY, btree (id)

pdns=# select * from pg_stat_all_tables where schemaname not like 'pg%';
-[ RECORD 1 ]-------+------------------------------
relid               | 16403
schemaname          | schema
relname             | table
seq_scan            | 26720      <------ Times sequence scan is used
seq_tup_read        | 87233270740
idx_scan            | 1          <------ Times Index scan is used
idx_tup_fetch       | 0
n_tup_ins           | 26714
n_tup_upd           | 26714
n_tup_del           | 0
n_tup_hot_upd       | 2407
n_live_tup          | 3278722           <------ Actual rows
n_dead_tup          | 2122
n_mod_since_analyze | 2196
last_vacuum         | 2018-08-16 14:51:12.559508+00
last_autovacuum     | 2018-08-16 15:37:55.617413+00
last_analyze        | 2018-08-16 15:10:38.768696+00
last_autoanalyze    | 2018-08-16 15:38:55.205594+00
vacuum_count        | 1
autovacuum_count    | 4
analyze_count       | 2
autoanalyze_count   | 8

您可以看到它进行了大量的 seq_scans 而没有进行索引扫描(这是我手动进行的)。使用绑定(bind)变量运行查询。应用程序调用的查询是:

postgres=# SELECT   min_time, max_time,  calls,  query FROM pg_stat_statements WHERE query like'%TABLE%';
 min_time  |  max_time  | calls |                                     query
-----------+------------+-------+--------------------------------------------------------------------------------
           |            |       | RETURNING *
 604.26355 | 2447.10553 | 31591 | update SCHEMA.TABLE set block_id=$1 where id=$2
(1 rows)    
postgres=#

然而，当我手动运行它时，我得到了索引扫描:

pdns=# prepare stmt(int) as update SCHEMA.TABLE set block_id=$1 where id=$2;
PREPARE
pdns=# explain execute stmt(1,2);
QUERY PLAN | Update on table (cost=0.43..8.45 rows=1 width=102)
QUERY PLAN |   ->  Index Scan using table_pkey on table(cost=0.43..8.45 rows=1 width=102)
QUERY PLAN |         Index Cond: (id = 2)

这太奇怪了。任何想法将不胜感激。

非常感谢H

Answer 1

最后我们找到了答案，其实就在auto_explain日志中:

Query Text: update SCHEMA.TABLE set block_id=$1 where id=$2 Update on table 
(cost=0.00..110291.14 rows=17624 width=102) > Seq Scan on table (cost=0.00..110291.14 
rows=17624 width=102) Filter: ((id)::numeric = '3525375'::numeric)

关键是

((id)::numeric = '3525375'::numeric)

这表明查询正在搜索整数从数字的转换。

当列类型为整数时，应用程序实际上将数据作为数字放入。因此，插入语句正在执行隐式数据类型更改，然后导致不使用索引。通过将应用程序更改为使用整数而不是数字数据类型来解决此问题呸。希望这可以帮助其他人。感谢您的建议 Linas