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 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2
Answers
You could replace the two columns
(start_ts,end_ts)with a single column of typetsrange. Then build a GiST index on that column, and test it with<@rather than with a conjunction of inequalities.Not really an answer as not tested and based on a certain amount of guessing about what you want.