优化Postgres中的UUID查找

下面的所有uuid列都使用本机Postgres uuid列类型 .

有一个查找表，其中uuid（uuid类型4 - 以及可行的随机）是主键 . 定期从此查找表中提取行序列，例如10,000 .

然后，希望使用从查找表中检索到的那组uuid来查询其他表，通常是另外两个表，使用刚刚检索到的UUID . 其他表（表A和表B）中的UUID不是主键 . 其他表A和B中的UUID列具有UNIQUE约束（btree索引） .

目前没有使用任何类型的JOIN进行此合并，只是简单：

• 查询查找表，获取uuids .

• 使用（1）中的uuids查询表A.

• 使用（1）中的uuids查询表B.

问题是查询（2）和（3）的速度非常慢 . 因此，对于表A和表B中的大约4000行，特别是表A，通常约为30-50秒 . 表A有大约60M行 .

在使用EXPLAIN ANALYZE时，只处理表A，报告为在A列的uuid列上执行“索引扫描”，并在EXPLAIN ANALYZE输出中使用索引条件 .

我已经尝试了各种WHERE子句：

• uuid = ANY（'{

• uuid = ANY（VALUES（'

• uuid = 'uuid1'或uuid = 'uuid2'等....

并在uuid，btree和hash索引上使用btree（distinct），哈希索引表A进行了实验 .

到目前为止，最快（仍然相对较慢）是：btree并在WHERE子句中使用“ANY（'{” .

我读过的各种意见：

• 实际上做了正确的JOIN，例如在三个表中左外连接 .

• 使用uuid类型4是问题，它是一个随机生成的id，而不是基于序列的id .

• 可能正在试验work_mem .

无论如何 . 想知道是否有其他人有任何其他建议？

Table: "lookup"

uuid: type uuid. not null. plain storage.
datetime_stamp: type bigint. not null. plain storage.
harvest_date_stamp: type bigint. not null. plain storage.
state: type smallint. not null. plain storage.

Indexes:
    "lookup_pkey" PRIMARY KEY, btree (uuid)
    "lookup_32ff3898" btree (datetime_stamp)
    "lookup_6c8369bc" btree (harvest_date_stamp)
    "lookup_9ed39e2e" btree (state)
Has OIDs: no

Table: "article_data"`

int: type integer. not null default nextval('article_data_id_seq'::regclass). plain storage.
title: text.
text: text.
insertion_date: date
harvest_date: timestamp with time zone.
uuid: uuid.

Indexes:
    "article_data_pkey" PRIMARY KEY, btree (id)
    "article_data_uuid_key" UNIQUE CONSTRAINT, btree (uuid)
Has OIDs: no

lookup和article_data都有大约65米的行 . 两个问题：

SELECT uuid FROM lookup WHERE state = 200 LIMIT 4000;

OUTPUT FROM EXPLAIN (ANALYZE, BUFFERS):  
 Limit  (cost=0.00..4661.02 rows=4000 width=16) (actual time=0.009..1.036 rows=4000 loops=1)  
   Buffers: shared hit=42  
   ->  Seq Scan on lookup  (cost=0.00..1482857.00 rows=1272559 width=16) (actual time=0.008..0.777 rows=4000 loops=1)  
         Filter: (state = 200)  
         Rows Removed by Filter: 410  
         Buffers: shared hit=42  
 Total runtime: 1.196 ms  
(7 rows)

问题：当状态为btree时，为什么执行序列扫描而不执行索引扫描？

SELECT article_data.id, article_data.uuid, article_data.title, article_data.text 
FROM article_data 
WHERE uuid = ANY ('{f0d5e665-4f21-4337-a54b-cf0b4757db65,..... 3999 more uuid's ....}'::uuid[]);

OUTPUT FROM EXPLAIN (ANALYZE, BUFFERS):  
 Index Scan using article_data_uuid_key on article_data  (cost=5.56..34277.00 rows=4000 width=581) (actual time=0.063..66029.031 rows=400  
0 loops=1)  
   Index Cond: (uuid = ANY ('{f0d5e665-4f21-4337-a54b-cf0b4757db65,5618754f-544b-4700-9d24-c364fd0ba4e9,958e37e3-6e6e-4b2a-b854-48e88ac1fdb7,  ba56b483-59b2-4ae5-ae44-910401f3221b,aa4  
aca60-a320-4ed3-b7b4-829e6ca63592,05f1c0b9-1f9b-4e1c-8f41-07545d694e6b,7aa4dee9-be17-49df-b0ca-d6e63b0dc023,e9037826-86c4-4bbc-a9d5-6977ff7458af,db5852bf-  a447-4a1d-9673-ead2f7045589  
,6704d89  .......}'::uuid[]))  
   Buffers: shared hit=16060 read=4084 dirtied=292  
 Total runtime: 66041.443 ms  
(4 rows)

问题：为什么这么慢，即使是从磁盘读取？