[Bug]: Query does not use index on partially compressed chunks

[antekresic]

What type of bug is this?

Performance issue

What subsystems and features are affected?

Compression

What happened?

When inserting into a hypertable with defined indexes and compressed chunks, querying does not use index scans where it seems appropriate for the uncompressed portion of the partially compressed chunk.

It seems to always default to sequential scans even if filtering 1000s of rows while an index exists which ideally should be used.

TimescaleDB version affected

main branch

PostgreSQL version used

14.5

What operating system did you use?

Ubuntu 22.04.1

What installation method did you use?

Source

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

N/A

How can we reproduce the bug?

postgres=# CREATE TABLE compression_insert(filler_1 int, filler_2 int, filler_3 int, time timestamptz NOT NULL, device_id int, v0 int, v1 int, v2 float, v3 float);
CREATE INDEX ON compression_insert(time);
CREATE INDEX ON compression_insert(device_id,time);
SELECT create_hypertable('compression_insert','time',create_default_indexes:=false);
ALTER TABLE compression_insert SET (timescaledb.compress, timescaledb.compress_orderby='time DESC', timescaledb.compress_segmentby='device_id');
CREATE TABLE
CREATE INDEX
CREATE INDEX
        create_hypertable         
----------------------------------
 (14,public,compression_insert,t)
(1 row)

ALTER TABLE
postgres=# INSERT INTO compression_insert(time,device_id,v0,v1,v2,v3) SELECT time, device_id, device_id, device_id + 2, device_id + 0.5, NULL FROM generate_series('2000-01-22 0:00:00+0'::timestamptz,'2000-01-24 23:55:00+0','2m') gtime(time), generate_series(1,100,1) gdevice(device_id);
INSERT 0 215800
postgres=# SELECT compress_chunk(c.schema_name|| '.' || c.table_name)                                                                                                                          FROM _timescaledb_catalog.chunk c, _timescaledb_catalog.hypertable ht where c.hypertable_id = ht.id and ht.table_name = 'compression_insert' and c.compressed_chunk_id IS NULL
ORDER BY c.table_name DESC;
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_14_76_chunk
(1 row)

postgres=# INSERT INTO compression_insert(time,device_id,v0,v1,v2,v3) SELECT time, device_id, device_id, device_id + 2, device_id + 0.5, NULL FROM generate_series('2000-01-22 0:00:00+0'::timestamptz,'2000-01-24 23:55:00+0','2m') gtime(time), generate_series(1,100,1) gdevice(device_id);
INSERT 0 215800
postgres=# EXPLAIN ANALYZE SELECT * 
FROM compression_insert 
WHERE device_id = 2 AND time = '2000-01-22';
                                                                                                QUERY PLAN                                                                                                 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=11.72..10000005052.72 rows=1001 width=48) (actual time=39.125..39.126 rows=0 loops=1)
   ->  Custom Scan (DecompressChunk) on _hyper_14_76_chunk  (cost=11.72..11.72 rows=1000 width=48) (actual time=0.011..0.012 rows=0 loops=1)
         Filter: ((device_id = 2) AND ("time" = '2000-01-22 00:00:00+01'::timestamp with time zone))
         ->  Index Scan using compress_hyper_15_77_chunk__compressed_hypertable_15_device_id_ on compress_hyper_15_77_chunk  (cost=0.15..11.72 rows=1 width=280) (actual time=0.010..0.010 rows=0 loops=1)
               Index Cond: (device_id = 2)
               Filter: ((_ts_meta_min_1 <= '2000-01-22 00:00:00+01'::timestamp with time zone) AND (_ts_meta_max_1 >= '2000-01-22 00:00:00+01'::timestamp with time zone))
               Rows Removed by Filter: 3
   ->  Seq Scan on _hyper_14_76_chunk  (cost=10000000000.00..10000005036.00 rows=1 width=48) (actual time=39.111..39.112 rows=0 loops=1)
         Filter: ((device_id = 2) AND ("time" = '2000-01-22 00:00:00+01'::timestamp with time zone))
         Rows Removed by Filter: 215800
 Planning Time: 0.972 ms
 Execution Time: 39.191 ms
(12 rows)

Running analyze on the hypertable or chunk does not help.

[lkshminarayanan]

Duplicate of #5413

[lkshminarayanan]

This is not a duplicate rather related to #5413.

#5413 reports that the index is not being used even when the query is only on the uncompressed chunks of a compressed table. This is not expected and it is probably a bug in a certain case (at the time of this comment, #5413 is still unverified).

But this issue, 5432, reports that the when there is a mix of compressed and uncompressed chunks and if a query has to scan both type of chunks, a seq scan is always chosen. A better plan is to use seq scan for the compressed chunks and index scan for the uncompressed chunks.

[lkshminarayanan]

@antekresic Verified and reopened this bug, Sorry for the confusion earlier!