sql - 从可能为空的多个表优化 SQL 查询

Question

我目前正在对我的数据库 (Postgres 9.5) 编写查询，希望从用户的多个表中获取所有信息。例如，我有一个用户可能有多双鞋、个人记录、多个团队等。我有几个关联表来存储一对多关系。我希望查询尽可能快，因为用户可能有每个表的多个项目。这是我目前拥有的:

SELECT p.username, p.sex, p.birthdate, p.firstname, p.lastname, json_agg(json_build_object('team', t.*)) as teams, json_agg(json_build_object('shoe', s.*)) as shoes, json_agg(json_build_object('pr', pr.*)) as prs, d.devicename FROM person_tbl p
LEFT JOIN person_team_tbl tp ON tp.person_id = p.person_id
LEFT JOIN team_tbl t ON tp.team_id = t.team_id
LEFT JOIN person_shoe_tbl ps on ps.person_id = p.person_id
LEFT JOIN shoe_tbl s on ps.shoe_id = s.shoe_id
LEFT JOIN person_pr_tbl ppr on ppr.person_id = p.person_id
LEFT JOIN personalrecord_tbl pr on ppr.pr_id = pr.pr_id
LEFT JOIN person_device_tbl dp on dp.person_id = p.person_id
LEFT JOIN deviceinfo_tbl d on dp.device_id = d.device_id
GROUP BY p.username, p.sex, p.birthdate, p.firstname, p.lastname, d.devicename

这是编写查询的最有效方式吗？它返回我需要的东西，但我想确保它被有效地编写。此外，它不会返回所有用户，而只会返回一个用户(我还没有写过)。

Answer 1

您上面的是一个自然描述需要什么数据的查询；这是您通常应该争取的目标，然后由查询优化器找到运行它的最快方式。

在某些情况下，您可能会发现可以重写查询以更快地运行，但是 (a) 随着数据库软件变得更好，这种情况变得更加模糊，并且 (b) 这通常可能不是一个好主意，因为使基于成本的优化器今天运行良好的技巧可能会使其盲目地在明天针对不同的数据集运行得非常糟糕。

除非您有明确的性能问题 - 即您可以阐明性能目标但您没有达到它 - 我会谨慎行事。如果您决定进行优化，在尝试超越优化器之前，还需要考虑其他方法:

1) 是否以支持查询的方式定义了适合您的 DBMS 的索引、统计信息和其他物理模型注意事项？我不是 Postgress 方面的专家，但我认为您希望在查询中使用主键和外键的索引，并且如果优化器要求您定义统计信息，您希望在这些列上收集适当的统计信息

2) 查询是否在最佳环境中运行？期望繁重的分析查询在实时事务系统或动力不足的服务器上高效运行可能是不现实的