根据包含两列的长列表选择行

Question

假设我有下表

CREATE TABLE IF NOT EXISTS "PROPS" (
"O_TYPE_ID"  UUID NOT NULL,
"O_ID"       UUID NOT NULL,
"R_TYPE_ID"  UUID NOT NULL,
"NAME"       VARCHAR NOT NULL,
"VALUE"      VARCHAR,
CONSTRAINT PK_PROPS  PRIMARY KEY ("O_ID", "R_TYPE_ID", "NAME")
);

现在我有一个列表列表，这是我想用来查询 Postgres 的列表。意思是我有一个列表

List((O_ID.type, NAME.type))

我想列出具有这两种类型组合的所有记录，一个列表我可以使用 IN 运算符，在上述情况下如何查询列表 os 列表值？一个朴素的实现是用两个等子句和 and 运算符做一个 where，但就我而言，如果列表太大，这意味着太多的 IO，你如何处理场景以非常优化的方式处理列表列表没有太多的 IO。使用 9.4+ 邮政。

Answer 1

许多可能的方法之一：传递两个数组，一个O_ID数组(我们将其命名为o_id_arr(，一个NAME数组(我们将其命名为name_arr(; 然后并行解嵌套并连接：

SELECT p.*
FROM   (o_id_arr, name_arr) AS t("O_ID", "NAME")
JOIN   "PROPS" p USING ("O_ID", "NAME");

Postgres 9.4 引入了并行取消嵌套多个数组的功能，因此应该适合您：

• 并行取消嵌套多个阵列

或者，特别是对于大表和很长的列表，创建一个临时表，将数据插入其中，可以选择ANALYZE它，然后连接到它：

CREATE TEMP TABLE tmp ("O_ID" UUID, "NAME" VARCHAR);
INSERT INTO tmp VALUES (..., ...), (..., ...);
ANALYZE tmp;
SELECT p.*
FROM   tmp
JOIN   "PROPS" p USING ("O_ID", "NAME")

通常，您应该在"PROPS" ("O_ID", "NAME")上有一个多列索引以提高性能。但是看到"O_ID"是uuid型，("O_ID", "R_TYPE_ID", "NAME")上的奇数PK指数可能会完成这项工作。为什么是"奇怪"？两个 UUID 和一个 varchar 列似乎太臃肿了，无法进行良好的 PK ......

相关：

• 每个搜索词显示一行，如果未找到，则替换默认值
• 将数组文本传递给 PostgreSQL 函数
• 将多个值集或数组传递给函数

旁白：摆脱那些繁琐且容易出错的双引号标识符。使用合法的小写标识符，不带双引号。看：

• PostgreSQL 列名是否区分大小写？

您的不带引号的约束名称PK_PROPS保存为pk_props...