By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create extension if not exists "uuid-ossp";
create type metric_enum as enum('distance','speed','elevation');
create table example_input(
asset_id uuid
,timestamp timestamp(0)
,metric_name metric_enum
,metric_value int);
insert into example_input values
('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:14','distance',100)
,('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:14','speed',60)
,('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:14','elevation',15)
,('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:14','distance',120)
,('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:14','speed',80)
,('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:19','distance',140)
,('07536cba-6e00-4b8a-be02-9509228f4bb0','2024-12-16 17:43:19','elevation',20)
returning *;
CREATE EXTENSION
CREATE TYPE
CREATE TABLE
| asset_id | timestamp | metric_name | metric_value |
|---|---|---|---|
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | distance | 100 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | speed | 60 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | elevation | 15 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | distance | 120 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | speed | 80 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:19 | distance | 140 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:19 | elevation | 20 |
INSERT 0 7
select*,uuid_generate_v3(asset_id,concat(timestamp,row_number()over w))as update_id
from example_input
window w as(partition by asset_id,timestamp,metric_name
order by ctid)
order by asset_id, timestamp, update_id, metric_name;
| asset_id | timestamp | metric_name | metric_value | update_id |
|---|---|---|---|---|
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | distance | 100 | 7e88679b-5038-3124-b0e6-a4ed16316ba4 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | speed | 60 | 7e88679b-5038-3124-b0e6-a4ed16316ba4 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | elevation | 15 | 7e88679b-5038-3124-b0e6-a4ed16316ba4 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | distance | 120 | d1ca0905-7fa1-3f8c-9284-2d615bdaacd3 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | speed | 80 | d1ca0905-7fa1-3f8c-9284-2d615bdaacd3 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:19 | distance | 140 | 40e25bd6-55eb-34cd-a8c5-262cd9d8987b |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:19 | elevation | 20 | 40e25bd6-55eb-34cd-a8c5-262cd9d8987b |
SELECT 7
alter table example_input add column update_id uuid;
update example_input as a
set update_id=uuid_generate_v3(asset_id,concat(timestamp,(
select row_number()over(order by ctid)
from example_input as c
where a.asset_id=c.asset_id
and a.timestamp=c.timestamp
and a.metric_name=c.metric_name
order by c.ctid<>a.ctid limit 1)))
returning *;
ALTER TABLE
| asset_id | timestamp | metric_name | metric_value | update_id |
|---|---|---|---|---|
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | distance | 100 | 7e88679b-5038-3124-b0e6-a4ed16316ba4 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | speed | 60 | 7e88679b-5038-3124-b0e6-a4ed16316ba4 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | elevation | 15 | 7e88679b-5038-3124-b0e6-a4ed16316ba4 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | distance | 120 | d1ca0905-7fa1-3f8c-9284-2d615bdaacd3 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:14 | speed | 80 | d1ca0905-7fa1-3f8c-9284-2d615bdaacd3 |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:19 | distance | 140 | 40e25bd6-55eb-34cd-a8c5-262cd9d8987b |
| 07536cba-6e00-4b8a-be02-9509228f4bb0 | 2024-12-16 17:43:19 | elevation | 20 | 40e25bd6-55eb-34cd-a8c5-262cd9d8987b |
UPDATE 7
--performance on 200k rows
select setseed(.42);
insert into example_input
select uuid_generate_v3(uuid_nil(),(random()*1e3)::int::text)
,(now()-random()*'70 seconds'::interval)::timestamp(0)
,(array['distance','speed','elevation']::metric_enum[])[1+(random()*2)::int]
,(random()*100)::int
from generate_series(1,2e5);
| setseed |
|---|
SELECT 1
INSERT 0 200000
alter table example_input
drop column if exists update_id
,drop column if exists id
,add column update_id uuid
,add column id serial;
create index idx1 on example_input(asset_id,timestamp,metric_name,id)
with(fillfactor=100);
ALTER TABLE
CREATE INDEX
vacuum analyze example_input;
VACUUM
cluster example_input using idx1;
CLUSTER
explain analyze verbose
select*,uuid_generate_v3(asset_id,concat(timestamp,row_number()over w))as update_id
from example_input
window w as(partition by asset_id,timestamp,metric_name
order by ctid);
| QUERY PLAN |
|---|
| WindowAgg (cost=1.42..28567.34 rows=200007 width=74) (actual time=0.196..843.894 rows=200007 loops=1) |
| Output: asset_id, "timestamp", metric_name, metric_value, update_id, id, uuid_generate_v3(asset_id, concat("timestamp", row_number() OVER (?))), ctid |
| -> Incremental Sort (cost=1.42..23067.14 rows=200007 width=58) (actual time=0.138..158.321 rows=200007 loops=1) |
| Output: asset_id, "timestamp", metric_name, ctid, metric_value, update_id, id |
| Sort Key: example_input.asset_id, example_input."timestamp", example_input.metric_name, example_input.ctid |
| Presorted Key: example_input.asset_id, example_input."timestamp", example_input.metric_name |
| Full-sort Groups: 6148 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB |
| -> Index Scan using idx1 on public.example_input (cost=0.42..14056.52 rows=200007 width=58) (actual time=0.062..70.343 rows=200007 loops=1) |
| Output: asset_id, "timestamp", metric_name, ctid, metric_value, update_id, id |
| Planning Time: 0.417 ms |
| Execution Time: 861.788 ms |
EXPLAIN
explain analyze verbose
update example_input as a
set update_id=uuid_generate_v3(asset_id,timestamp::text || (
select row_number()over(order by id)
from example_input as c
where a.asset_id=c.asset_id
and a.timestamp=c.timestamp
and a.metric_name=c.metric_name
order by c.id<>a.id limit 1));
| QUERY PLAN |
|---|
| Update on public.example_input a (cost=0.00..1701726.50 rows=0 width=0) (actual time=6314.550..6314.552 rows=0 loops=1) |
| -> Seq Scan on public.example_input a (cost=0.00..1701726.50 rows=200007 width=22) (actual time=0.098..3463.995 rows=200007 loops=1) |
| Output: uuid_generate_v3(a.asset_id, ((a."timestamp")::text || ((SubPlan 1))::text)), a.ctid |
| SubPlan 1 |
| -> Limit (cost=8.47..8.47 rows=1 width=13) (actual time=0.011..0.011 rows=1 loops=200007) |
| Output: (row_number() OVER (?)), ((c.id <> a.id)), c.id |
| -> Sort (cost=8.47..8.47 rows=1 width=13) (actual time=0.010..0.010 rows=1 loops=200007) |
| Output: (row_number() OVER (?)), ((c.id <> a.id)), c.id |
| Sort Key: ((c.id <> a.id)) |
| Sort Method: quicksort Memory: 25kB |
| -> WindowAgg (cost=0.42..8.46 rows=1 width=13) (actual time=0.007..0.008 rows=2 loops=200007) |
| Output: row_number() OVER (?), (c.id <> a.id), c.id |
| -> Index Only Scan using idx1 on public.example_input c (cost=0.42..8.44 rows=1 width=4) (actual time=0.004..0.005 rows=2 loops=200007) |
| Output: c.id |
| Index Cond: ((c.asset_id = a.asset_id) AND (c."timestamp" = a."timestamp") AND (c.metric_name = a.metric_name)) |
| Heap Fetches: 518691 |
| Planning Time: 0.457 ms |
| Execution Time: 6314.638 ms |
EXPLAIN
select * from example_input
order by asset_id, timestamp, update_id, metric_name::metric_enum
fetch first 10 rows with ties;
| asset_id | timestamp | metric_name | metric_value | update_id | id |
|---|---|---|---|---|---|
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:09 | distance | 47 | 1cc013d0-f9e4-376a-ae19-370a4b832d4b | 170745 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:09 | speed | 77 | 1cc013d0-f9e4-376a-ae19-370a4b832d4b | 106587 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:09 | elevation | 85 | 1cc013d0-f9e4-376a-ae19-370a4b832d4b | 50134 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:09 | speed | 4 | f17eb59b-7ae9-30a0-b682-e00859d8e5c7 | 124767 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:10 | speed | 30 | 0c723551-80c0-3477-9576-e8d0046dcf09 | 117999 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:10 | distance | 19 | a8a2263c-02f7-3894-bfd5-e1d58f302c61 | 165042 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:10 | speed | 78 | a8a2263c-02f7-3894-bfd5-e1d58f302c61 | 58158 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:10 | elevation | 2 | a8a2263c-02f7-3894-bfd5-e1d58f302c61 | 105154 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:10 | speed | 13 | fd8254c6-c9c2-3cf7-84ee-06b8f2b5a9b0 | 164137 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:11 | distance | 59 | 1965ec51-70cf-3842-92b1-255f673ac890 | 144820 |
SELECT 10
alter table example_input
drop column if exists update_id
,drop column if exists id;
select*,uuid_generate_v3(asset_id,concat(timestamp,(row_number()over w)))as update_id
from example_input
window w as(partition by asset_id,timestamp,metric_name
order by ctid)
order by asset_id, timestamp, update_id, metric_name::metric_enum
fetch first 10 rows with ties;
ALTER TABLE
| asset_id | timestamp | metric_name | metric_value | update_id |
|---|---|---|---|---|
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:09 | distance | 47 | 1cc013d0-f9e4-376a-ae19-370a4b832d4b |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:09 | speed | 77 | 1cc013d0-f9e4-376a-ae19-370a4b832d4b |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:09 | elevation | 85 | 1cc013d0-f9e4-376a-ae19-370a4b832d4b |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:09 | speed | 4 | f17eb59b-7ae9-30a0-b682-e00859d8e5c7 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:10 | speed | 30 | 0c723551-80c0-3477-9576-e8d0046dcf09 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:10 | distance | 19 | a8a2263c-02f7-3894-bfd5-e1d58f302c61 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:10 | speed | 78 | a8a2263c-02f7-3894-bfd5-e1d58f302c61 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:10 | elevation | 2 | a8a2263c-02f7-3894-bfd5-e1d58f302c61 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:10 | speed | 13 | fd8254c6-c9c2-3cf7-84ee-06b8f2b5a9b0 |
| 0017c077-cbc1-3525-90b4-b505b5f8768f | 2024-12-30 22:45:11 | distance | 59 | 1965ec51-70cf-3842-92b1-255f673ac890 |
SELECT 10