- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我浏览了其他一些帖子并设法使我的查询运行得更快一些。但是,我对如何进一步优化此查询一头雾水。我打算在一个网站上使用它,它会在加载页面时执行查询,但是 5.5 秒对于等待应该更简单的事情来说太长了。最大的表有大约 4,000,000 行,其他表各有大约 400,000 行。
表结构
匹配
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'
上创建了单独的索引>(因为那只是我感兴趣的)。请注意 enable_seqscan = on
因为当它关闭时查询会非常慢。本质上,我想要得到的结果是这样的:
Champion |item0 | item1 | ... | item5
champ_name | num | num1 | ... | num5
...
由于我在数据库设计方面仍然是初学者,如果我的整体表设计存在缺陷,我不会感到惊讶。不过,我仍然倾向于绝对低效的查询。我玩过内连接和左连接——虽然没有显着差异。此外,匹配必须是 bigint
(或大于 integer
的值,因为它太小了)。
最佳答案
我建议:
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
创建查找表, matchtype
和 region
并且只在match
中写入一个小整数ID .
似乎是 participant.item0
列.. item5
和 highestAchievedSeasonTier
可能是 integer
, 但被定义为 text
?
专栏team.winner
好像是boolean
, 但被定义为 text
.
我还更改了列的顺序以提高效率。详情:
基于上述修改并针对 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 WHERE
不需要条件。
在 Postgres 9.4 或更高版本中,您可以使用新聚合 FILTER
语法代替。详细信息和备选方案:
team
上的部分索引你已经有应该看起来像这样以允许仅索引扫描:
CREATE INDEX on team (matchid, id) WHERE winner -- boolean
但据我所知,您可能只需添加一个 winner
列到 participant
并删除表格 team
完全(除非还有更多内容)。
此外,该索引不会有多大帮助,因为(从您的查询计划来看)该表有 80 万行,其中一半符合条件:
rows=399999 ... Filter: (winner = 'True'::text) ... Rows Removed by Filter: 399999
这个索引在match
当您有更多不同的匹配类型和匹配版本时(稍后)会有更多帮助:
CREATE INDEX on match (matchtype_id, matchversion_id, match_id);
不过,虽然 400k 行中有 100k 行符合条件,但该索引仅对仅索引扫描有用。否则,顺序扫描会更快。索引通常只需要选择表的 5% 或更少。
您的主要问题是您显然正在运行一个几乎不现实的数据分布的测试用例。使用更多选择性谓词,索引将更容易使用。
确保你有 configured basic Postgres settings like random_page_cost
or work_mem
etc.
enable_seqscan = on
一点不吭就走了。这仅在调试时或在本地作为万不得已的最后手段而关闭。
关于postgresql - 使用多个连接和分组优化 SQL 查询 (Postgres 9.3),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34969720/
您好,我正在处理 BIRT 报告。我有一个查询,我必须对父级的重复数据进行分组,但子级也不能分组! 在我的查询中: item 是父项,item_ledger_entry 是子项。我有来自 item.N
我正在使用 GA API。 这是针对 MCF 目标报告(底部)的标准目标完成指标表(顶部) 看一下这个: 总数加起来 (12,238),但看看按 channel 分组的分割有多么不同!我以为这些会很接
我正在开发一个流量计数器,我想获得 IP 和重复计数,但是如何? 就像是 :select ip, count(ip) from Redirect 返回 : null total ip count 重定
我尝试编写一个正则表达式来匹配条件表达式,例如: a!=2 1+2=2+a 我尝试提取运算符。我当前的正则表达式是“.+([!=<>]+).+” 但问题是匹配器总是尝试匹配组中可能的最短字符串
在 MS Transact SQL 中,假设我有一个这样的表(订单): Order Date Order Total Customer # 09/30/2008 8
我想按 m.ID 分组,并对每个 m.id 求和 (pm.amount_construction* prod.anzahl) 实际上我有以下结果: Meterial_id | amount_const
我想根据多列中的值对值进行分组。这是一个例子: 我想得到输出: {{-30,-50,20},{-20,30,60},{-30,NULL or other value, 20}} 我设法到达: SELE
我正在尝试找出运行此查询的最佳方式。我基本上需要返回在我们的系统中只下了一个订单的客户的“登录”字段列表(登录字段基本上是客户 ID/ key )。 我们系统的一些背景...... 客户在同一日期下的
给定以下mysql结果集: id code name importance '1234', 'ID-CS-B', 'Chocolate Sauce'
大家好,我的数据框中有以下列: LC_REF 1 DT 16 2C 2 DT 16 2C 3 DT 16 2C 1 DT 16 3C 6 DT 16 3C 3
我有这样的 mongoDB 集合 { "_id" : "EkKTRrpH4FY9AuRLj", "stage" : 10, }, { "_id" : "EkKTRrpH4FY9
假设我有一组数据对,其中 index 0 是值,index 1 是类型: input = [ ('11013331', 'KAT'), ('9085267',
java中用stream进行去重,排序,分组 一、distinct 1. 八大基本数据类型 List collect = ListUtil.of(1, 2, 3, 1, 2).stream().fil
基本上,我从 TABLE_A 中的这个开始 France - 100 France - 200 France - 300 Mexico - 50 Mexico - 50 Mexico - 56 Pol
我希望这个正则表达式 ([A-Z]+)$ 将选择此示例中的最后一次出现: AB.012.00.022ABC-1 AB.013.00.022AB-1 AB.014.00.022ABAB-1 但我没有匹配
我创建了一个数据透视表,但数据没有组合在一起。 任何人都可以帮助我获得所需的格式吗? 我为获取数据透视表而编写的查询: DECLARE @cols AS NVARCHAR(MAX), -- f
我想按时间段(月,周,日,小时,...)选择计数和分组。例如,我想选择行数并将它们按 24 小时分组。 我的表创建如下。日期是时间戳。 CREATE TABLE MSG ( MSG_ID dec
在 SQL Server 2005 中,我有一个包含如下数据的表: WTN------------Date 555-111-1212 2009-01-01 555-111-1212 2009-
题 假设我有 k 个标量列,如果它们沿着每列彼此在一定距离内,我想对它们进行分组。 假设简单 k 是 2 并且它们是我唯一的列。 pd.DataFrame(list(zip(sorted(choice
问题 在以下数据框中 df : import random import pandas as pd random.seed(999) sz = 50 qty = {'one': 1, 'two': 2
我是一名优秀的程序员,十分优秀!