PostgreSQL 条件下的松散索引搜索

Question

针对产品表中的某些过滤子条件，我想找出被过滤的产品（数百万个产品）的不同类别。

明确的方法是：

SELECT DISTINCT category_id FROM "products" WHERE _conditions_

当涉及到大量行时，完成时间很长（使用 GROUP BY 并没有真正改变太多）。

根据https://wiki.postgresql.org/wiki/Loose_indexscan，当唯一列上只有相对较少的不同值（例如此处约为30个类别），条件是：

SELECT DISTINCT category_id FROM "products"

可以重述为递归CTE

WITH RECURSIVE t AS (
   SELECT MIN(category_id) AS category_id FROM "products"
   UNION ALL
   SELECT (SELECT MIN(category_id) FROM "products" WHERE category_id > t.category_id)
   FROM t WHERE t.category_id IS NOT NULL
   )
SELECT category_id FROM t WHERE category_id IS NOT NULL
UNION ALL
SELECT NULL WHERE EXISTS(SELECT 1 FROM "products" WHERE category_id IS NULL);

在我的使用情况下，实际上表现更好（毫秒而不是秒）。但它没有条件。

如何正确地将“WHERE _conditions_”部分添加到递归CTE中？

- Yo Ludke

---

哦，这是个聪明的技巧。它也表明优化器可能会错过一些技巧，尽管可能存在微妙的语义差异。 - Craig Ringer

我不确定你的意思，但我猜测在Postgres中更好地支持松散索引扫描会非常棒。 - Yo Ludke

这里有一个关于查询规划器中松散索引扫描的功能请求：https://postgresql.uservoice.com/forums/21853-general/suggestions/8973760-loose-index-scans，您可以为其投票。 - Yo Ludke

这意味着如果没有人热衷于做这项工作或资助他人去做，它就毫无意义。这就是独立开源项目的问题所在。我甚至不知道PostgreSQL有一个“uservoice”部分，之前也没有看到过它被提及。 - Craig Ringer

它是否“开源”并不是那么重要- 如果您希望为MS SQL添加某个功能，您也无法确定该功能是否会在某个时候得到实现。 - Yo Ludke

Answer 1

WITH RECURSIVE t AS (
   SELECT MIN(category_id) AS category_id FROM "products" WHERE _conditions_
   UNION ALL
   SELECT (SELECT MIN(category_id) FROM "products" WHERE category_id > t.category_id AND _conditions_ )
   FROM t WHERE t.category_id IS NOT NULL
   )
SELECT category_id FROM t WHERE category_id IS NOT NULL
UNION ALL
SELECT NULL WHERE EXISTS(SELECT 1 FROM "products" WHERE category_id IS NULL AND _conditions_);

像这样。有三个条件。

- znmean

---

这适用于我的数据以使用索引，尽管现在在低百毫秒范围内（考虑到表大小和条件，我想这还可以接受）。 - Yo Ludke