chunk_column_stats.out

-- This file and its contents are licensed under the Timescale License.
-- Please see the included NOTICE for copyright information and
-- LICENSE-TIMESCALE for a copy of the license.
\set PREFIX 'EXPLAIN (buffers off, costs off, timing off, summary off)'
CREATE OR REPLACE VIEW compressed_chunk_info_view AS
SELECT
   h.schema_name AS hypertable_schema,
   h.table_name AS hypertable_name,
   c.schema_name || '.' || c.table_name as chunk_name,
   c.status as chunk_status
FROM
   _timescaledb_catalog.hypertable h JOIN
  _timescaledb_catalog.chunk c ON h.id = c.hypertable_id
   LEFT JOIN _timescaledb_catalog.chunk comp
ON comp.id = c.compressed_chunk_id
;
CREATE TABLE sample_table (
       time TIMESTAMP WITH TIME ZONE NOT NULL,
       sensor_id INTEGER NOT NULL,
       cpu double precision null,
       temperature double precision null,
       name varchar(100) default 'this is a default string value'
);
CREATE INDEX sense_idx ON sample_table (sensor_id);
SELECT * FROM create_hypertable('sample_table', 'time',
       chunk_time_interval => INTERVAL '2 months');
WARNING:  column type "character varying" used for "name" does not follow best practices
 hypertable_id | schema_name |  table_name  | created 
---------------+-------------+--------------+---------
             1 | public      | sample_table | t

\set start_date '2022-01-28 01:09:53.583252+05:30'
-- insert into chunk1
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 1, 21.98, 33.123, 'new row1');
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 2, 17.66, 13.875, 'new row1');
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 3, 21.98, 33.123, 'new row2');
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 8, 21.98, 33.123, 'new row2');
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 4, 21.98, 33.123, 'new row2');
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 5, 0.988, 33.123, 'new row3');
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 6, 4.6554, 47, 'new row3');
\set start_date '2023-03-17 17:51:11.322998+05:30'
-- insert into new chunks
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 12, 121.98, 33.123, 'new row1');
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 12, 117.66, 13.875, 'new row1');
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 13, 121.98, 33.123, 'new row2');
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 9, 121.98, 33.123, 'new row2');
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 14, 121.98, 33.123, 'new row2');
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 15, 10.988, 33.123, 'new row3');
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 16, 14.6554, 47, 'new row3');
-- Non-int, date, timestamp cannot be specified as a min/max ranges for now
-- We could expand to FLOATs, NUMERICs later
\set ON_ERROR_STOP 0
-- Error if you try enabling without enabling the GUC first
SELECT * FROM enable_chunk_skipping('sample_table', 'name');
ERROR:  chunk skipping functionality disabled, enable it by first setting timescaledb.enable_chunk_skipping to on
-- Enable the chunk skipping GUC
SET timescaledb.enable_chunk_skipping to ON;
SELECT * FROM enable_chunk_skipping('sample_table', 'name');
ERROR:  data type "character varying" unsupported for range calculation
SELECT * FROM enable_chunk_skipping('sample_table', NULL);
ERROR:  column name cannot be NULL
SELECT * FROM enable_chunk_skipping(NULL, 'name');
ERROR:  hypertable cannot be NULL
SELECT * FROM enable_chunk_skipping('sample_table1', 'name');
ERROR:  relation "sample_table1" does not exist at character 37
CREATE TABLE plain_table(like sample_table);
SELECT * FROM enable_chunk_skipping('plain_table', 'sensor_id');
ERROR:  table "plain_table" is not a hypertable
DROP TABLE plain_table;
\set ON_ERROR_STOP 1
-- Specify tracking of min/max ranges for a column
SELECT * FROM enable_chunk_skipping('sample_table', 'sensor_id');
 column_stats_id | enabled 
-----------------+---------
               1 | t

-- The above should add an entry with MIN/MAX int64 entries for invalid chunk id
-- to indicate that ranges on this column should be calculated for chunks
SELECT * from _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name |     range_start      |      range_end      | valid 
----+---------------+----------+-------------+----------------------+---------------------+-------
  1 |             1 |          | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  2 |             1 |        1 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  3 |             1 |        2 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t

-- Skipping should work
SELECT * FROM enable_chunk_skipping('sample_table', 'sensor_id', true);
NOTICE:  already enabled for column "sensor_id", skipping
 column_stats_id | enabled 
-----------------+---------
               1 | t

-- A query using a WHERE clause on "sensor_id" column will scan all the chunks
:PREFIX SELECT * FROM sample_table WHERE sensor_id > 9;
--- QUERY PLAN ---
 Append
   ->  Index Scan using _hyper_1_1_chunk_sense_idx on _hyper_1_1_chunk
         Index Cond: (sensor_id > 9)
   ->  Index Scan using _hyper_1_2_chunk_sense_idx on _hyper_1_2_chunk
         Index Cond: (sensor_id > 9)

-- For the purposes of min/max range tracking, a compressed chunk is considered as a
-- completed chunk.
-- enable compression
ALTER TABLE sample_table SET (
	timescaledb.compress,
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_segmentby = 'sensor_id'
);
--
-- compress one chunk
SELECT show_chunks('sample_table') AS "CH_NAME" order by 1 limit 1 \gset
SELECT compress_chunk(:'CH_NAME');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

SELECT id AS "CHUNK_ID" from _timescaledb_catalog.chunk WHERE table_name = '_hyper_1_1_chunk' \gset
-- There should be an entry with min/max range computed for this chunk for this
-- "sensor_id" column.
SELECT * from _timescaledb_catalog.chunk_column_stats where chunk_id = :'CHUNK_ID';
 id | hypertable_id | chunk_id | column_name | range_start | range_end | valid 
----+---------------+----------+-------------+-------------+-----------+-------
  2 |             1 |        1 | sensor_id   |           1 |         9 | t

-- check chunk compression status
SELECT chunk_status
FROM compressed_chunk_info_view
WHERE hypertable_name = 'sample_table' AND chunk_name = :'CH_NAME';
 chunk_status 
--------------
            1

:PREFIX SELECT * FROM sample_table WHERE sensor_id > 9;
--- QUERY PLAN ---
 Index Scan using _hyper_1_2_chunk_sense_idx on _hyper_1_2_chunk
   Index Cond: (sensor_id > 9)

-- do update, this will change the status of the chunk
UPDATE sample_table SET name = 'updated row' WHERE cpu = 21.98 AND temperature = 33.123;
-- check chunk compression status
SELECT chunk_status
FROM compressed_chunk_info_view
WHERE hypertable_name = 'sample_table' AND chunk_name = :'CH_NAME';
 chunk_status 
--------------
            9

-- There should be an entry with "valid" set to false for this chunk
SELECT * from _timescaledb_catalog.chunk_column_stats WHERE chunk_id = :'CHUNK_ID';
 id | hypertable_id | chunk_id | column_name | range_start | range_end | valid 
----+---------------+----------+-------------+-------------+-----------+-------
  2 |             1 |        1 | sensor_id   |           1 |         9 | f

-- A query using a WHERE clause on "sensor_id" column will go back to scanning all the chunks
-- along with an expensive DECOMPRESS on the first chunk
:PREFIX SELECT * FROM sample_table WHERE sensor_id > 9;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: (sensor_id > 9)
   ->  Index Scan using _hyper_1_1_chunk_sense_idx on _hyper_1_1_chunk
         Index Cond: (sensor_id > 9)
   ->  Index Scan using _hyper_1_2_chunk_sense_idx on _hyper_1_2_chunk
         Index Cond: (sensor_id > 9)

-- Remove the index to check the sequential min/max calculation code as well
DROP INDEX sense_idx;
-- recompress the partial chunk
SELECT compress_chunk(:'CH_NAME');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

-- check chunk compression status
SELECT chunk_status
FROM compressed_chunk_info_view
WHERE hypertable_name = 'sample_table' AND chunk_name = :'CH_NAME';
 chunk_status 
--------------
            1

-- The chunk entry should become "valid" again
SELECT min(sensor_id), max(sensor_id) FROM :CH_NAME;
 min | max 
-----+-----
   1 |   8

SELECT * from _timescaledb_catalog.chunk_column_stats WHERE chunk_id = :'CHUNK_ID';
 id | hypertable_id | chunk_id | column_name | range_start | range_end | valid 
----+---------------+----------+-------------+-------------+-----------+-------
  2 |             1 |        1 | sensor_id   |           1 |         9 | t

-- A query using a WHERE clause on "sensor_id" column will scan the proper chunk
-- due to chunk exclusion using min/max ranges calculated above
-- First chunk has [start,end) values of [1,9), here we confirm that its being
-- filtered out when we are not hitting that range.
-- Query ranges outside of chunk stats, should not include first chunk.
:PREFIX SELECT * FROM sample_table WHERE sensor_id < 1;
--- QUERY PLAN ---
 Seq Scan on _hyper_1_2_chunk
   Filter: (sensor_id < 1)

:PREFIX SELECT * FROM sample_table WHERE sensor_id <= 0;
--- QUERY PLAN ---
 Seq Scan on _hyper_1_2_chunk
   Filter: (sensor_id <= 0)

:PREFIX SELECT * FROM sample_table WHERE sensor_id > 8;
--- QUERY PLAN ---
 Seq Scan on _hyper_1_2_chunk
   Filter: (sensor_id > 8)

:PREFIX SELECT * FROM sample_table WHERE sensor_id >= 9;
--- QUERY PLAN ---
 Seq Scan on _hyper_1_2_chunk
   Filter: (sensor_id >= 9)

:PREFIX SELECT * FROM sample_table WHERE sensor_id = 10;
--- QUERY PLAN ---
 Seq Scan on _hyper_1_2_chunk
   Filter: (sensor_id = 10)

-- Query intersecting ranges, should include chunk.
:PREFIX SELECT * FROM sample_table WHERE sensor_id = 7;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: (sensor_id = 7)
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: (sensor_id = 7)

:PREFIX SELECT * FROM sample_table WHERE sensor_id < 2;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: (sensor_id < 2)
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: (sensor_id < 2)

:PREFIX SELECT * FROM sample_table WHERE sensor_id > 3;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: (sensor_id > 3)
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: (sensor_id > 3)

:PREFIX SELECT * FROM sample_table WHERE sensor_id <= 1;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: (sensor_id <= 1)
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: (sensor_id <= 1)

:PREFIX SELECT * FROM sample_table WHERE sensor_id >= 8;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: (sensor_id >= 8)
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: (sensor_id >= 8)

:PREFIX SELECT * FROM sample_table WHERE sensor_id >= 8 AND sensor_id <= 10;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: ((sensor_id >= 8) AND (sensor_id <= 10))
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: ((sensor_id >= 8) AND (sensor_id <= 10))

:PREFIX SELECT * FROM sample_table WHERE sensor_id <= 4 AND sensor_id > -5;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: ((sensor_id <= 4) AND (sensor_id > '-5'::integer))
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: ((sensor_id <= 4) AND (sensor_id > '-5'::integer))

:PREFIX SELECT * FROM sample_table WHERE sensor_id > 6 AND sensor_id < 10;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: ((sensor_id > 6) AND (sensor_id < 10))
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: ((sensor_id > 6) AND (sensor_id < 10))

:PREFIX SELECT * FROM sample_table WHERE sensor_id <= 5 AND sensor_id > -1;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: ((sensor_id <= 5) AND (sensor_id > '-1'::integer))
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: ((sensor_id <= 5) AND (sensor_id > '-1'::integer))

-- Query chunk superset ranges, should include chunk.
:PREFIX SELECT * FROM sample_table WHERE sensor_id > -2;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: (sensor_id > '-2'::integer)
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: (sensor_id > '-2'::integer)

:PREFIX SELECT * FROM sample_table WHERE sensor_id < 11;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: (sensor_id < 11)
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: (sensor_id < 11)

:PREFIX SELECT * FROM sample_table WHERE sensor_id >= 1 AND sensor_id <= 8;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: ((sensor_id >= 1) AND (sensor_id <= 8))
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: ((sensor_id >= 1) AND (sensor_id <= 8))

:PREFIX SELECT * FROM sample_table WHERE sensor_id > 0 AND sensor_id < 9;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: ((sensor_id > 0) AND (sensor_id < 9))
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: ((sensor_id > 0) AND (sensor_id < 9))

-- Query chunk subset ranges, should include chunk.
:PREFIX SELECT * FROM sample_table WHERE sensor_id > 2 AND sensor_id < 7;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: ((sensor_id > 2) AND (sensor_id < 7))
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: ((sensor_id > 2) AND (sensor_id < 7))

:PREFIX SELECT * FROM sample_table WHERE sensor_id >= 3 AND sensor_id <= 6;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: ((sensor_id >= 3) AND (sensor_id <= 6))
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: ((sensor_id >= 3) AND (sensor_id <= 6))

:PREFIX SELECT * FROM sample_table WHERE sensor_id > 4 AND sensor_id <= 5;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: ((sensor_id > 4) AND (sensor_id <= 5))
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: ((sensor_id > 4) AND (sensor_id <= 5))

:PREFIX SELECT * FROM sample_table WHERE sensor_id >= 6 AND sensor_id < 8;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: ((sensor_id >= 6) AND (sensor_id < 8))
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: ((sensor_id >= 6) AND (sensor_id < 8))

-- Disabling the GUC will prevent plan time chunk skipping
RESET timescaledb.enable_chunk_skipping;
:PREFIX SELECT * FROM sample_table WHERE sensor_id < 1;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: (sensor_id < 1)
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: (sensor_id < 1)

SET timescaledb.enable_chunk_skipping to ON;
-- Query ranges with OR clause will not work properly due to the way
-- hypertable expansion works as of now. Once we fix that, it should
-- fix the issue here too.
-- Should not include but it does.
:PREFIX SELECT * FROM sample_table WHERE sensor_id < 1 OR sensor_id > 8;
--- QUERY PLAN ---
 Append
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Seq Scan on compress_hyper_2_3_chunk
               Filter: ((sensor_id < 1) OR (sensor_id > 8))
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: ((sensor_id < 1) OR (sensor_id > 8))

-- Executor startup time exclusion will also use these ranges appropriately
:PREFIX UPDATE sample_table set sensor_id = 10 WHERE sensor_id > length(substring(version(),1,9));
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   ->  Update on sample_table
         Update on _hyper_1_1_chunk sample_table
         Update on _hyper_1_2_chunk sample_table_1
         ->  Result
               ->  Custom Scan (ChunkAppend) on sample_table
                     Chunks excluded during startup: 1
                     ->  Seq Scan on _hyper_1_2_chunk sample_table_1
                           Filter: (sensor_id > length("substring"(version(), 1, 9)))

:PREFIX DELETE FROM sample_table  WHERE sensor_id > length(substring(version(),1,10));
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   ->  Delete on sample_table
         Delete on _hyper_1_1_chunk sample_table
         Delete on _hyper_1_2_chunk sample_table_1
         ->  Custom Scan (ChunkAppend) on sample_table
               Chunks excluded during startup: 1
               ->  Seq Scan on _hyper_1_2_chunk sample_table_1
                     Filter: (sensor_id > length("substring"(version(), 1, 10)))

:PREFIX SELECT * FROM sample_table  WHERE sensor_id > length(substring(version(),1,11));
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on sample_table
   Chunks excluded during startup: 1
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: (sensor_id > length("substring"(version(), 1, 11)))

-- Scan both chunks
:PREFIX SELECT * FROM sample_table  WHERE sensor_id > length(substring(version(),1,6));
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on sample_table
   Chunks excluded during startup: 0
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         ->  Index Scan using compress_hyper_2_3_chunk_sensor_id__ts_meta_v2_first_time___idx on compress_hyper_2_3_chunk
               Index Cond: (sensor_id > length("substring"(version(), 1, 6)))
   ->  Seq Scan on _hyper_1_2_chunk
         Filter: (sensor_id > length("substring"(version(), 1, 6)))

-- Disabling the GUC will prevent execution time chunk skipping
RESET timescaledb.enable_chunk_skipping;
show timescaledb.enable_chunk_skipping;
 timescaledb.enable_chunk_skipping 
-----------------------------------
 off

-- Manually trigger a clearing of the hypertable cache so that
-- the entry for sample_table will be created new without a valid
-- range_space when chunk skipping is off.
--
-- This triggers a bug where the Hypertable entry is cached without
-- a range space entry even though chunk skipping is reenabled. The result is
-- that no chunk_column_stats entry is created for new chunks.
SELECT clear_hypertable_cache();
 clear_hypertable_cache 
------------------------
 

:PREFIX UPDATE sample_table set sensor_id = 10 WHERE sensor_id > length(substring(version(),1,9));
--- QUERY PLAN ---
 Custom Scan (ModifyHypertable)
   ->  Update on sample_table
         Update on _hyper_1_1_chunk sample_table_1
         Update on _hyper_1_2_chunk sample_table_2
         ->  Result
               ->  Custom Scan (ChunkAppend) on sample_table
                     Chunks excluded during startup: 0
                     ->  Seq Scan on _hyper_1_1_chunk sample_table_1
                           Filter: (sensor_id > length("substring"(version(), 1, 9)))
                     ->  Seq Scan on _hyper_1_2_chunk sample_table_2
                           Filter: (sensor_id > length("substring"(version(), 1, 9)))

SET timescaledb.enable_chunk_skipping to ON;
-- IN/ANY uses bounding range [min, max] for chunk exclusion
:PREFIX SELECT * FROM sample_table WHERE sensor_id IN (9, 10, 11);
--- QUERY PLAN ---
 Seq Scan on _hyper_1_2_chunk
   Filter: (sensor_id = ANY ('{9,10,11}'::integer[]))

:PREFIX SELECT * FROM sample_table WHERE sensor_id = ANY(ARRAY[9, 10, 11]);
--- QUERY PLAN ---
 Seq Scan on _hyper_1_2_chunk
   Filter: (sensor_id = ANY ('{9,10,11}'::integer[]))

-- Newly added chunks should also have MIN/MAX entry
\set start_date '2024-01-28 01:09:51.583252+05:30'
INSERT INTO sample_table VALUES (:'start_date'::timestamptz, 1, 9, 78.999, 'new row4');
SELECT * from _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name |     range_start      |      range_end      | valid 
----+---------------+----------+-------------+----------------------+---------------------+-------
  1 |             1 |          | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  2 |             1 |        1 | sensor_id   |                    1 |                   9 | t
  3 |             1 |        2 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  4 |             1 |        4 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t

-- Check that a RENAME COLUMN works ok
ALTER TABLE sample_table RENAME COLUMN sensor_id TO sense_id;
-- use the disable_chunk_skipping API to remove the min/max range entries
SELECT * from disable_chunk_skipping('sample_table', 'sense_id');
 hypertable_id | column_name | disabled 
---------------+-------------+----------
             1 | sense_id    | t

SELECT * from _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name | range_start | range_end | valid 
----+---------------+----------+-------------+-------------+-----------+-------

SELECT * from disable_chunk_skipping('sample_table', 'sense_id', true);
NOTICE:  statistics not enabled for column "sense_id", skipping
 hypertable_id | column_name | disabled 
---------------+-------------+----------
             1 | sense_id    | f

ALTER TABLE sample_table RENAME COLUMN sense_id TO sensor_id;
\set ON_ERROR_STOP 0
SELECT * from disable_chunk_skipping('sample_table', 'sensor_id');
ERROR:  statistics not enabled for column "sensor_id"
SELECT * from disable_chunk_skipping('sample_table', NULL);
ERROR:  column name cannot be NULL
SELECT * from disable_chunk_skipping(NULL, 'sensor_id');
ERROR:  hypertable cannot be NULL
SELECT * from disable_chunk_skipping('sample_table1', 'sensor_id');
ERROR:  relation "sample_table1" does not exist at character 38
-- should only work on columns that have been enabled for tracking
SELECT * from disable_chunk_skipping('sample_table', 'time');
ERROR:  statistics not enabled for column "time"
SELECT * from disable_chunk_skipping('sample_table', 'cpu');
ERROR:  statistics not enabled for column "cpu"
\set ON_ERROR_STOP 1
SELECT * FROM enable_chunk_skipping('sample_table', 'sensor_id');
 column_stats_id | enabled 
-----------------+---------
               5 | t

-- Chunk was already compressed before we enabled stats. It will
-- point to min/max entries till the ranges get refreshed later.
SELECT decompress_chunk(:'CH_NAME');
            decompress_chunk            
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

SELECT * from _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name |     range_start      |      range_end      | valid 
----+---------------+----------+-------------+----------------------+---------------------+-------
  5 |             1 |          | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  6 |             1 |        2 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  7 |             1 |        1 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  8 |             1 |        4 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t

-- Compressing a chunk again should calculate proper ranges
SELECT compress_chunk(:'CH_NAME');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

SELECT * from _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name |     range_start      |      range_end      | valid 
----+---------------+----------+-------------+----------------------+---------------------+-------
  5 |             1 |          | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  6 |             1 |        2 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  7 |             1 |        1 | sensor_id   |                    1 |                   9 | t
  8 |             1 |        4 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t

SELECT decompress_chunk(:'CH_NAME');
            decompress_chunk            
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

-- Entry should be reset for this chunk now
SELECT * from _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name |     range_start      |      range_end      | valid 
----+---------------+----------+-------------+----------------------+---------------------+-------
  5 |             1 |          | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  6 |             1 |        2 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  7 |             1 |        1 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  8 |             1 |        4 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t

-- Check that truncate resets the entry in the catalog
SELECT compress_chunk(:'CH_NAME');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

SELECT * from _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name |     range_start      |      range_end      | valid 
----+---------------+----------+-------------+----------------------+---------------------+-------
  5 |             1 |          | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  6 |             1 |        2 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  7 |             1 |        1 | sensor_id   |                    1 |                   9 | t
  8 |             1 |        4 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t

TRUNCATE :CH_NAME;
SELECT * from _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name |     range_start      |      range_end      | valid 
----+---------------+----------+-------------+----------------------+---------------------+-------
  5 |             1 |          | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  6 |             1 |        2 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  7 |             1 |        1 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  8 |             1 |        4 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t

-- Check that drop chunk also removes entries from the catalog
SELECT drop_chunks('sample_table', older_than => '2022-02-28');
              drop_chunks               
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

-- Entry should be removed for this chunk now
SELECT * from _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name |     range_start      |      range_end      | valid 
----+---------------+----------+-------------+----------------------+---------------------+-------
  5 |             1 |          | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  6 |             1 |        2 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
  8 |             1 |        4 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t

-- disable compression to allow dropping of the column
ALTER TABLE sample_table SET (
	timescaledb.compress = FALSE
);
-- Check that a DROP COLUMN removes entries from catalogs as well
ALTER TABLE sample_table DROP COLUMN sensor_id;
SELECT * from _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name | range_start | range_end | valid 
----+---------------+----------+-------------+-------------+-----------+-------

DROP TABLE sample_table;
SELECT * from _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name | range_start | range_end | valid 
----+---------------+----------+-------------+-------------+-----------+-------

-- Check that empty hypertables can have enable_chunk_skipping and
-- that new chunks get entries in the catalog as they get added
CREATE TABLE sample_table1 (
       time TIMESTAMP WITH TIME ZONE NOT NULL,
       sensor_id INTEGER NOT NULL,
       cpu double precision null,
       temperature double precision null,
       name varchar(100) default 'this is a default string value'
);
CREATE INDEX sense_idx ON sample_table1 (sensor_id);
SELECT * FROM create_hypertable('sample_table1', 'time',
       chunk_time_interval => INTERVAL '2 months');
WARNING:  column type "character varying" used for "name" does not follow best practices
 hypertable_id | schema_name |  table_name   | created 
---------------+-------------+---------------+---------
             3 | public      | sample_table1 | t

SELECT * FROM enable_chunk_skipping('sample_table1', 'sensor_id');
 column_stats_id | enabled 
-----------------+---------
               9 | t

SELECT * from _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name |     range_start      |      range_end      | valid 
----+---------------+----------+-------------+----------------------+---------------------+-------
  9 |             3 |          | sensor_id   | -9223372036854775808 | 9223372036854775807 | t

\set start_date '2023-03-17 17:51:11.322998+05:30'
-- insert into new chunks
INSERT INTO sample_table1 VALUES (:'start_date'::timestamptz, 12, 21, 33.123, 'new row1');
SELECT * from _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name |     range_start      |      range_end      | valid 
----+---------------+----------+-------------+----------------------+---------------------+-------
  9 |             3 |          | sensor_id   | -9223372036854775808 | 9223372036854775807 | t
 10 |             3 |        7 | sensor_id   | -9223372036854775808 | 9223372036854775807 | t

-- Check that ALTER TYPE for a column on which stats are enabled only works
-- in a few sub types
ALTER TABLE sample_table1 ALTER COLUMN sensor_id TYPE BIGINT;
\set ON_ERROR_STOP 0
ALTER TABLE sample_table1 ALTER COLUMN sensor_id TYPE TIMESTAMPTZ;
ERROR:  column "sensor_id" cannot be cast automatically to type timestamp with time zone
ALTER TABLE sample_table1 ALTER COLUMN sensor_id TYPE TEXT;
ERROR:  data type "text" unsupported for statistics calculation
\set ON_ERROR_STOP 1
SELECT * FROM disable_chunk_skipping('sample_table1', 'sensor_id');
 hypertable_id | column_name | disabled 
---------------+-------------+----------
             3 | sensor_id   | t

ALTER TABLE sample_table1 ALTER COLUMN sensor_id TYPE TEXT;
-- Check that column mapping between hypertable and chunk doesn't break when
-- hypertable has dropped columns
-- See: https://github.com/timescale/timescaledb/issues/7932
CREATE TABLE sample_table (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER,
    cpu NUMERIC,
    temperature BIGINT);
CREATE INDEX ON sample_table (temperature);
SELECT create_hypertable('sample_table', 'time', chunk_time_interval=>'7 days'::interval);
     create_hypertable     
---------------------------
 (4,public,sample_table,t)

ALTER TABLE sample_table DROP COLUMN sensor_id;
INSERT INTO sample_table VALUES
    (now(), 1, 366),
    (now(), 2, 501);
ALTER TABLE sample_table SET (timescaledb.compress, timescaledb.compress_orderby='time', timescaledb.compress_segmentby='temperature');
set timescaledb.enable_chunk_skipping = on;
SELECT enable_chunk_skipping('sample_table', 'temperature');
 enable_chunk_skipping 
-----------------------
 (11,t)

SELECT show_chunks('sample_table') AS "CH_NAME" order by 1 limit 1 \gset
SELECT compress_chunk(:'CH_NAME');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_4_8_chunk

SELECT * FROM _timescaledb_catalog.chunk_column_stats ORDER BY 1;
 id | hypertable_id | chunk_id | column_name |     range_start      |      range_end      | valid 
----+---------------+----------+-------------+----------------------+---------------------+-------
 11 |             4 |          | temperature | -9223372036854775808 | 9223372036854775807 | t
 12 |             4 |        8 | temperature |                  366 |                 502 | t

-- Check min/max ranges for partial chunks with segmentby columns get recalculated correctly by seementwise recompression
CREATE TABLE chunk_skipping(time timestamptz,device text, updated_at timestamptz)
WITH (tsdb.hypertable, tsdb.partition_column='time',tsdb.segmentby='device');
SELECT enable_chunk_skipping('chunk_skipping', 'updated_at');
 enable_chunk_skipping 
-----------------------
 (13,t)

INSERT INTO chunk_skipping SELECT '2025-01-01', 'd1', '2025-01-01';
SELECT compress_chunk(show_chunks('chunk_skipping'));
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_6_10_chunk

SELECT * from chunk_skipping where updated_at < '2026-01-01';
             time             | device |          updated_at          
------------------------------+--------+------------------------------
 Wed Jan 01 00:00:00 2025 PST | d1     | Wed Jan 01 00:00:00 2025 PST

INSERT INTO chunk_skipping SELECT '2025-01-01', 'd2', '2026-01-01';
SELECT compress_chunk(show_chunks('chunk_skipping'));
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_6_10_chunk

SELECT * from chunk_skipping where updated_at < '2026-01-01';
             time             | device |          updated_at          
------------------------------+--------+------------------------------
 Wed Jan 01 00:00:00 2025 PST | d1     | Wed Jan 01 00:00:00 2025 PST

-- Check chunk skipping with dropped columns
CREATE TABLE attno (id int, dropped_col text, start timestamptz) WITH (tsdb.hypertable, tsdb.segmentby='id');
NOTICE:  using column "start" as partitioning column
SET timescaledb.enable_chunk_skipping = true;
SELECT * FROM enable_chunk_skipping('attno', 'start');
 column_stats_id | enabled 
-----------------+---------
              15 | t

ALTER TABLE attno DROP COLUMN dropped_col;
INSERT INTO attno SELECT i % 5, '2026-01-01'::timestamptz + format('%s minutes', i)::interval FROM generate_series(1, 10) i;
SELECT count(compress_chunk(ch)) FROM show_chunks('attno') ch;
 count 
-------
     1

SELECT * FROM attno WHERE id = 1 AND start > '2025-02-02T11:53:28Z'::date ORDER BY start;
 id |            start             
----+------------------------------
  1 | Thu Jan 01 00:01:00 2026 PST
  1 | Thu Jan 01 00:06:00 2026 PST

-- Test for wrong allocation size of chunk stats storage
CREATE TABLE sensor_readings(measured_at timestamptz NOT NULL, temperature int, humidity int);
SELECT create_hypertable('sensor_readings', 'measured_at');
       create_hypertable       
-------------------------------
 (10,public,sensor_readings,t)

SELECT enable_chunk_skipping('sensor_readings', 'temperature');
 enable_chunk_skipping 
-----------------------
 (17,t)

RESET timescaledb.enable_chunk_skipping;
RESET timescaledb.enable_chunk_skipping;