我有一张带有复合索引的巨大表格(A, B, C)
。
-- psql (13.16 (Debian 13.16-0+deb11u1), server 14.12)
\d index_a_b_c
Index "public.index_a_b_c"
Column | Type | Key? |
----------+-----------------------+------+
A | character varying(44) | yes |
B | numeric(20,0) | yes |
C | numeric(20,0) | yes |
btree, for table "public.table_a_b_c"
我需要所有不同的B
。
此查询使用 运行Index Only Scan
,但会扫描所有A
匹配项。这不适用于我的情况,因为对于某些A
s 来说,有数百万行。数百万Index Only Scan
行很慢。
EXPLAIN (ANALYZE true)
SELECT DISTINCT ON ("B") "B"
FROM "table_a_b_c"
WHERE "A" = 'astring'
-- Execution time: 0.172993s
-- Unique (cost=0.83..105067.18 rows=1123 width=5) (actual time=0.037..19.468 rows=67 loops=1)
-- -> Index Only Scan using index_a_b_c on table_a_b_c (cost=0.83..104684.36 rows=153129 width=5) (actual time=0.036..19.209 rows=1702 loops=1)
-- Index Cond: (A = 'astring'::text)
-- Heap Fetches: 351
-- Planning Time: 0.091 ms
-- Execution Time: 19.499 ms
如您所见,运行超过 1.7k 行并手动过滤并返回 67 行。从 1.7k 到数百万,20ms 需要几十秒。
我还需要所有最大的C
s 来表示不同的B
s。
与1)相同。理论上,Postgres 可以知道可能的B
,而不需要检查与 匹配的整个列表A
。
EXPLAIN (ANALYZE true)
SELECT DISTINCT ON ("B") *
FROM "table_a_b_c"
WHERE "A" = 'astring'
ORDER BY "B" DESC,
"C" DESC
-- Execution time: 0.822705s
-- Unique (cost=0.83..621264.51 rows=1123 width=247) (actual time=0.957..665.927 rows=67 loops=1)
-- -> Index Scan using index_a_b_c on table_a_b_c (cost=0.83..620881.69 rows=153130 width=247) (actual time=0.955..664.408 rows=1702 loops=1)
-- Index Cond: (a = 'astring'::text)
-- Planning Time: 0.116 ms
-- Execution Time: 665.978 ms
但例如,这很快:
SELECT * WHERE A="x" AND B=1 ORDER BY C DESC
UNION
SELECT * WHERE A="x" AND B=2 ORDER BY C DESC
UNION
....
对于所有可能的B
s。这就像次数循环B
。
问题
a)从理论上讲,上的索引不应该(A, B, C)
是的超集吗?对于不同来说会非常快。(A, B)
(A, B)
B
b) 为什么对于 Postgres 来说很难找到不同的s?
c) 如果没有新索引该如何处理?
附件 A(来自您的描述):
附件 B(来自查询计划):
意思是每个不同值都有许多
B
行。对每组多行模拟索引跳过扫描
截然不同
B
:B
与最大不同C
:小提琴
现在,仅索引扫描仅获取所需的值。(没有覆盖索引的顺序扫描。)实际上,您得到的是试图通过查询强制获得的结果
UNION
(应该是UNION ALL
这样的)。应该很快。
有关的:
这项技术的细节非常复杂。请参阅:
关于多列索引中的交替排序(
ASC
/ )顺序:DESC
DISTINCT ON
每组仅几行在这种情况下,您尝试的查询是最佳的。递归 CTE 的大量开销不会带来回报。
小提琴
看: