Postgres returns records in the wrong order

Question

I have pretty big table posts. It contains about 100 million records.

When I run a simple query:

select uid from messages order by uid asc limit 1000

The result is very strange. Entries are fine from the start, but then they are not always ordered by the uid column.

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

Analyzed here

 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

The table is always under high load (insert, update, delete) and almost constantly autovacuuming. May have a problem?

UPD Added table definition. Sorry, I can’t add a full table definition, but all the impotent fields and their types are here.

# \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

, . , , . , .

is always under high load, . , . REINDEX:

, CREATE INDEX CONCURRENTLY.

CREATE INDEX:

CONCURRENTLY

, PostgreSQL , , ; ( ) , . . .

:

ALTER TABLE news DROP CONSTRAINT messages_pkey;
CREATE UNIQUE INDEX CONCURRENTLY messages_pkey ON news (uid);
ALTER TABLE news ADD PRIMARY KEY USING INDEX messages_pkey;

- .

Answer 2

:

:

SET enable_indexscan TO off;

.

enable_indexscan on, .

REINDEX INDEX index_name;