英文:
PostgreSQL - Optimizing query with multiple table selects
问题
以下是翻译好的部分:
什么是基于外键关系编写以下多选查询的最优化方法?
选择
a.id AS 帐户,
(SELECT COUNT()FROM b WHERE b.a_id = a.id)AS b_count,
(SELECT COUNT()FROM c WHERE c.a_id = a.id)AS c_count,
(SELECT COUNT(*)FROM d WHERE d.a_id = a.id)AS d_count
从
a
查询结果应如下所示;
帐户| b_count | c_count | d_count |
1 | 3325 | 5004 | 8002 |
2 | 52353 | 3009 | 1202 |
为了透明,查询的行数量大约是;
表a:1,200行
表b:500,000行
表c:600,000行
表d:300,000行
英文:
What would be the most optimized way to write the following multi select query based on a foreign key relationship?
SELECT
a.id AS account,
(SELECT COUNT(*) FROM b WHERE b.a_id = a.id) AS b_count,
(SELECT COUNT(*) FROM c WHERE c.a_id = a.id) AS c_count,
(SELECT COUNT(*) FROM d WHERE d.a_id = a.id) AS d_count
FROM
a
The query results should look like;
account| b_count | c_count | d_count |
---------------------------------------
1 | 3325 | 5004 | 8002 |
---------------------------------------
2 | 52353 | 3009 | 1202 |
To be transparent the row volume for query will be roughly;
table a: 1,200 rows
table b: 500,000 rows
table c: 600,000 rows
table d: 300,000 rows
答案1
得分: 3
请看这个。我猜性能会更好。
也许优化器本身会对您的请求进行这样的转换。
如果对于表b、c、d有索引(a_id)-会更好。
select a.id
,coalesce(b_count,0)b_count
,coalesce(c_count,0)c_count
,coalesce(d_count,0)d_count
from a
left join( select a_id,count(*) b_count from b group by b.a_id)b on b.a_id=a.id
left join( select a_id,count(*) c_count from c group by c.a_id)c on c.a_id=a.id
left join( select a_id,count(*) d_count from d group by d.a_id)d on d.a_id=a.id
如果您展示不同查询性能的比较将会很有趣。您有足够多的数据来进行测试。
英文:
Try this. I guess the performance will be better.
Perhaps the optimizer itself will make such a transformation of your request.
If you have index (a_id) for tables b,c,d - will be better.
select a.id
,coalesce(b_count,0)b_count
,coalesce(c_count,0)c_count
,coalesce(d_count,0)d_count
from a
left join( select a_id,count(*) b_count from b group by b.a_id)b on b.a_id=a.id
left join( select a_id,count(*) c_count from c group by c.a_id)c on c.a_id=a.id
left join( select a_id,count(*) d_count from d group by d.a_id)d on d.a_id=a.id
It will be interesting if you show a comparison of the performance of different queries. You have a large enough amount of data to test.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论