我在 Postgres 9.4.1 中有一个大约 3.25M 行的表,格式如下
CREATE TABLE stats
(
id serial NOT NULL,
type character varying(255) NOT NULL,
"references" jsonb NOT NULL,
path jsonb,
data jsonb,
"createdAt" timestamp with time zone NOT NULL,
CONSTRAINT stats_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
type
是一个不超过 50 个字符的简单字符串。
该references
列是一个包含键值列表的对象。基本上任何简单键值列表,并且只有 1 层深,值始终是字符串。它可能是
{
"fruit": "plum"
"car": "toyota"
}
或者它可能是
{
"project": "2532"
}
createdAt
时间戳并不总是从数据库生成(但如果未提供值,则默认情况下会生成)
我目前正在使用仅包含测试数据的表格。在此数据中,每一行都有一个project
键作为参考。所以有 325 万行带有项目键。恰好有 400,000 个不同的project
参考值。该字段只有 5 个不同的值type
,这在生产中可能不会超过几百个。
所以我试图索引表以快速执行以下查询:
SELECT
EXTRACT(EPOCH FROM (MAX("createdAt") - MIN("createdAt")))
FROM
stats
WHERE
stats."references"::jsonb ? 'project' AND
(
stats."type" = 'event1' OR
(
stats."type" = 'event2' AND
stats."createdAt" > '2015-11-02T00:00:00+08:00' AND
stats."createdAt" < '2015-12-03T23:59:59+08:00'
)
)
GROUP BY stats."references"::jsonb->> 'project'
该查询基于具有相同引用的两个统计行返回两个事件之间的时间距离。在这种情况下project
。每个type
和选定的reference
值只有 1 行,但也可能没有行,在这种情况下返回的结果为 0(稍后在较大查询的不同部分进行平均)。
我已经在createdAt
type
和references
列上创建了一个索引,但查询执行计划似乎是在进行全面扫描。
指标
CREATE INDEX "stats_createdAt_references_type_idx"
ON stats
USING btree
("createdAt", "references", type COLLATE pg_catalog."default");
执行计划:
HashAggregate (cost=111188.31..111188.33 rows=1 width=38)
(actual time=714.499..714.499 rows=0 loops=1)
Group Key: ("references" ->> 'project'::text)
-> Seq Scan on stats (cost=0.00..111188.30 rows=1 width=38)
(actual time=714.498..714.498 rows=0 loops=1)
Filter: (
(("references" ? 'project'::text)
AND ((type)::text = 'event1'::text)) OR
(((type)::text = 'event2'::text)
AND ("createdAt" > '2015-11-02 05:00:00+13'::timestamp with time zone)
AND ("createdAt" < '2015-12-04 04:59:59+13'::timestamp with time zone)))
Rows Removed by Filter: 3258680
Planning time: 0.163 ms
Execution time: 714.534 ms
我真的不太了解索引和查询执行计划,所以如果有人能指出我正确的方向,那就太好了。
编辑
正如 Erwin 所指出的,看起来即使我确实有正确的索引,表扫描仍然会发生,因为从查询返回的表部分非常大。这是否意味着对于这组数据,这是我可以获得的最快查询时间?我假设如果我在没有项目引用的情况下再添加 60M 不相关的行,它可能会使用索引(如果我有正确的索引),但我看不出如何通过添加更多数据来加快查询速度。也许我错过了什么。
根据您当前的解释,索引对您当前的查询没有多大帮助(如果有的话)。
这也是行的总数,所以这个谓词是
true
(几乎)每一行......而且根本没有选择性。jsonb
但是该列没有有用的索引"references"
。将它包含在btree索引中("createdAt", "references", type)
是毫无意义的。即使你有一个通常更有用的 GIN 索引,
"reference"
比如:... Postgres 仍然没有关于列内各个键的有用统计信息
jsonb
。您的查询选择了一种类型的全部和另一种类型的未知部分。这估计占所有行的 20-40%。顺序扫描肯定是最快的计划。索引开始对大约 5% 或更少的行有意义。
要进行测试,您可以通过在会话中设置调试目的来强制使用可能的索引:
重置:
你会看到更慢的查询......
您按项目值分组:
和:
平均每个项目 8 行。根据值频率,如果我们只在 LATERAL 子查询中为每个项目选择最小值和最大值,我们仍然必须检索所有行的估计 3 - 20% ...
试试这个索引,它比你现在拥有的更有意义:
Postgres 可能仍会退回到顺序扫描......
可以使用规范化模式/更具选择性的标准/仅选择最小值和最大值的更智能查询来完成更多工作
"createdAt"
......询问
我会这样写你的查询:
笔记
不要在这里投:
stats."references"
::jsonb? 'project'该列
jsonb
已经存在,您将一无所获。如果谓词是选择性的,索引的使用可能会被强制转换禁止。您的谓词在下限和上限
"createdAt"
可能不正确。要包括整天,请考虑我建议的替代方案。references
是一个保留字,因此您必须始终将其双引号。不要将其用作标识符。双引号 CaMeL-case 名称类似"createdAt"
。允许,但容易出错,不必要的复杂化。type
这些似乎都说不通。
varchar(255)
本身几乎没有任何意义。255 个字符是一个任意限制,在 Postgres 中没有意义。integer
列type_id
(引用一个小type
表),每行仅占用 4 个字节,并使索引更小更快。理想情况下,您将有一个
project
表,列出所有项目和另一个小整数 FKproject_id
列stats
。将使任何此类查询更快。对于选择性标准,更快的查询是可能的——即使没有建议的规范化。沿着这些线:优化 GROUP BY 查询以检索每个用户的最新记录