#10570·timescaledb

minmax sparse index not used for INSERT with UNIQUE constraint

Author: akuzmCreated Sep 8, 2026Updated Sep 8, 2026
LabelsColumnstore
-- The configured minmax sparse index is not applied for the INSERT unique-constraint conflict check.

create table ticket_scans(scan_time timestamptz not null, ticket_code text);
select create_hypertable('ticket_scans', 'scan_time');
create unique index on ticket_scans(ticket_code, scan_time);
alter table ticket_scans set (timescaledb.compress,
    timescaledb.compress_orderby = 'scan_time',
    timescaledb.compress_index = 'minmax("ticket_code")');

insert into ticket_scans
select '2026-01-01'::timestamptz + interval '1 second' * i, 'code-' || i
from generate_series(1, 10) i;
select compress_chunk(show_chunks('ticket_scans'));

set timescaledb.debug_compression_path_info = on;

-- baseline to demonstrate the index itself works: the key is below the batch minimum, DELETE never fetches
-- the batch
delete from ticket_scans where ticket_code = 'aaa-lost';
-- INFO:  Number of compressed rows fetched from table scan: 0.

-- an INSERT of the same key fetches and decompresses the batch
explain (analyze, costs off, buffers off, summary off, timing off)
insert into ticket_scans values ('2026-01-01 00:00:05.5', 'aaa-lost');
-- INFO:  Number of compressed rows fetched from table scan: 1.
-- shows: Batches scanned: 1, Batches filtered after decompression: 1

This is for minmax specifically, if you change the index type to bloom, it will be used for INSERT as well.

The index is also used properly if the columns is an orderby.