PostgreSQL – 优化多表查询的查询

huangapple go评论65阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年6月9日 00:00:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/76433719.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定