使用多个连接和分组优化SQL查询（Postgres 9.3）(Optimizing SQL query with multiple joins and grouping (Postgres 9.3))

我浏览过其他一些帖子，并设法让我的查询运行得更快。然而，我已经对如何进一步优化这个查询感到迷茫。我将在一个网站上使用它，它将在页面加载时执行查询，但5.5秒太长了，等待一些应该更简单的东西。最大的桌子大约有400万排，其他大约有40万排。

表结构

比赛

id BIGINT PRIMARY KEY, region TEXT, matchType TEXT, matchVersion TEXT

球队

matchid BIGINT REFERENCES match(id), id INTEGER, PRIMARY KEY(matchid, id), winner TEXT

冠军

id INTEGER PRIMARY KEY, version TEXT, name TEXT

项目

id INTEGER PRIMARY KEY, name TEXT

参加者

PRIMARY KEY(matchid, id), id INTEGER NOT NULL, matchid BIGINT REFERENCES match(id), championid INTEGER REFERENCES champion(id), teamid INTEGER, FOREIGN KEY (matchid, teamid) REFERENCES team(matchid, id), magicDamageDealtToChampions REAL, damageDealtToChampions REAL, item0 TEXT, item1 TEXT, item2 TEXT, item3 TEXT, item4 TEXT, item5 TEXT, highestAchievedSeasonTier TEXT

询问

select champion.name, sum(case when participant.item0 = '3285' then 1::int8 else 0::int8 end) as it0, sum(case when participant.item1 = '3285' then 1::int8 else 0::int8 end) as it1, sum(case when participant.item2 = '3285' then 1::int8 else 0::int8 end) as it2, sum(case when participant.item3 = '3285' then 1::int8 else 0::int8 end) as it3, sum(case when participant.item4 = '3285' then 1::int8 else 0::int8 end) as it4, sum(case when participant.item5 = '3285' then 1::int8 else 0::int8 end) as it5 from participant left join champion on champion.id = participant.championid left join team on team.matchid = participant.matchid and team.id = participant.teamid left join match on match.id = participant.matchid where (team.winner = 'True' and matchversion = '5.14' and matchtype='RANKED_SOLO_5x5') group by champion.name;

EXPLAIN ANALYZE输出： http ： //explain.depesz.com/s/ZYX

我到目前为止所做的

我已经在match.region ， participant.championid和团队where winner = 'True'的部分索引创建了单独的索引， where winner = 'True' （因为这只是我感兴趣的部分）。请注意enable_seqscan = on因为它关闭时查询速度非常慢。基本上，我试图得到的结果是这样的：

Champion |item0 | item1 | ... | item5 champ_name | num | num1 | ... | num5 ...

由于我仍然是数据库设计方面的初学者，如果我的整个桌面设计存在缺陷，我不会感到惊讶。尽管如此，我仍然倾向于查询是绝对低效的。我玩过内连接和左连接 - 虽然没有显着差异。另外，匹配需要是bigint （或者大于integer ，因为它太小）。

I've browsed around some other posts and managed to make my queries run a bit faster. However, I've come to a loss as to how to further optimize this query. I'm going to be using it on a website where it will execute the query when the page is loaded, but 5.5 seconds is far too long to wait for something that should be a lot more simple. The largest table has around 4,000,000 rows and the other ones are around 400,000 each.

Table Structure

match

id BIGINT PRIMARY KEY, region TEXT, matchType TEXT, matchVersion TEXT

team

matchid BIGINT REFERENCES match(id), id INTEGER, PRIMARY KEY(matchid, id), winner TEXT

champion

id INTEGER PRIMARY KEY, version TEXT, name TEXT

item

id INTEGER PRIMARY KEY, name TEXT

participant

Query

Output of EXPLAIN ANALYZE: http://explain.depesz.com/s/ZYX

What I've done so far

I've created separate indexes on match.region, participant.championid, and a partial index on team where winner = 'True' (since that is only what I am interested in). Note that enable_seqscan = on since when it's off the query is extremely slow. Essentially, the result I'm trying to get is something like this:

Champion |item0 | item1 | ... | item5 champ_name | num | num1 | ... | num5 ...

Since I'm still a beginner with respect to database design, I wouldn't be surprised if there is a flaw in my overall table design. I'm still leaning towards the query being absolutely inefficient, though. I've played with both inner joins and left joins -- there is no significant difference though. Additionally, match needs to be bigint (or something larger than integer, since it's too small).

最满意答案

数据库设计

我建议：

CREATE TABLE matchversion ( matchversion_id int PRIMARY KEY , matchversion text UNIQUE NOT NULL ); CREATE TABLE matchtype ( matchtype_id int PRIMARY KEY , matchtype text UNIQUE NOT NULL ); CREATE TABLE region ( region_id int PRIMARY KEY , region text NOT NULL ); CREATE TABLE match ( match_id bigint PRIMARY KEY , region_id int REFERENCES region , matchtype_id int REFERENCES matchtype , matchversion_id int REFERENCES matchversion ); CREATE TABLE team ( match_id bigint REFERENCES match , team_id integer -- better name ! , winner boolean -- ?! , PRIMARY KEY(match_id, team_id) ); CREATE TABLE champion ( champion_id int PRIMARY KEY , version text , name text ); CREATE TABLE participant ( participant_id serial PRIMARY KEY -- use proper name ! , champion_id int NOT NULL REFERENCES champion , match_id bigint NOT NULL REFERENCES match -- this FK might be redundant , team_id int , magic_damage_dealt_to_champions real , damage_dealt_to_champions real , item0 text -- or integer ?? , item1 text , item2 text , item3 text , item4 text , item5 text , highest_achieved_season_tier text -- integer ?? , FOREIGN KEY (match_id, team_id) REFERENCES team );

为了获得更小的表和索引以及更快的访问，更规范化。为matchversion ， match和region创建查找表，并仅在match写入小整数ID。

似乎列participant.item0 item5 .. item5和highestAchievedSeasonTier可能是integer ，但被定义为text ？

列team.winner似乎是boolean ，但被定义为text 。

我也改变了列的顺序，以提高效率。细节：

计算并保存PostgreSQL中的空间

询问

基于上述修改和Postgres 9.3：

SELECT c.name, * FROM ( SELECT p.champion_id , count(p.item0 = '3285' OR NULL) AS it0 , count(p.item1 = '3285' OR NULL) AS it1 , count(p.item2 = '3285' OR NULL) AS it2 , count(p.item3 = '3285' OR NULL) AS it3 , count(p.item4 = '3285' OR NULL) AS it4 , count(p.item5 = '3285' OR NULL) AS it5 FROM matchversion mv CROSS JOIN matchtype mt JOIN match m USING (matchtype_id, matchversion_id) JOIN team t USING (match_id) JOIN participant p USING (match_id, team_id) WHERE mv.matchversion = '5.14' AND mt.matchtype = 'RANKED_SOLO_5x5' AND t.winner = 'True' -- should be boolean GROUP BY p.champion_id ) p JOIN champion c USING (champion_id); -- probably just JOIN ?

由于champion.name未定义为UNIQUE ，因此GROUP BY可能是错误的 。这也是低效的。改为使用participant.championid （如果您需要结果中的名称，请稍后加入champion ）。

所有LEFT JOIN实例都是毫无意义的，因为无论如何您都在左表上有谓词和/或使用GROUP BY的列。

AND -ed周围的圆括号不需要条件。

在Postgres 9.4或更高版本中，您可以使用新的聚合FILTER语法。细节和替代方案：

如何简化此游戏统计查询？

指数

您已经拥有的team的部分索引应该看起来像这样，以允许仅索引扫描：

CREATE INDEX on team (matchid, id) WHERE winner -- boolean

但是从我看到的情况来看，您可能只需向participant添加一个winner列表并完全放弃表格team （除非有更多内容）。

此外，该索引不会有太大的帮助，因为（从您的查询计划中得知）该表有800k行，其中一半符合要求：

rows=399999 ... Filter: (winner = 'True'::text) ... Rows Removed by Filter: 399999

当你有更多不同的比赛类型和matchversions时，这个match指数会有助于更多（稍后）：

CREATE INDEX on match (matchtype_id, matchversion_id, match_id);

尽管如此，尽管100k行满足400k，但索引仅适用于仅索引扫描。否则，顺序扫描将会更快。一个索引通常支付大约5％或更少的费用。

你的主要问题是，你显然正在运行一个测试案例，但几乎没有现实的数据分布 。有了更多的选择性谓词，索引将更容易使用。

在旁边

确保您已配置了基本的Postgres设置，如random_page_cost或work_mem等。

enable_seqscan = on不言而喻。这只是关闭调试或本地作为绝望的措施的最后手段。

Database design

I suggest:

More normalization in order to get smaller tables and indexes and faster access. Create lookup-tables for matchversion, matchtype and region and only write a small integer ID in match.

Seems like the columns participant.item0 .. item5 and highestAchievedSeasonTier could be integer, but are defined as text?

The column team.winner seems to be boolean, but is defined as text.

I also changed the order of columns to be more efficient. Details:

Calculating and saving space in PostgreSQL

Query

Building on above modifications and for Postgres 9.3:

Since champion.name is not defined UNIQUE, it's probably wrong to GROUP BY it. It's also inefficient. Use participant.championid instead (and join to champion later if you need the name in the result).

All instances of LEFT JOIN are pointless, since you have predicates on the left tables anyway and / or use the column in GROUP BY.

Parentheses around AND-ed WHERE conditions are not needed.

In Postgres 9.4 or later you could use the new aggregate FILTER syntax instead. Details and alternatives:

How can I simplify this game statistics query?

Index

The partial index on team you already have should look like this to allow index-only scans:

CREATE INDEX on team (matchid, id) WHERE winner -- boolean

But from what I see, you might just add a winner column to participant and drop the table team completely (unless there is more to it).

Also, that index is not going to help much, because (telling from your query plan) the table has 800k rows, half of which qualify:

rows=399999 ... Filter: (winner = 'True'::text) ... Rows Removed by Filter: 399999

This index on match will help a little more (later) when you have more different matchtypes and matchversions:

CREATE INDEX on match (matchtype_id, matchversion_id, match_id);

Still, while 100k rows qualify out of 400k, the index is only useful for an index only scan. Otherwise, a sequential scan will be faster. An index typically pays for about selecting 5 % of the table or less.

Your main problem is that you are obviously running a test case with hardly realistic data distribution. With more selective predicates, indexes will be used more readily.

Aside

Make sure you have configured basic Postgres settings like random_page_cost or work_mem etc.

enable_seqscan = on goes without saying. This is only turned off for debugging or locally as a desperate measure of last resort.

更多推荐

使用多个连接和分组优化SQL查询（Postgres 9.3）(Optimizing SQL query with multiple joins and grouping (Postgres 9.3))