I’m using this quire straight forward query to try delete millions of rows. But it takes around minute to do it. Not sure if this is the best approach to delete rows in this way. Any suggestions?
delete from timeseries where sid = ANY (‘{uuid1, uuid2, uuid3, uuid4, uuid5, uuid6, uuid7, uuid8, uuid9, uuid10, uuid11, uuid12, uuid13, uuid14, uuid15, uuid16, uuid17, uuid18, uuid19, uuid20…, uid40}’)
and time >= ‘2020-12-28T05:25:20Z’ and time<= ‘2020-12-29T09:15:00Z’;
Is there an index on the UUID column? Apart from that, have you had a look at the execution plan?
Hi, thanks I really appreaciate your response.
Yes there’s an index in the UUID (which is actually called sid) colum. This is the script we use to create this table
CREATE TABLE IF NOT EXISTS public.timeseries (
“time” timestamp without time zone NOT NULL,
sid uuid NOT NULL,
vdouble double precision, – Float, Double
vint bigint, – Byte, Integer, Long
vtext text, – String, StringFixed
vbool boolean, – Boolean
vtimestamp timestamp without time zone, – TimeStamp
varraybool boolean[], – Array Boolean
varraydouble double precision[], – Array Float, Double
varrayint bigint[], – Array Integer, Long
varraytext text[], – Array String
multified jsonb, – MultiField
vbytes bytea, – Bytes
indices jsonb,
pointmetadata jsonb
)
WITH (
OIDS = FALSE)
TABLESPACE pg_default;
– Timescale Hypertable
SELECT
create_hypertable (‘public.timeseries’,
‘time’);
– DROP INDEX public.timeseries_sid_time;
CREATE UNIQUE INDEX IF NOT EXISTS timeseries_sid_time
ON public.timeseries USING btree
(sid ASC NULLS LAST, “time” ASC NULLS LAST)
TABLESPACE pg_default;
and this is what I get when I ran EXPLAIN ANALYZE
“Custom Scan (HypertableModify) (cost=0.43…39543.30 rows=517763 width=10) (actual time=1560.088…1560.090 rows=0 loops=1)”
" → Delete on timeseries (cost=0.43…39543.30 rows=517763 width=10) (actual time=1560.088…1560.090 rows=0 loops=1)"
" Delete on _hyper_1_1_chunk timeseries_1"
" → Index Scan using _hyper_1_1_chunk_timeseries_sid_time on _hyper_1_1_chunk timeseries_1 (cost=0.43…39543.30 rows=517763 width=10) (actual time=0.057…1356.549 rows=519580 loops=1)"
" Index Cond: ((sid = ANY (‘{036043cc-b58a-4a4e-973c-95946b8584f2,04412e3e-ed88-424f-8140-d94f1734cb7f,058c8190-55d0-4e18-8187-f675f3d5451c,16a5cae0-ebdd-4f27-9f2d-766098d95328,199641eb-b45d-46d8-acc3-a7da90f2b886,21a0661f-203b-4099-be51-9e2f67319679,2550070d-f40b-45a1-b43b-d49f583e541e,2558bf6d-5675-4041-a31b-66134c70b245,2a9ea6b0-1d64-436d-8a9d-859f531463a4,2ab84b5b-432c-42dc-aa0f-620f6786e9d0,3377b62c-8308-4515-8f4e-37647e924e54,34bd4e6c-82a0-46f7-928e-22aa4ae7e91a,378423c3-6368-413f-acec-0fc8be190f34,3e605c26-e2ed-4e03-9306-e3db93a0eecf,49ca72f6-2e48-4915-9f2b-d49b2f76d6e0,4f7bb2fd-4dd4-45c7-99d3-f72f24d87178,57f70e55-5c0d-4193-90fb-faf3dbd107ae,65d1aead-0241-484c-8724-61722dfe54a1,6e3e77d1-9ff8-4d8b-8df5-282deb9d143e,6fc4372e-8ed3-4dd1-bd8d-c4f53169f6a8,720d73ae-0e90-4775-ba83-e93ec75032e5,7cf38576-5dcc-4e2e-8d23-d40f58b1f13b,8a36d34a-6408-448e-9f3d-d9e01ba05d20,92217f35-01eb-4f3c-a042-adac6e25f986,945ff31e-2007-4cc4-ba2f-883604acebae,a22b3aba-69e9-4f0e-9035-483ead7d24cb,a505770d-fc4b-4470-b8e9-d32a141a2a97,a83d1fe6-fab8-439b-919a-7b9505660515,c4a2a63c-f909-45ed-b71f-d39a06c2607a,cd9f69c5-f4d2-412a-9ed8-11a210430361,cee5e9b3-5ce6-4436-bba3-d3046db4955e,e05c1d0f-5cea-40ce-841e-113a604355fb,e06e1a09-aa54-46ff-b734-f8c483f4b4b3,e58b5b4e-e31a-4e34-a17b-f405e650e4da,fd4f4216-5131-4d89-8b3a-36c11d082397}’::uuid[])) AND (““time”” >= ‘2020-12-28 05:25:20’::timestamp without time zone) AND (““time”” <= ‘2020-12-29 09:15:00’::timestamp without time zone))"
“Planning Time: 0.514 ms”
“Execution Time: 1560.107 ms”
Have you tried to break down and remove data in smaller batches of UIDs?
Maybe it become a big transaction.
Also, what is the chunk time interval of your hypertable and how big is the actual chunk that you’re deleting? is it compressed?
Compression and how long is each chunk can impact the size of the transaction and larger chunks will be very expensive to decompress and delete.
Maybe you can:
In different transactions because if you just send the delete everything related to the where clause will need to be decompressed / recompressed.