英文:
How to optimize PostgreSQL JOIN based on time ranges
问题
以下是翻译好的内容：
我们有一个昂贵的查询，需要从存储对象指标的表中提取每日值，这些指标跨越不同的时间范围。我们已经分离出查询中昂贵的部分，并提取了以下最小示例。
关于涉及的表的一些详细信息：
history表定义如下：
create table history
(
start_ts timestamp,
end_ts timestamp,
obj_id integer,
metrics jsonb
);
create index idx_history_timerange
on metrics_history (start_ts, end_ts);
create index idx_history_file_id
on metrics_history (obj_id);
create index history_full_cover_idx
on history (obj_id, start_ts, end_ts) include (metrics);
-
all_objs_per_scope是obj_id和scope_id之间的简单多对多关系。 -
history表总共包含大约 600 万行，对于大约 60 万个不同的对象。对象的时间范围不会重叠。
以下是最小示例查询。日期、scope_id 以及 metrics JSON 字段的键是用户输入。
在我们的测试中，查询运行时间大致与时间范围的长度成正比，如果总体速度更快，这可能是可以接受的。理想情况下，整个年度的查询应该在 10 秒以内完成。
给定范围内对象的数量也会影响运行时间，但不像给定时间范围的长度那样直接影响。更多信息如下。
给定的 JSON 键似乎不会影响运行时间。
EXPLAIN ANALYZE
WITH timestamp_series AS (
SELECT series.ts as ts
FROM generate_series(
'2022-07-12'::timestamp,
'2022-08-12'::timestamp,
'1 day'::interval) AS series(ts)
)
SELECT
ts,
obj_id,
COALESCE((metrics ->> '64')::FLOAT, 0) AS value
FROM timestamp_series
JOIN history ON history.start_ts <= timestamp_series.ts
AND history.end_ts > timestamp_series.ts
AND obj_id IN (SELECT obj_id AS id FROM all_objs_per_scope WHERE scope_id = 87)
AND history.start_ts <= '2022-08-12'
AND history.end_ts >= '2022-07-12'
从运行时间来看，计划器选择一个计划而不是另一个的决定似乎是合理的。
有什么我们可以做的，包括彻底重构我们的表，以加快速度吗？
英文:
We have an expensive query that needs to extract daily values from a table storing metrics for objects over timeranges. We have isolated the expensive part of the query and extracted the following minimal example.
Some details on the tables involved:
history is defined as
create table history
(
start_ts timestamp,
end_ts timestamp,
obj_id integer,
metrics jsonb
);
create index idx_history_timerange
on metrics_history (start_ts, end_ts);
create index idx_history_file_id
on metrics_history (obj_id);
create index history_full_cover_idx
on history (obj_id, start_ts, end_ts) include (metrics);
all_objs_per_scope is a simple m:n relation between obj_id and scope_id
history contains about 6 million rows in total for roughly 600k distinct objects. The timeranges per object do not overlap.
The following is the minimal example query. The dates, the scope_id and the key for the metrics JSON field are user input.
In our testing the query runtime scales roughly linearly with the length of timerange, which might be acceptable if it was faster in general. Ideally this should be fater than 10s for a whole year.
The number of objects in the given scope also affects the runtime, but not as straight forwardly as the length of the given timerange. More on than below.
The given JSON key seems to have no effect on runtime.
EXPLAIN ANALYZE
WITH timestamp_series AS (
SELECT series.ts as ts
FROM generate_series(
'2022-07-12'::timestamp,
'2022-08-12'::timestamp,
'1 day'::interval) AS series(ts)
)
SELECT
ts,
obj_id,
COALESCE((metrics ->> '64')::FLOAT, 0) AS value
FROM timestamp_series
JOIN history ON history.start_ts <= timestamp_series.ts
AND history.end_ts > timestamp_series.ts
AND obj_id IN (SELECT obj_id AS id FROM all_objs_per_scope WHERE scope_id = 87)
AND history.start_ts <= '2022-08-12'
AND history.end_ts >= '2022-07-12'
There are about 500k obj_ids in the scope with id 87 (reminder that's 500k of 600k total). And for that scope the planner comes up with this query plan:
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Hash Join (cost=42119.88..17542416.61 rows=147924810 width=20) (actual time=250.729..13941.074 rows=8985650 loops=1) |
| Hash Cond: (history.obj_id = all_objs_per_scope.obj_id) |
| -> Nested Loop (cost=0.43..15942340.50 rows=170863778 width=262) (actual time=0.041..7052.328 rows=8985650 loops=1) |
| -> Function Scan on generate_series series (cost=0.00..10.00 rows=1000 width=8) (actual time=0.010..0.052 rows=32 loops=1) |
| -> Index Scan using idx_history_timerange on history (cost=0.43..14233.69 rows=170864 width=270) (actual time=0.024..187.880 rows=280802 loops=32) |
| Index Cond: ((start_ts <= series.ts) AND (start_ts <= '2022-08-12 00:00:00'::timestamp without time zone) AND (end_ts > series.ts) AND (end_ts >= '2022-07-12 00:00:00'::timestamp without time zone))|
| -> Hash (cost=35634.70..35634.70 rows=518779 width=4) (actual time=250.024..250.026 rows=525331 loops=1) |
| Buckets: 1048576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 26661kB |
| -> Bitmap Heap Scan on all_objs_per_scope (cost=7650.97..35634.70 rows=518779 width=4) (actual time=32.684..163.549 rows=525331 loops=1) |
| Recheck Cond: (scope_id = 87) |
| Heap Blocks: exact=21499 |
| -> Bitmap Index Scan on all_objs_per_scope_unique_idx (cost=0.00..7521.27 rows=518779 width=0) (actual time=29.639..29.640 rows=525331 loops=1) |
| Index Cond: (scope_id = 87) |
|Planning Time: 0.399 ms |
|Execution Time: 14250.802 ms |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
A smaller scope with about 160k objects, results in the following plan. The breaking point between which plan is chosen seems to be around 50% of existing objects in the scope. Smaller scopes use the second plan, larger ones the first one.
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Nested Loop (cost=57568.26..7647417.04 rows=44265468 width=20) (actual time=2698.298..2698.302 rows=0 loops=1) |
| Join Filter: ((history.start_ts <= series.ts) AND (history.end_ts > series.ts)) |
| -> Function Scan on generate_series series (cost=0.00..10.00 rows=1000 width=8) (actual time=0.009..0.014 rows=32 loops=1) |
| -> Materialize (cost=57568.26..344604.50 rows=398389 width=270) (actual time=84.321..84.321 rows=0 loops=32) |
| -> Hash Join (cost=57568.26..342612.56 rows=398389 width=270) (actual time=2698.277..2698.279 rows=0 loops=1) |
| Hash Cond: (history.obj_id = all_objs_per_scope.obj_id) |
| -> Bitmap Heap Scan on history (cost=29896.69..310904.30 rows=1537774 width=270) (actual time=133.159..471.802 rows=707498 loops=1) |
| Recheck Cond: ((start_ts <= '2022-08-12 00:00:00'::timestamp without time zone) AND (end_ts >= '2022-07-12 00:00:00'::timestamp without time zone)) |
| Heap Blocks: exact=238148 |
| -> Bitmap Index Scan on idx_history_timerange (cost=0.00..29512.24 rows=1537774 width=0) (actual time=75.315..75.315 rows=707498 loops=1) |
| Index Cond: ((start_ts <= '2022-08-12 00:00:00'::timestamp without time zone) AND (end_ts >= '2022-07-12 00:00:00'::timestamp without time zone)) |
| -> Hash (cost=25731.06..25731.06 rows=155241 width=4) (actual time=2090.490..2090.492 rows=157424 loops=1) |
| Buckets: 262144 Batches: 1 Memory Usage: 7583kB |
| -> Bitmap Heap Scan on all_objs_per_scope (cost=2291.55..25731.06 rows=155241 width=4) (actual time=2018.784..2065.120 rows=157424 loops=1) |
| Recheck Cond: (scope_id = 80) |
| Heap Blocks: exact=15024 |
| -> Bitmap Index Scan on all_objs_per_scope_unique_idx (cost=0.00..2252.74 rows=155241 width=0) (actual time=2016.143..2016.143 rows=157424 loops=1)|
| Index Cond: (scope_id = 80) |
|Planning Time: 0.502 ms |
|Execution Time: 2699.537 ms |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
As far as we can tell from the runtimes, the decision by the planner to choose one plan over the other seems reasonable.
Is there anything we can do, including radically restructuring our tables to speed this up?
EDIT: As @jjanes pointed out inthe comments the example for the second query plan was badly chosen as it returns 0 rows. (The particular scope had no data for the given timerange.) I fixed that and here is the new query plan for the same scope. Looks essentially the same with the exception that it does return "a few" rows.
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Nested Loop (cost=74958.41..589737.44 rows=2629659 width=20) (actual time=60.952..5360.037 rows=10326172 loops=1) |
| Join Filter: ((metrics_history.start_ts <= series.ts) AND (metrics_history.end_ts > series.ts)) |
| Rows Removed by Join Filter: 236923 |
| -> Function Scan on generate_series series (cost=0.00..10.00 rows=1000 width=8) (actual time=0.009..0.040 rows=31 loops=1) |
| -> Materialize (cost=74958.41..155891.66 rows=23667 width=270) (actual time=1.966..41.804 rows=340745 loops=31) |
| -> Gather (cost=74958.41..155773.33 rows=23667 width=270) (actual time=60.925..521.223 rows=340745 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Nested Loop (cost=73958.41..152406.63 rows=9861 width=270) (actual time=56.552..716.250 rows=113582 loops=3) |
| -> Parallel Bitmap Heap Scan on metrics_history (cost=73957.98..120719.55 rows=12275 width=270) (actual time=56.483..119.172 rows=165519 loops=3) |
| Recheck Cond: ((start_ts <= '2023-05-12 00:00:00'::timestamp without time zone) AND (end_ts >= '2023-04-12 00:00:00'::timestamp without time zone)) |
| Heap Blocks: exact=18928 |
| -> Bitmap Index Scan on idx_metrics_history_timerange (cost=0.00..73950.61 rows=29460 width=0) (actual time=47.164..47.164 rows=496558 loops=1) |
| Index Cond: ((start_ts <= '2023-05-12 00:00:00'::timestamp without time zone) AND (end_ts >= '2023-04-12 00:00:00'::timestamp without time zone)) |
| -> Index Only Scan using all_files_per_scope_unique_idx on all_files_per_scope (cost=0.43..2.58 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=496558)|
| Index Cond: ((scope_id = 67) AND (file_id = metrics_history.file_id)) |
| Heap Fetches: 340745 |
|Planning Time: 0.416 ms |
|Execution Time: 5750.332 ms |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
答案1
得分: 0
你可以用类型为 tsrange 的单列替换两列 (start_ts, end_ts)。然后在该列上建立 GiST 索引，并使用 <@ 进行测试，而不是使用不等式的连接。
英文:
You could replace the two columns (start_ts,end_ts) with a single column of type tsrange. Then build a GiST index on that column, and test it with <@ rather than with a conjunction of inequalities.
答案2
得分: 0
以下是您要翻译的代码部分：
SELECT
history.start_ts,
obj_id,
COALESCE((metrics ->> '64')::FLOAT, 0) AS value
FROM
history
JOIN
all_objs_per_scope AS aops
ON
history.obj_id = aops.obj_id
WHERE
scope_id = 87
AND
daterange(history.start_ts, history.end_ts) <@ daterange('2022-07-12', '2022-08-12')
如果您需要任何进一步的翻译，请随时提问。
英文:
Not really an answer as not tested and based on a certain amount of guessing about what you want.
SELECT
history.start_ts,
obj_id,
COALESCE((metrics ->> '64')::FLOAT, 0) AS value
FROM
history
JOIN
all_objs_per_scope AS aops
ON
history.obj_id = aops.obj_id
WHERE
scope_id = 87
AND
daterange(history.start_ts, history.end_ts) <@ daterange('2022-07-12', '2022-08-12')
答案3
得分: 0
以下是您要翻译的内容：
似乎您将CTE连接的唯一原因是为了提供跨越多天的历史行的重复答案。与为每个历史行生成一个月的重复条目，然后筛选出错误的条目不同，您可以通过为每个历史行调用generate_series并使用定制参数来直接生成正确的重复项。
所以类似于：
SELECT
ts,
obj_id,
COALESCE((metrics ->> '64')::FLOAT, 0) AS value
FROM
history
CROSS JOIN
generate_series(greatest('2022-07-12',history.start_ts::date+1),least('2022-08-12',end_ts::date),interval '1 day') f(ts)
where obj_id IN (SELECT obj_id AS id FROM all_objs_per_scope WHERE scope_id = 87)
AND history.start_ts <= '2022-08-12'
AND history.end_ts >= '2022-07-12';
在我的环境中，这比您原来的公式快大约两倍。但是对于我来说，您原来的公式比您报告的快大约4倍，如果不知道原因，很难预测您是否会看到相同的相对收益，或者更多还是更少。
英文:
It looks like the only reason you are doing the join to the CTE is for it to provide duplicate answers for the history rows which span more than one day. Rather than generating a month of duplicate entries for every history row and then filtering the wrong ones out, you could just generate the correct duplicates to start with, by calling generate_series once per history row with tailored arguments.
So something like:
SELECT
ts,
obj_id,
COALESCE((metrics ->> '64')::FLOAT, 0) AS value
FROM
history
CROSS JOIN
generate_series(greatest('2022-07-12',history.start_ts::date+1),least('2022-08-12',end_ts::date),interval '1 day') f(ts)
where obj_id IN (SELECT obj_id AS id FROM all_objs_per_scope WHERE scope_id = 87)
AND history.start_ts <= '2022-08-12'
AND history.end_ts >= '2022-07-12';
In my hands this is about twice as fast as your original formulation. But your original formulation is about 4 times faster for me that what you report, and without knowing why that is the case makes it hard to predict whether you will see the same relative gain, or more, or less.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论