给初学者们:
这个问题有一个有趣的解决方案。
感谢这两个问题Q1和Q2,以及敏锐的提问者和特别是天才回答者Erwin Brandstetter,我们可以使用递归CTE来实现部分索引跳过扫描。
至于你的例子,这里是SQL:
WITH RECURSIVE mid_cte AS (
(
SELECT x
FROM t
ORDER BY 1
LIMIT 1
)
UNION ALL
SELECT n.*
FROM mid_cte o
CROSS JOIN LATERAL (
SELECT next_t.x
FROM t next_t
WHERE next_t.x > o.x
ORDER BY 1
LIMIT 1
) n
)
SELECT MIN(x),MAX(x),COUNT(DISTINCT x)
FROM mid_cte
但是有一些事情我们必须注意:
这个
索引跳过扫描仿真的性能
深度依赖于索引列的基数,而表扫描只依赖于表大小。
以下是我的实验和结果:
第一部分。1k基数--2.67秒表扫描 vs 0.03秒模拟索引跳过扫描
DROP TABLE IF EXISTS t;
CREATE TABLE t AS SELECT MD5(LEFT (RANDOM()::TEXT,5)) AS x FROM GENERATE_SERIES(1,1500000);
CREATE INDEX t_x_idx ON t USING btree(x);
SELECT MIN(x),MAX(x),COUNT(DISTINCT x) FROM t;
WITH RECURSIVE mid_cte AS (
(
SELECT x
FROM t
ORDER BY 1
LIMIT 1
)
UNION ALL
SELECT n.*
FROM mid_cte o
CROSS JOIN LATERAL (
SELECT next_t.x
FROM t next_t
WHERE next_t.x > o.x
ORDER BY 1
LIMIT 1
) n
)
SELECT MIN(x),MAX(x),COUNT(DISTINCT x)
FROM mid_cte
;
第二部分。10k基数--3.44秒表扫描 vs 0.09秒模拟索引跳过扫描
......
CREATE TABLE t AS SELECT MD5(LEFT (RANDOM()::TEXT,6)) AS x FROM GENERATE_SERIES(1,1500000);
......
;
第三方。100k 基数 -- 4.27 秒表扫描 vs 0.89 秒模拟索引跳过扫描
......
CREATE TABLE t AS SELECT MD5(LEFT (RANDOM()::TEXT,7)) AS x FROM GENERATE_SERIES(1,1500000);
......
;
第四部分。776k基数(总行数的50%)-- 5.16秒表扫描 vs 10.38秒模拟索引跳过扫描
......
CREATE TABLE t AS SELECT MD5(LEFT (RANDOM()::TEXT,8)) AS x FROM GENERATE_SERIES(1,1500000);
......
;
当基数增加时,表扫描时间的增加速度比模拟索引跳过扫描要慢得多。因此,在何时使用此方法是重要的,并且必须考虑到。