我自己加入一张桌子并得到以下结果:
+----+----+----+----+----+----+----+----+----+
| id | s1 | s2 | s3 | s4 | s5 | s6 | s7 | s8 |
+----+----+----+----+----+----+----+----+----+
| 1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| 2 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 7 |
| 3 | 10 | 11 | 12 | 11 | 13 | 11 | 8 | 1 |
| 4 | 4 | 2 | 3 | 1 | 10 | 11 | 17 | 13 |
| 5 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
+----+----+----+----+----+----+----+----+----+
如何过滤掉s1到s8中数字不唯一的行,比如第2、3、5行?
基本过滤器将是:
SELECT t1.id, t1.col as s1, t2.col as s2, t3.col as s3, t4.col as s4, t5.col as s5, t6.col as s6, t7.col as s7, t8.col as s8
FROM [...]
WHERE s1 NOT IN (s2, s3, s4, s5, s6, s7, s8),
AND s2 NOT IN (s3, s4, s5, s6, s7, s8),
AND s3 NOT IN (s4, s5, s6, s7, s8),
AND s4 NOT IN (s5, s6, s7, s8),
AND s5 NOT IN (s6, s7, s8),
AND s6 NOT IN (s7, s8),
AND s7 <> s8
我检查每一列是否与其他列不同,这 8 列需要 28 个条件(使用 NOT IN 简化),太多了。
我能以有效的方式做到这一点吗?
更多细节
CREATE TABLE superobject__object (
superobject_id integer NOT NULL,
path smallint NOT NULL,
set smallint NOT NULL,
object_id integer NOT NULL,
color_id integer NOT NULL,
CONSTRAINT superobject_object__pk PRIMARY KEY (superobject_id,path,object_id,color_id)
);
ALTER TABLE superobject__object ADD CONSTRAINT superobject_object__superobject_id_fk FOREIGN KEY (superobject_id) REFERENCES superobject (id);
ALTER TABLE superobject__object ADD CONSTRAINT superobject_object__object_id_fk FOREIGN KEY (object_id) REFERENCES object (id);
ALTER TABLE superobject__object ADD CONSTRAINT superobject_object__color_id_fk FOREIGN KEY (color_id) REFERENCES color (id);
它是组合数据。让它变得简单是相当复杂的。
基本上,超级对象由 1/ 可以具有依赖性 2/ 可以组合在一起作为选项集的对象组成。
想一想您可以购买绿色、黄色或红色(选项集)的汽车。如果您购买红色油漆,您可以花钱选择额外的轮毂盖。
现在,如果您搜索涂有绿色油漆和轮毂盖的汽车,您将找不到那个超级对象,因为轮毂盖有一个依赖项,即红色油漆。
我用上述表格解决了这个问题。
依赖关系(层次结构)根本不深,我可以将超级对象切割成“路径”。
对于我的汽车示例,我有:
superobject_1 path_1 object_green_paint
superobject_1 path_1 object_yellow_paint
superobject_1 path_1 object_common_object
superobject_1 path_2 object_red_paint
superobject_1 path_2 object_hubcaps
superobject_1 path_2 object_common_object
至于选项集,我给路径的每个对象一个不同的整数,并将选项与相同的整数组合在一起:
superobject_1 path_1 set_1 object_green_paint
superobject_1 path_1 set_1 object_yellow_paint
superobject_1 path_1 set_2 object_common_object
superobject_1 path_2 set_1 object_red_paint
superobject_1 path_2 set_2 object_hubcaps
superobject_1 path_2 set_3 object_common_object
为什么我这样做而不是给每个组合自己的路径?因为每增加一组选项,组合就会呈指数级增长。
然后,我搜索:
SELECT s1.superobject_id as id, s1.set, s2.set, s3.set, s4.set, s5.set, s6.set, s7.set, s8.set FROM superobject__object s1
JOIN superobject__object s2 ON (s1.superobject_id, s1.path) = (s2.superobject_id, s2.path)
JOIN superobject__object s3 ON (s1.superobject_id, s1.path) = (s3.superobject_id, s3.path)
...
JOIN superobject__object s8 ON (s1.superobject_id, s1.path) = (s8.superobject_id, s8.path)
WHERE s1.object_id IN (SELECT id FROM object WHERE <filter_1>)
AND s2.object_id IN (SELECT id FROM object WHERE <filter_2>)
AND s3.object_id IN (SELECT id FROM object WHERE <filter_3>)
...
AND s8.object_id IN (SELECT id FROM object WHERE <filter_8>)
这给了我像第一张桌子的东西。然后我继续过滤掉你有来自同一组(选项)的对象的行,因为你无法同时找到带有绿色和黄色油漆的汽车,因为一个选项排除了其他选项。
简短而简单
如果您的列都是
integer
(int4
!),NOT NULL
, 并且您可以自由安装附加模块intarray(或已经安装),则有一个非常简单的解决方案:或者,虽然您知道列数,但您自己找到了更简单的版本:
快速地
这种查询的主要挑战是许多连接会产生过多的行(在过滤之前)。尽早过滤行通常效率更高。上面的简单谓词在加入所有关系后进行过滤。很有可能,有一个更有效(尽管更冗长)的查询。你原来的方法是一个热门的竞争者。
据我所知,在您的问题更新之后,这可能会更快:
或者仍然:
与您的版本相比,最大的变化是我
object_id IN (subquery)
用连接替换了所有子句。理由是我昨天碰巧发布的内容:子句中有 then 16 个表,我们现在超过了8中
FROM
的默认值。超过该限制,Postgres 将停止尝试展平所有连接项并评估每个可能的连接顺序(因为组合的数量失控并且计划变得太昂贵)。因此,将最具选择性的过滤器移至子句的顶部变得越来越重要。有关详细信息,请阅读手册中的这一章:join_collapse_limit
from_collapse_limit
FROM
两种选择性都很难为您预测,而 Postgres 通常会根据可用的统计数据做出更好的估计。这不能完美地工作,但通常仍然比人工干预好。
或者您更清楚哪些过滤器最具选择性。然后您需要手动定义顺序或加入以获得最佳计划并节省计划时间。
我将该
FROM
子句重新排列为 8 个子查询。现在您可以使用上述两个设置来优化查询计划(和计划时间)。您需要在本地设置这些,例如:该
USING
子句主要是缩短语法。你也可以用拼写出来ON
。同样重要的是:我
s2 <> s1
从不s1 NOT IN (s2, s3, s4, s5, s6, s7, s8)
与连接顺序保持同步开始。但这可能会限制项目的顺序FROM
。因此,虽然连接的最佳顺序尚不清楚,但替代的短过滤器可能仍然更好。根据基数和过滤器中的具体内容,可能还有进一步优化的空间。但我们已经很好地离开了公共论坛中简单问题的领域,进入了付费咨询工作的领域......
有关的: