postgresql - Postgres 以错误的顺序返回记录

Question

我有相当大的表消息。它包含大约 1 亿条记录。

当我运行简单查询时:

select uid from messages order by uid asc limit 1000

结果很奇怪。从一开始的记录是可以的，但是它们并不总是按 uid 列排序。

   uid    
----------
 94621458 
 94637590 
 94653611 
 96545014 
 96553145 
 96581957 
 96590621 
102907437 
.....
446131576 
459475933 
424507749 
424507166 
459474125 
431059132 
440517049 
446131301 
475651666 
413687676 
.....

分析一下

 Limit  (cost=0.00..3740.51 rows=1000 width=4) (actual time=0.009..4.630 rows=1000 loops=1)
   Output: uid
   ->  Index Scan using messages_pkey on public.messages  (cost=0.00..376250123.91 rows=100587944 width=4) (actual time=0.009..4.150 rows=1000 loops=1)
         Output: uid
 Total runtime: 4.796 ms

PostgreSQL 9.1.12

该表始终处于高负载(插入、更新、删除)并且几乎不断自动清理。这可能会导致问题吗？

更新。添加了表定义。抱歉无法添加完整的表定义，但所有重要的字段及其类型都在这里。

# \d+ messages
                                                   Table "public.messages"
    Column    |            Type             |                       Modifiers                        | Storage  | Description
--------------+-----------------------------+--------------------------------------------------------+----------+-------------
 uid          | integer                     | not null default nextval('messages_uid_seq'::regclass) | plain    |
 code         | character(22)               | not null                                               | extended |
 created      | timestamp without time zone | not null                                               | plain    |
 accountid    | integer                     | not null                                               | plain    |
 status       | character varying(20)       | not null                                               | extended |
 hash         | character(3)                | not null                                               | extended |
 xxxxxxxx     | timestamp without time zone | not null                                               | plain    |
 xxxxxxxx     | integer                     |                                                        | plain    |
 xxxxxxxx     | character varying(255)      |                                                        | extended |
 xxxxxxxx     | text                        | not null                                               | extended |
 xxxxxxxx     | character varying(250)      | not null                                               | extended |
 xxxxxxxx     | text                        |                                                        | extended |
 xxxxxxxx     | text                        | not null                                               | extended |
Indexes:
    "messages_pkey" PRIMARY KEY, btree (uid)
    "messages_unique_code" UNIQUE CONSTRAINT, btree (code)
    "messages_accountid_created_idx" btree (accountid, created)
    "messages_accountid_hash_idx" btree (accountid, hash)
    "messages_accountid_status_idx" btree (accountid, status)
Has OIDs: no

Answer 1

这是一个非常笼统的答案:

尝试:

SET enable_indexscan TO off;

在同一 session 中重新运行查询。

如果结果的顺序不同于 enable_indexscan 到 on，则索引已损坏。

在这种情况下，修复它:

REINDEX INDEX index_name;