sql - 过滤视图时如何利用基础索引?
问题描述
我有一个非常简单的查询,执行速度非常慢,原因是它在执行 JOIN后对视图进行扫描。看到这一点我有点惊讶,因为我希望 Postgres在加入之前进行过滤,看到其中一个基础表在要过滤的列上有一个 INDEX。
有没有办法我可以以某种方式重新排序查询或提示计划者如何以不同的方式进行查询?
请注意,我确实知道如何通过直接访问基础表来解决这个问题,但是视图隐藏了一些复杂性,最好避开查询。
查询
select * from form where encounter_id= 23728 and type = 'vitals';
解释分析
Subquery Scan on form (cost=0.57..3439.07 rows=1 width=622) (actual time=8.187..8.187 rows=0 loops=1)
Filter: ((form.encounter_id = 23728) AND (form.type = 'vitals'::text))
Rows Removed by Filter: 12000
-> Unique (cost=0.57..3259.07 rows=12000 width=626) (actual time=0.008..7.612 rows=12000 loops=1)
-> Merge Join (cost=0.57..3229.07 rows=12000 width=626) (actual time=0.007..5.485 rows=12000 loops=1)
Merge Cond: (fd.form_id = f.id)
-> Index Scan using _idx_form_details on _form_details fd (cost=0.29..2636.78 rows=12000 width=603) (actual time=0.003..1.918 rows=12000 loops=1)
-> Index Scan using pk_form on _form f (cost=0.29..412.29 rows=12000 width=27) (actual time=0.002..1.214 rows=12000 loops=1)
Planning time: 0.170 ms
Execution time: 8.212 ms
TABLE 和 VIEW 定义
CREATE TABLE _form (
id INT NOT NULL,
encounter_id INT REFERENCES _encounter (id) NOT NULL,
type TEXT NOT NULL,
CONSTRAINT pk_form PRIMARY KEY (id),
FOREIGN KEY (cid) REFERENCES _user_in_role (id)
);
CREATE INDEX encounter_id ON _form (encounter_id, type);
CREATE TABLE _form_details (
id INT NOT NULL,
form_id INT REFERENCES _form (id) NOT NULL,
archived BOOLEAN NOT NULL DEFAULT FALSE,
CONSTRAINT pk_form_details PRIMARY KEY (id),
FOREIGN KEY (cid) REFERENCES _user_in_role (id)
);
CREATE VIEW form AS
SELECT DISTINCT ON (f.id)
f.id,
f.encounter_id,
f.type,
fd.archived,
f.cid
FROM _form f
JOIN _form_details fd
ON (f.id = fd.form_id)
ORDER BY f.id, fd.id DESC;
编辑:
有人发布了一个答案(随后被删除),其中包含一个重要的信息:即使encounter_id
基础表中的列被索引,ORDER BY
视图中的操作也违背了它的目的。不幸的是,我们无法摆脱自己,ORDER BY
因为它是 DISTINCT ON
工作所必需的。
解决方案
- 这
DISTINCT ON ... ORDER BY
是一个性能杀手(子查询不能被分解) form_id INT REFERENCES _form (id)
FK缺少索引- 一个
NOT EXISTS()
反连接,或者row_number()
可以用来避免DISTINCT
子查询
SET search_path=tmp;
/***/
\i tmp.sql
CREATE TABLE tform (
id INT NOT NULL
, encounter_id INT NOT NULL -- REFERENCES tencounter (id)
, ztype TEXT NOT NULL
, CONSTRAINT pk_form PRIMARY KEY (id)
-- FOREIGN KEY (cid) REFERENCES _user_in_role (id)
);
CREATE TABLE tform_details (
id INT NOT NULL
, form_id INT REFERENCES tform (id) NOT NULL
, archived BOOLEAN NOT NULL DEFAULT FALSE
, CONSTRAINT pk_form_details PRIMARY KEY (id)
-- , FOREIGN KEY (cid) REFERENCES _user_in_role (id)
);
-- ALTER TABLE tform ADD FOREIGN KEY(encounter_id) REFERENCES tencounter (id) ;
CREATE INDEX encounter_id ON tform (encounter_id, ztype);
INSERT INTO tform (id, encounter_id, ztype)
SELECT gs, 23720+gs%29, 'ztype_' || gs::text
FROM generate_series(1,10000) gs
;
INSERT INTO tform_details (id, form_id, archived)
SELECT 10000*gs+tf.id, tf.id, (random() > 0.3) ::boolean
FROM tform tf
CROSS JOIN generate_series(0,22) gs
;
UPDATE tform
SET ztype = 'vitals'
WHERE random() < 0.2;
/***/
DROP INDEX xxxx ;
CREATE UNIQUE INDEX xxxx ON tform_details (form_id, id);
VACUUM ANALYZE tform;
VACUUM ANALYZE tform_details;
\d tform;
\d tform_details;
select COUNT(*) FROM tform;
select COUNT(*) FROM tform_details;
DROP VIEW form ;
CREATE VIEW form AS
SELECT DISTINCT ON (f.id)
f.id
, f.encounter_id
, f.ztype
, fd.archived
-- , f.cid
FROM tform f
JOIN tform_details fd ON f.id = fd.form_id
ORDER BY f.id, fd.id DESC
;
DROP VIEW form2 ;
CREATE VIEW form2 AS
SELECT f.id
, f.encounter_id
, f.ztype
, fd.archived
FROM tform f
JOIN tform_details fd
ON f.id = fd.form_id
WHERE NOT EXISTS ( SELECT *
FROM tform_details nx
WHERE nx.form_id = fd.form_id
AND nx.id > fd.id
)
;
DROP VIEW form3 ;
CREATE VIEW form3 AS
SELECT f.id
, f.encounter_id
, f.ztype
, fd.archived
FROM tform f
JOIN ( select xx.form_id, xx.archived
, row_number() OVER (PARTITION BY xx.form_id ORDER BY xx.id DESC) AS rn
FROM tform_details xx
) fd ON f.id = fd.form_id AND fd.rn = 1
;
\echo burn-in
EXPLAIN ANALYZE
select * from form where encounter_id= 23728 and ztype = 'vitals' ;
\echo plain
EXPLAIN ANALYZE
select * from form where encounter_id= 23728 and ztype = 'vitals' ;
EXPLAIN ANALYZE
select * from form2 where encounter_id= 23728 and ztype = 'vitals' ;
EXPLAIN ANALYZE
select * from form3 where encounter_id= 23728 and ztype = 'vitals' ;
\echo no_hash
SET enable_hashjoin = False;
EXPLAIN ANALYZE
select * from form where encounter_id= 23728 and ztype = 'vitals' ;
EXPLAIN ANALYZE
select * from form2 where encounter_id= 23728 and ztype = 'vitals' ;
EXPLAIN ANALYZE
select * from form3 where encounter_id= 23728 and ztype = 'vitals' ;
推荐阅读
- python - Python 请求 GET 未获取 JSON 有效负载?
- python - 使用“for”循环和“if”语句加速 Python Lemmatizer
- oracle - oracle PL/SQL 如何确定 IPv6-Address 是否在 IPv6Range 中
- msbuild - MSBuild 16.9 .NET Core 3.1 - OutDir 不搜索依赖项
- javascript - 如何在 Chart.js 中显示 1 个项目的多年数据?
- flutter - 一个用户如何查看另一个用户之前输入的数据?
- jsf - 如何将 primeface 列包含到组合中
- jquery - 如何在 ember v3.2.5 中使用 jQuery AJAX?
- unity3d - 对象从新场景 Unity 的层次结构中消失
- reactjs - 如何使用 gh-pages 在 root 中部署包含子目录的 React 项目?