Postgresql
如何最有效地找出記錄是否有子記錄?
我正在編寫一個從
parent
表中返回一條記錄的查詢。如果它有任何孩子,我也想在這個查詢中返回。這是一對多的關係。parent: -parent_id -name child: -child_id -name -parent_id
我的第一直覺是編寫以下查詢:
select name, (select count(child_id) from child c where c.parent_id=p.parent_id) children from parent p where name like 'some name'
但我想知道是否有更有效的方法來做到這一點,因為我實際上並不關心計數,只關心它是否有孩子。任何指針?
不要忘記 Postgres 有一個布爾數據類型。以下是表達查詢的最簡潔的方式:
select parent_id, name, exists (select from child where parent_id = p.parent_id) as has_children from parent p;
https://dbfiddle.uk/?rdbms=postgres_10&fiddle=86748ba18ba8c0f31f1b77a74230f67b
方法
聚合方法
我們將其稱為聚合方法的流行方式。注意
bool_or(child_id IS NOT NULL)
也有效,但速度並不快。SELECT parent_id, count(*)>1 AS has_children FROM parent LEFT OUTER JOIN children USING (parent_id) GROUP BY parent_id;
LEFT JOIN LATERAL
有限制但你也可以試試這個,
LEFT JOIN LATERAL()
像這樣..SELECT parent_id, has_children FROM parent AS p LEFT JOIN LATERAL ( SELECT true FROM children AS c WHERE c.parent_id = p.parent_id FETCH FIRST ROW ONLY ) AS t(has_children) ON (true);
EXISTS
僅供參考,您也可以使用
CROSS JOIN LATERAL
withEXISTS
(我相信它是如何計劃的)。我們將其稱為EXISTS 方法。SELECT parent_id, has_children FROM parent AS p CROSS JOIN LATERAL ( SELECT EXISTS( SELECT FROM children AS c WHERE c.parent_id = p.parent_id ) ) AS t(has_children);
這與,
SELECT parent_id, EXISTS( SELECT FROM children AS c WHERE c.parent_id = p.parent_id ) AS has_children FROM parent AS p;
基準
樣本數據集
1000000個孩子,2500個父母。我們的模擬人生完成了。
CREATE TABLE parent ( parent_id int PRIMARY KEY ); INSERT INTO parent SELECT x FROM generate_series(1,1e4,4) AS gs(x); CREATE TABLE children ( child_id int PRIMARY KEY, parent_id int REFERENCES parent ); INSERT INTO children SELECT x, 1 + (x::int%1e4)::int/4*4 FROM generate_series(1,1e6) AS gs(x); VACUUM FULL ANALYZE children; VACUUM FULL ANALYZE parent;
結果 (pt1)
- 聚合方法:450ms,
LEFT JOIN LATERAL ( FETCH FIRST ROW ONLY )
: 850 毫秒- 存在方法:850ms
結果(添加索引並再次執行)
現在讓我們添加一個索引
CREATE INDEX ON children (parent_id); ANALYZE children;
現在時間曲線完全不同了,
- 聚合方法:450ms,
LEFT JOIN LATERAL ( FETCH FIRST ROW ONLY )
: 30 毫秒- 存在方法:30ms