我有以下表格:
CREATE TABLE acl_sid (
id bigserial not null primary key,
principal boolean not null,
sid varchar(100) not null,
constraint unique_uk_1 unique(sid,principal) );
CREATE TABLE users(
id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('users_sequence') PRIMARY KEY,
email VARCHAR(255) NOT NULL,
-- some ~10 columns omitted
);
CREATE TABLE authorities (
user_id NUMERIC(20, 0) NOT NULL,
sid_id BIGINT NOT NULL,
CONSTRAINT fk_authorities_users FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_authorities_sids FOREIGN KEY (sid_id) REFERENCES acl_sid (id) ON UPDATE CASCADE ON DELETE CASCADE );
CREATE SEQUENCE decidables_sequence START WITH 1 NO MAXVALUE;
CREATE TABLE decidables (
id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('decidables_sequence') PRIMARY KEY,
assigned_to NUMERIC(20, 0) DEFAULT NULL NULL,
CONSTRAINT fk_decidables_assigned_to FOREIGN KEY (assigned_to) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL );
CREATE TABLE decidables_transactions (
decidable_id NUMERIC(20, 0) NOT NULL PRIMARY KEY,
transaction_date TIMESTAMP DEFAULT NULL NULL,
transaction_id VARCHAR(255) DEFAULT NULL,
-- some 100 columns here
CONSTRAINT uq_decidables_transactions_transaction_id UNIQUE (transaction_id),
CONSTRAINT fk_decidables_transactions_decidable_id FOREIGN KEY (decidable_id) REFERENCES decidables (id) ON UPDATE CASCADE ON DELETE CASCADE );
CREATE TABLE decidables_transactions_mv (
decidable_id NUMERIC(20, 0) NOT NULL,
transaction_date TIMESTAMP DEFAULT NULL NULL,
-- some 10 columns here, excerpt from decidables_transactions
assigned_to NUMERIC(20, 0) DEFAULT NULL NULL,
CONSTRAINT fk_decidables_transactions_mv_decidable_id_idx FOREIGN KEY (decidable_id) REFERENCES decidables (id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_decidables_transactions_mv_assigned_to_idx FOREIGN KEY (assigned_to) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL );
CREATE INDEX decidable_id_mv_idx ON decidables_transactions_mv (decidable_id);
-- few more indexes on omitted columns, that are not being used in the following queries
CREATE INDEX transaction_date_mv_idx ON decidables_transactions_mv (transaction_date, decidable_id);
我有这个键集分页查询,它的性能非常好:
SELECT d.*,
t.*,
u.id AS ass_to_id,
u.email AS ass_to_email,
NULL AS ass_to_authority
FROM decidables d
JOIN decidables_transactions t ON d.id = t.decidable_id
LEFT JOIN users u ON d.assigned_to = u.id AND u.deleted_at IS NULL
-- some more joins omitted for simplicity
JOIN (SELECT t.decidable_id, t.transaction_date
FROM decidables_transactions_mv t
ORDER BY t.transaction_date DESC, t.decidable_id DESC
LIMIT 10) AS decidables_table ON d.id = decidables_table.decidable_id
ORDER BY t.transaction_date DESC, t.decidable_id DESC;
但是当我实际尝试选择用户的权限时:
SELECT d.*,
t.*,
u.id AS ass_to_id,
u.email AS ass_to_email,
s.sid AS ass_to_authority
FROM decidables d
JOIN decidables_transactions t ON d.id = t.decidable_id
LEFT JOIN users u ON d.assigned_to = u.id AND u.deleted_at IS NULL
LEFT JOIN authorities a ON a.user_id = u.id
LEFT JOIN acl_sid s ON a.sid_id = s.id
-- some more joins omitted for simplicity
JOIN (SELECT t.decidable_id, t.transaction_date
FROM decidables_transactions_mv t
ORDER BY t.transaction_date DESC, t.decidable_id DESC
LIMIT 10) AS decidables_table ON d.id = decidables_table.decidable_id
ORDER BY t.transaction_date DESC, t.decidable_id DESC;
开始需要几分钟。我在 ,和中有 ~10 行users
~10 行和acl_sid
~200 万行。decidables
decidables_transactions
decidables_transactions_mv
执行计划如下:
为什么查询的执行效果非常不同并且速度显着减慢?
LIMIT 10
是关键。在快速查询中,引擎首先使用
decidables_table
LIMIT 10 计算子查询,该子查询仅返回 10 个可判定 ID。然后它将所有其他表连接到这 10 行,这显然很快。decidables
当您在慢速查询中添加其他 LEFT JOINed 表时,引擎显然没有看到这个机会,并首先将该表(及其所有数百万行)与其他表连接起来。由于这些是 LEFT JOINS,即使您只有 10 个用户,中间结果也将始终包含所有数百万个可判定行。仅在近端,引擎才会加入有限子查询并删除除 10 行之外的所有内容。如何加快缓慢的查询速度?
ANALYZE 可以向查询规划器提供一些附加信息,帮助选择更好的计划。你说较大的表有 2m 条记录,但查询规划器估计每个表有 12m 条记录,所以统计数据一定很糟糕。
但我建议您重写查询,将子查询在列表中向上移动,以向查询规划器提供从计算子查询开始的建议: