compression.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 timescaledb.enable_transparent_decompression to OFF;
\set PREFIX 'EXPLAIN (analyze, verbose, costs off, timing off, summary off)'
\ir include/rand_generator.sql
-- 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.
--------------------------
-- cheap rand generator --
--------------------------
create table rand_minstd_state(i bigint);
create function rand_minstd_advance(bigint) returns bigint
language sql immutable as
$$
	select (16807 * $1) % 2147483647
$$;
create function gen_rand_minstd() returns bigint
language sql security definer as
$$
	update rand_minstd_state set i = rand_minstd_advance(i) returning i
$$;
-- seed the random num generator
insert into rand_minstd_state values (321);
--test_collation ---
--basic test with count
create table foo (a integer, b integer, c integer, d integer);
select table_name from create_hypertable('foo', 'a', chunk_time_interval=> 10);
NOTICE:  adding not-null constraint to column "a"
 table_name 
------------
 foo
(1 row)

create unique index foo_uniq ON foo (a, b);
--note that the "d" order by column is all NULL
insert into foo values( 3 , 16 , 20, NULL);
insert into foo values( 10 , 10 , 20, NULL);
insert into foo values( 20 , 11 , 20, NULL);
insert into foo values( 30 , 12 , 20, NULL);
analyze foo;
-- check that approximate_row_count works with a regular table
SELECT approximate_row_count('foo');
 approximate_row_count 
-----------------------
                     4
(1 row)

SELECT count(*) from foo;
 count 
-------
     4
(1 row)

alter table foo set (timescaledb.compress, timescaledb.compress_segmentby = 'a,b', timescaledb.compress_orderby = 'c desc, d asc nulls last');
--test self-refencing updates
SET timescaledb.enable_transparent_decompression to ON;
update foo set c = 40
where  a = (SELECT max(a) FROM foo);
SET timescaledb.enable_transparent_decompression to OFF;
select id, schema_name, table_name, compression_state as compressed, compressed_hypertable_id from
_timescaledb_catalog.hypertable order by id;
 id |      schema_name      |        table_name        | compressed | compressed_hypertable_id 
----+-----------------------+--------------------------+------------+--------------------------
  1 | public                | foo                      |          1 |                        2
  2 | _timescaledb_internal | _compressed_hypertable_2 |          2 |                         
(2 rows)

select * from _timescaledb_catalog.hypertable_compression order by hypertable_id, attname;
 hypertable_id | attname | compression_algorithm_id | segmentby_column_index | orderby_column_index | orderby_asc | orderby_nullsfirst 
---------------+---------+--------------------------+------------------------+----------------------+-------------+--------------------
             1 | a       |                        0 |                      1 |                      |             | 
             1 | b       |                        0 |                      2 |                      |             | 
             1 | c       |                        4 |                        |                    1 | f           | t
             1 | d       |                        4 |                        |                    2 | t           | f
(4 rows)

select * from timescaledb_information.compression_settings ORDER BY hypertable_name;
 hypertable_schema | hypertable_name | attname | segmentby_column_index | orderby_column_index | orderby_asc | orderby_nullsfirst 
-------------------+-----------------+---------+------------------------+----------------------+-------------+--------------------
 public            | foo             | a       |                      1 |                      |             | 
 public            | foo             | b       |                      2 |                      |             | 
 public            | foo             | c       |                        |                    1 | f           | t
 public            | foo             | d       |                        |                    2 | t           | f
(4 rows)

-- TEST2 compress-chunk for the chunks created earlier --
select compress_chunk( '_timescaledb_internal._hyper_1_2_chunk');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_2_chunk
(1 row)

select tgname , tgtype, tgenabled , relname
from pg_trigger t, pg_class rel
where t.tgrelid = rel.oid and rel.relname like '_hyper_1_2_chunk' order by tgname;
 tgname | tgtype | tgenabled | relname 
--------+--------+-----------+---------
(0 rows)

\x
select * from chunk_compression_stats('foo')
order by chunk_name limit 2;
-[ RECORD 1 ]------------------+----------------------
chunk_schema                   | _timescaledb_internal
chunk_name                     | _hyper_1_1_chunk
compression_status             | Uncompressed
before_compression_table_bytes | 
before_compression_index_bytes | 
before_compression_toast_bytes | 
before_compression_total_bytes | 
after_compression_table_bytes  | 
after_compression_index_bytes  | 
after_compression_toast_bytes  | 
after_compression_total_bytes  | 
node_name                      | 
-[ RECORD 2 ]------------------+----------------------
chunk_schema                   | _timescaledb_internal
chunk_name                     | _hyper_1_2_chunk
compression_status             | Compressed
before_compression_table_bytes | 8192
before_compression_index_bytes | 32768
before_compression_toast_bytes | 0
before_compression_total_bytes | 40960
after_compression_table_bytes  | 16384
after_compression_index_bytes  | 16384
after_compression_toast_bytes  | 8192
after_compression_total_bytes  | 40960
node_name                      | 

\x
select compress_chunk( '_timescaledb_internal._hyper_1_1_chunk');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk
(1 row)

\x
select * from _timescaledb_catalog.compression_chunk_size
order by chunk_id;
-[ RECORD 1 ]--------------+------
chunk_id                   | 1
compressed_chunk_id        | 6
uncompressed_heap_size     | 8192
uncompressed_toast_size    | 0
uncompressed_index_size    | 32768
compressed_heap_size       | 16384
compressed_toast_size      | 8192
compressed_index_size      | 16384
numrows_pre_compression    | 1
numrows_post_compression   | 1
numrows_frozen_immediately | 1
-[ RECORD 2 ]--------------+------
chunk_id                   | 2
compressed_chunk_id        | 5
uncompressed_heap_size     | 8192
uncompressed_toast_size    | 0
uncompressed_index_size    | 32768
compressed_heap_size       | 16384
compressed_toast_size      | 8192
compressed_index_size      | 16384
numrows_pre_compression    | 1
numrows_post_compression   | 1
numrows_frozen_immediately | 1

\x
select  ch1.id, ch1.schema_name, ch1.table_name ,  ch2.table_name as compress_table
from
_timescaledb_catalog.chunk ch1, _timescaledb_catalog.chunk ch2
where ch1.compressed_chunk_id = ch2.id;
 id |      schema_name      |    table_name    |      compress_table      
----+-----------------------+------------------+--------------------------
  2 | _timescaledb_internal | _hyper_1_2_chunk | compress_hyper_2_5_chunk
  1 | _timescaledb_internal | _hyper_1_1_chunk | compress_hyper_2_6_chunk
(2 rows)

\set ON_ERROR_STOP 0
--cannot recompress the chunk the second time around
select compress_chunk( '_timescaledb_internal._hyper_1_2_chunk');
ERROR:  chunk "_hyper_1_2_chunk" is already compressed
--TEST2a try DML on a compressed chunk
BEGIN;
insert into foo values( 11 , 10 , 20, 120);
ROLLBACK;
update foo set b =20 where a = 10;
delete from foo where a = 10;
--TEST2b try complex DML on compressed chunk
create table foo_join ( a integer, newval integer);
select table_name from create_hypertable('foo_join', 'a', chunk_time_interval=> 10);
NOTICE:  adding not-null constraint to column "a"
 table_name 
------------
 foo_join
(1 row)

insert into foo_join select generate_series(0,40, 10), 111;
create table foo_join2 ( a integer, newval integer);
select table_name from create_hypertable('foo_join2', 'a', chunk_time_interval=> 10);
NOTICE:  adding not-null constraint to column "a"
 table_name 
------------
 foo_join2
(1 row)

insert into foo_join select generate_series(0,40, 10), 222;
update foo
set b = newval
from foo_join where foo.a = foo_join.a;
update foo
set b = newval
from foo_join where foo.a = foo_join.a and foo_join.a > 10;
--here the chunk gets excluded , so succeeds --
update foo
set b = newval
from foo_join where foo.a = foo_join.a and foo.a > 20;
update foo
set b = (select f1.newval from foo_join f1 left join lateral (select newval as newval2 from  foo_join2 f2 where f1.a= f2.a ) subq on true limit 1);
--upsert test --
insert into foo values(10, 12, 12, 12)
on conflict( a, b)
do update set b = excluded.b;
SELECT * from foo ORDER BY a,b;
 a  |  b  | c  | d  
----+-----+----+----
  3 | 111 | 20 |   
 10 |  12 | 12 | 12
 20 | 111 | 20 |   
 30 | 111 | 40 |   
(4 rows)

--TEST2c Do DML directly on the chunk.
insert into _timescaledb_internal._hyper_1_2_chunk values(10, 12, 12, 12)
on conflict( a, b)
do update set b = excluded.b + 12;
SELECT * from foo ORDER BY a,b;
 a  |  b  | c  | d  
----+-----+----+----
  3 | 111 | 20 |   
 10 |  24 | 12 | 12
 20 | 111 | 20 |   
 30 | 111 | 40 |   
(4 rows)

update _timescaledb_internal._hyper_1_2_chunk
set b = 12;
delete from _timescaledb_internal._hyper_1_2_chunk;
--TEST2d decompress the chunk and try DML
select decompress_chunk( '_timescaledb_internal._hyper_1_2_chunk');
            decompress_chunk            
----------------------------------------
 _timescaledb_internal._hyper_1_2_chunk
(1 row)

insert into foo values( 11 , 10 , 20, 120);
update foo set b =20 where a = 10;
select * from _timescaledb_internal._hyper_1_2_chunk order by a,b;
 a  | b  | c  |  d  
----+----+----+-----
 11 | 10 | 20 | 120
(1 row)

delete from foo where a = 10;
select * from _timescaledb_internal._hyper_1_2_chunk order by a,b;
 a  | b  | c  |  d  
----+----+----+-----
 11 | 10 | 20 | 120
(1 row)

-- TEST3 check if compress data from views is accurate
CREATE TABLE conditions (
      time        TIMESTAMPTZ       NOT NULL,
      location    TEXT              NOT NULL,
      location2    char(10)              NOT NULL,
      temperature DOUBLE PRECISION  NULL,
      humidity    DOUBLE PRECISION  NULL
    );
select create_hypertable( 'conditions', 'time', chunk_time_interval=> '31days'::interval);
    create_hypertable    
-------------------------
 (5,public,conditions,t)
(1 row)

alter table conditions set (timescaledb.compress, timescaledb.compress_segmentby = 'location', timescaledb.compress_orderby = 'time');
insert into conditions
select generate_series('2018-12-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day'), 'POR', 'klick', 55, 75;
insert into conditions
select generate_series('2018-12-01 00:00'::timestamp, '2018-12-31 00:00'::timestamp, '1 day'), 'NYC', 'klick', 55, 75;
select hypertable_id, attname, compression_algorithm_id , al.name
from _timescaledb_catalog.hypertable_compression hc,
     _timescaledb_catalog.hypertable ht,
      _timescaledb_catalog.compression_algorithm al
where ht.id = hc.hypertable_id and ht.table_name like 'conditions' and al.id = hc.compression_algorithm_id
ORDER BY hypertable_id, attname;
 hypertable_id |   attname   | compression_algorithm_id |               name               
---------------+-------------+--------------------------+----------------------------------
             5 | humidity    |                        3 | COMPRESSION_ALGORITHM_GORILLA
             5 | location    |                        0 | COMPRESSION_ALGORITHM_NONE
             5 | location2   |                        2 | COMPRESSION_ALGORITHM_DICTIONARY
             5 | temperature |                        3 | COMPRESSION_ALGORITHM_GORILLA
             5 | time        |                        4 | COMPRESSION_ALGORITHM_DELTADELTA
(5 rows)

select attname, attstorage, typname from pg_attribute at, pg_class cl , pg_type ty
where cl.oid = at.attrelid and  at.attnum > 0
and cl.relname = '_compressed_hypertable_4'
and atttypid = ty.oid
order by at.attnum;
 attname | attstorage | typname 
---------+------------+---------
(0 rows)

SELECT ch1.schema_name|| '.' || ch1.table_name as "CHUNK_NAME", ch1.id "CHUNK_ID"
FROM _timescaledb_catalog.chunk ch1, _timescaledb_catalog.hypertable ht where ch1.hypertable_id = ht.id and ht.table_name like 'conditions'
ORDER BY ch1.id
LIMIT 1 \gset
SELECT count(*) from :CHUNK_NAME;
 count 
-------
    42
(1 row)

SELECT count(*) as "ORIGINAL_CHUNK_COUNT" from :CHUNK_NAME \gset
select tableoid::regclass, count(*) from conditions group by tableoid order by tableoid;
                tableoid                 | count 
-----------------------------------------+-------
 _timescaledb_internal._hyper_5_12_chunk |    42
 _timescaledb_internal._hyper_5_13_chunk |    20
(2 rows)

SELECT compress_chunk(ch) FROM show_chunks('conditions') ch LIMIT 1;
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_5_12_chunk
(1 row)

--test that only one chunk was affected
--note tables with 0 rows will not show up in here.
select tableoid::regclass, count(*) from conditions group by tableoid order by tableoid;
                tableoid                 | count 
-----------------------------------------+-------
 _timescaledb_internal._hyper_5_13_chunk |    20
(1 row)

SELECT compress_chunk(ch, true) FROM show_chunks('conditions') ch ORDER BY ch::text DESC LIMIT 1;
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_5_13_chunk
(1 row)

select tableoid::regclass, count(*) from conditions group by tableoid order by tableoid;
 tableoid | count 
----------+-------
(0 rows)

select  compressed.schema_name|| '.' || compressed.table_name as "COMPRESSED_CHUNK_NAME"
from _timescaledb_catalog.chunk uncompressed, _timescaledb_catalog.chunk compressed
where uncompressed.compressed_chunk_id = compressed.id AND uncompressed.id = :'CHUNK_ID' \gset
SELECT count(*) from :CHUNK_NAME;
 count 
-------
     0
(1 row)

SELECT count(*) from :COMPRESSED_CHUNK_NAME;
 count 
-------
     2
(1 row)

SELECT sum(_ts_meta_count) from :COMPRESSED_CHUNK_NAME;
 sum 
-----
  42
(1 row)

SELECT location, _ts_meta_sequence_num from :COMPRESSED_CHUNK_NAME ORDER BY 1,2;
 location | _ts_meta_sequence_num 
----------+-----------------------
 NYC      |                    10
 POR      |                    10
(2 rows)

\x
SELECT chunk_id, numrows_pre_compression, numrows_post_compression
FROM _timescaledb_catalog.chunk srcch,
      _timescaledb_catalog.compression_chunk_size map,
     _timescaledb_catalog.hypertable srcht
WHERE map.chunk_id = srcch.id and srcht.id = srcch.hypertable_id
       and srcht.table_name like 'conditions'
order by chunk_id;
-[ RECORD 1 ]------------+---
chunk_id                 | 12
numrows_pre_compression  | 42
numrows_post_compression | 2
-[ RECORD 2 ]------------+---
chunk_id                 | 13
numrows_pre_compression  | 20
numrows_post_compression | 2

select * from chunk_compression_stats('conditions')
order by chunk_name;
-[ RECORD 1 ]------------------+----------------------
chunk_schema                   | _timescaledb_internal
chunk_name                     | _hyper_5_12_chunk
compression_status             | Compressed
before_compression_table_bytes | 8192
before_compression_index_bytes | 16384
before_compression_toast_bytes | 8192
before_compression_total_bytes | 32768
after_compression_table_bytes  | 16384
after_compression_index_bytes  | 16384
after_compression_toast_bytes  | 8192
after_compression_total_bytes  | 40960
node_name                      | 
-[ RECORD 2 ]------------------+----------------------
chunk_schema                   | _timescaledb_internal
chunk_name                     | _hyper_5_13_chunk
compression_status             | Compressed
before_compression_table_bytes | 8192
before_compression_index_bytes | 16384
before_compression_toast_bytes | 8192
before_compression_total_bytes | 32768
after_compression_table_bytes  | 16384
after_compression_index_bytes  | 16384
after_compression_toast_bytes  | 8192
after_compression_total_bytes  | 40960
node_name                      | 

select * from hypertable_compression_stats('foo');
-[ RECORD 1 ]------------------+------
total_chunks                   | 4
number_compressed_chunks       | 1
before_compression_table_bytes | 8192
before_compression_index_bytes | 32768
before_compression_toast_bytes | 0
before_compression_total_bytes | 40960
after_compression_table_bytes  | 16384
after_compression_index_bytes  | 16384
after_compression_toast_bytes  | 8192
after_compression_total_bytes  | 40960
node_name                      | 

select * from hypertable_compression_stats('conditions');
-[ RECORD 1 ]------------------+------
total_chunks                   | 2
number_compressed_chunks       | 2
before_compression_table_bytes | 16384
before_compression_index_bytes | 32768
before_compression_toast_bytes | 16384
before_compression_total_bytes | 65536
after_compression_table_bytes  | 32768
after_compression_index_bytes  | 32768
after_compression_toast_bytes  | 16384
after_compression_total_bytes  | 81920
node_name                      | 

vacuum full foo;
vacuum full conditions;
-- After vacuum, table_bytes is 0, but any associated index/toast storage is not
-- completely reclaimed. Sets it at 8K (page size). So a chunk which has
-- been compressed still incurs an overhead of n * 8KB (for every index + toast table) storage on the original uncompressed chunk.
select pg_size_pretty(table_bytes), pg_size_pretty(index_bytes),
pg_size_pretty(toast_bytes), pg_size_pretty(total_bytes)
from hypertable_detailed_size('conditions');
-[ RECORD 1 ]--+-------
pg_size_pretty | 16 kB
pg_size_pretty | 56 kB
pg_size_pretty | 40 kB
pg_size_pretty | 112 kB

select * from timescaledb_information.hypertables
where hypertable_name like 'foo' or hypertable_name like 'conditions'
order by hypertable_name;
-[ RECORD 1 ]-------+------------------
hypertable_schema   | public
hypertable_name     | conditions
owner               | default_perm_user
num_dimensions      | 1
num_chunks          | 2
compression_enabled | t
is_distributed      | f
replication_factor  | 
data_nodes          | 
tablespaces         | 
-[ RECORD 2 ]-------+------------------
hypertable_schema   | public
hypertable_name     | foo
owner               | default_perm_user
num_dimensions      | 1
num_chunks          | 4
compression_enabled | t
is_distributed      | f
replication_factor  | 
data_nodes          | 
tablespaces         | 

\x
SELECT count(decompress_chunk(ch)) FROM show_chunks('conditions') ch;
 count 
-------
     2
(1 row)

SELECT count(*), count(*) = :'ORIGINAL_CHUNK_COUNT' from :CHUNK_NAME;
 count | ?column? 
-------+----------
    42 | t
(1 row)

--check that the compressed chunk is dropped
\set ON_ERROR_STOP 0
SELECT count(*) from :COMPRESSED_CHUNK_NAME;
ERROR:  relation "_timescaledb_internal.compress_hyper_6_14_chunk" does not exist at character 22
\set ON_ERROR_STOP 1
--size information is gone too
select count(*)
FROM _timescaledb_catalog.chunk ch1, _timescaledb_catalog.hypertable ht,
_timescaledb_catalog.compression_chunk_size map
where ch1.hypertable_id = ht.id and ht.table_name like 'conditions'
and map.chunk_id = ch1.id;
 count 
-------
     0
(1 row)

--make sure  compressed_chunk_id  is reset to NULL
select ch1.compressed_chunk_id IS NULL
FROM _timescaledb_catalog.chunk ch1, _timescaledb_catalog.hypertable ht where ch1.hypertable_id = ht.id and ht.table_name like 'conditions';
 ?column? 
----------
 t
 t
(2 rows)

-- test plans get invalidated when chunks get compressed
SET timescaledb.enable_transparent_decompression TO ON;
CREATE TABLE plan_inval(time timestamptz, device_id int);
SELECT create_hypertable('plan_inval','time');
NOTICE:  adding not-null constraint to column "time"
    create_hypertable    
-------------------------
 (7,public,plan_inval,t)
(1 row)

ALTER TABLE plan_inval SET (timescaledb.compress,timescaledb.compress_orderby='time desc');
-- create 2 chunks
INSERT INTO plan_inval SELECT * FROM (VALUES ('2000-01-01'::timestamptz,1), ('2000-01-07'::timestamptz,1)) v(time,device_id);
SET max_parallel_workers_per_gather to 0;
-- Disable hash aggregation to get a deterministic test output
SET enable_hashagg = OFF;
PREPARE prep_plan AS SELECT count(*) FROM plan_inval;
EXECUTE prep_plan;
 count 
-------
     2
(1 row)

EXECUTE prep_plan;
 count 
-------
     2
(1 row)

EXECUTE prep_plan;
 count 
-------
     2
(1 row)

-- get name of first chunk
SELECT tableoid::regclass AS "CHUNK_NAME" FROM plan_inval ORDER BY time LIMIT 1
\gset
SELECT compress_chunk(:'CHUNK_NAME');
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_7_16_chunk
(1 row)

EXECUTE prep_plan;
 count 
-------
     2
(1 row)

EXPLAIN (COSTS OFF) EXECUTE prep_plan;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Finalize Aggregate
   ->  Append
         ->  Partial Aggregate
               ->  Custom Scan (DecompressChunk) on _hyper_7_16_chunk
                     ->  Seq Scan on compress_hyper_8_18_chunk
         ->  Partial Aggregate
               ->  Seq Scan on _hyper_7_17_chunk
(7 rows)

SET enable_hashagg = ON;
CREATE TABLE test_collation (
      time      TIMESTAMPTZ       NOT NULL,
      device_id  TEXT   COLLATE "C"           NULL,
      device_id_2  TEXT  COLLATE "POSIX"           NULL,
      val_1 TEXT  COLLATE "C" NULL,
      val_2 TEXT COLLATE "POSIX"  NULL
    );
--we want all the data to go into 1 chunk. so use 1 year chunk interval
select create_hypertable( 'test_collation', 'time', chunk_time_interval=> '1 day'::interval);
      create_hypertable      
-----------------------------
 (9,public,test_collation,t)
(1 row)

\set ON_ERROR_STOP 0
--forbid setting collation in compression ORDER BY clause. (parse error is fine)
alter table test_collation set (timescaledb.compress, timescaledb.compress_segmentby='device_id, device_id_2', timescaledb.compress_orderby = 'val_1 COLLATE "POSIX", val2, time');
ERROR:  unable to parse ordering option "val_1 COLLATE "POSIX", val2, time"
\set ON_ERROR_STOP 1
alter table test_collation set (timescaledb.compress, timescaledb.compress_segmentby='device_id, device_id_2', timescaledb.compress_orderby = 'val_1, val_2, time');
insert into test_collation
select generate_series('2018-01-01 00:00'::timestamp, '2018-01-10 00:00'::timestamp, '2 hour'), 'device_1', 'device_3', gen_rand_minstd(), gen_rand_minstd();
insert into test_collation
select generate_series('2018-01-01 00:00'::timestamp, '2018-01-10 00:00'::timestamp, '2 hour'), 'device_2', 'device_4', gen_rand_minstd(), gen_rand_minstd();
insert into test_collation
select generate_series('2018-01-01 00:00'::timestamp, '2018-01-10 00:00'::timestamp, '2 hour'), NULL, 'device_5', gen_rand_minstd(), gen_rand_minstd();
--compress 2 chunks
SELECT compress_chunk(ch) FROM show_chunks('test_collation') ch LIMIT 2;
             compress_chunk              
-----------------------------------------
 _timescaledb_internal._hyper_9_19_chunk
 _timescaledb_internal._hyper_9_20_chunk
(2 rows)

CREATE OR REPLACE PROCEDURE reindex_compressed_hypertable(hypertable REGCLASS)
AS $$
DECLARE
  hyper_id int;
BEGIN
  SELECT h.compressed_hypertable_id
  INTO hyper_id
  FROM _timescaledb_catalog.hypertable h
  WHERE h.table_name = hypertable::name;
  EXECUTE format('REINDEX TABLE _timescaledb_internal._compressed_hypertable_%s',
    hyper_id);
END $$ LANGUAGE plpgsql;
-- reindexing compressed hypertable to update statistics
CALL reindex_compressed_hypertable('test_collation');
--segment bys are pushed down correctly
EXPLAIN (costs off) SELECT * FROM test_collation WHERE device_id < 'a';
                        QUERY PLAN                        
----------------------------------------------------------
 Append
   ->  Custom Scan (DecompressChunk) on _hyper_9_19_chunk
         ->  Seq Scan on compress_hyper_10_29_chunk
               Filter: (device_id < 'a'::text)
   ->  Custom Scan (DecompressChunk) on _hyper_9_20_chunk
         ->  Seq Scan on compress_hyper_10_30_chunk
               Filter: (device_id < 'a'::text)
   ->  Seq Scan on _hyper_9_21_chunk
         Filter: (device_id < 'a'::text)
   ->  Seq Scan on _hyper_9_22_chunk
         Filter: (device_id < 'a'::text)
   ->  Seq Scan on _hyper_9_23_chunk
         Filter: (device_id < 'a'::text)
   ->  Seq Scan on _hyper_9_24_chunk
         Filter: (device_id < 'a'::text)
   ->  Seq Scan on _hyper_9_25_chunk
         Filter: (device_id < 'a'::text)
   ->  Seq Scan on _hyper_9_26_chunk
         Filter: (device_id < 'a'::text)
   ->  Seq Scan on _hyper_9_27_chunk
         Filter: (device_id < 'a'::text)
   ->  Seq Scan on _hyper_9_28_chunk
         Filter: (device_id < 'a'::text)
(23 rows)

EXPLAIN (costs off) SELECT * FROM test_collation WHERE device_id < 'a' COLLATE "POSIX";
                          QUERY PLAN                           
---------------------------------------------------------------
 Append
   ->  Custom Scan (DecompressChunk) on _hyper_9_19_chunk
         ->  Seq Scan on compress_hyper_10_29_chunk
               Filter: (device_id < 'a'::text COLLATE "POSIX")
   ->  Custom Scan (DecompressChunk) on _hyper_9_20_chunk
         ->  Seq Scan on compress_hyper_10_30_chunk
               Filter: (device_id < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_21_chunk
         Filter: (device_id < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_22_chunk
         Filter: (device_id < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_23_chunk
         Filter: (device_id < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_24_chunk
         Filter: (device_id < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_25_chunk
         Filter: (device_id < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_26_chunk
         Filter: (device_id < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_27_chunk
         Filter: (device_id < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_28_chunk
         Filter: (device_id < 'a'::text COLLATE "POSIX")
(23 rows)

\set ON_ERROR_STOP 0
EXPLAIN (costs off) SELECT * FROM test_collation WHERE device_id COLLATE "POSIX" < device_id_2 COLLATE "C";
ERROR:  collation mismatch between explicit collations "POSIX" and "C" at character 96
SELECT device_id < device_id_2  FROM test_collation;
ERROR:  could not determine which collation to use for string comparison
\set ON_ERROR_STOP 1
--segment meta on order bys pushdown
--should work
EXPLAIN (costs off) SELECT * FROM test_collation WHERE val_1 < 'a';
                        QUERY PLAN                        
----------------------------------------------------------
 Append
   ->  Custom Scan (DecompressChunk) on _hyper_9_19_chunk
         Filter: (val_1 < 'a'::text)
         ->  Seq Scan on compress_hyper_10_29_chunk
               Filter: (_ts_meta_min_1 < 'a'::text)
   ->  Custom Scan (DecompressChunk) on _hyper_9_20_chunk
         Filter: (val_1 < 'a'::text)
         ->  Seq Scan on compress_hyper_10_30_chunk
               Filter: (_ts_meta_min_1 < 'a'::text)
   ->  Seq Scan on _hyper_9_21_chunk
         Filter: (val_1 < 'a'::text)
   ->  Seq Scan on _hyper_9_22_chunk
         Filter: (val_1 < 'a'::text)
   ->  Seq Scan on _hyper_9_23_chunk
         Filter: (val_1 < 'a'::text)
   ->  Seq Scan on _hyper_9_24_chunk
         Filter: (val_1 < 'a'::text)
   ->  Seq Scan on _hyper_9_25_chunk
         Filter: (val_1 < 'a'::text)
   ->  Seq Scan on _hyper_9_26_chunk
         Filter: (val_1 < 'a'::text)
   ->  Seq Scan on _hyper_9_27_chunk
         Filter: (val_1 < 'a'::text)
   ->  Seq Scan on _hyper_9_28_chunk
         Filter: (val_1 < 'a'::text)
(25 rows)

EXPLAIN (costs off) SELECT * FROM test_collation WHERE val_2 < 'a';
                        QUERY PLAN                        
----------------------------------------------------------
 Append
   ->  Custom Scan (DecompressChunk) on _hyper_9_19_chunk
         Filter: (val_2 < 'a'::text)
         ->  Seq Scan on compress_hyper_10_29_chunk
               Filter: (_ts_meta_min_2 < 'a'::text)
   ->  Custom Scan (DecompressChunk) on _hyper_9_20_chunk
         Filter: (val_2 < 'a'::text)
         ->  Seq Scan on compress_hyper_10_30_chunk
               Filter: (_ts_meta_min_2 < 'a'::text)
   ->  Seq Scan on _hyper_9_21_chunk
         Filter: (val_2 < 'a'::text)
   ->  Seq Scan on _hyper_9_22_chunk
         Filter: (val_2 < 'a'::text)
   ->  Seq Scan on _hyper_9_23_chunk
         Filter: (val_2 < 'a'::text)
   ->  Seq Scan on _hyper_9_24_chunk
         Filter: (val_2 < 'a'::text)
   ->  Seq Scan on _hyper_9_25_chunk
         Filter: (val_2 < 'a'::text)
   ->  Seq Scan on _hyper_9_26_chunk
         Filter: (val_2 < 'a'::text)
   ->  Seq Scan on _hyper_9_27_chunk
         Filter: (val_2 < 'a'::text)
   ->  Seq Scan on _hyper_9_28_chunk
         Filter: (val_2 < 'a'::text)
(25 rows)

EXPLAIN (costs off) SELECT * FROM test_collation WHERE val_1 < 'a' COLLATE "C";
                           QUERY PLAN                           
----------------------------------------------------------------
 Append
   ->  Custom Scan (DecompressChunk) on _hyper_9_19_chunk
         Filter: (val_1 < 'a'::text COLLATE "C")
         ->  Seq Scan on compress_hyper_10_29_chunk
               Filter: (_ts_meta_min_1 < 'a'::text COLLATE "C")
   ->  Custom Scan (DecompressChunk) on _hyper_9_20_chunk
         Filter: (val_1 < 'a'::text COLLATE "C")
         ->  Seq Scan on compress_hyper_10_30_chunk
               Filter: (_ts_meta_min_1 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_21_chunk
         Filter: (val_1 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_22_chunk
         Filter: (val_1 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_23_chunk
         Filter: (val_1 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_24_chunk
         Filter: (val_1 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_25_chunk
         Filter: (val_1 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_26_chunk
         Filter: (val_1 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_27_chunk
         Filter: (val_1 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_28_chunk
         Filter: (val_1 < 'a'::text COLLATE "C")
(25 rows)

EXPLAIN (costs off) SELECT * FROM test_collation WHERE val_2 < 'a' COLLATE "POSIX";
                             QUERY PLAN                             
--------------------------------------------------------------------
 Append
   ->  Custom Scan (DecompressChunk) on _hyper_9_19_chunk
         Filter: (val_2 < 'a'::text COLLATE "POSIX")
         ->  Seq Scan on compress_hyper_10_29_chunk
               Filter: (_ts_meta_min_2 < 'a'::text COLLATE "POSIX")
   ->  Custom Scan (DecompressChunk) on _hyper_9_20_chunk
         Filter: (val_2 < 'a'::text COLLATE "POSIX")
         ->  Seq Scan on compress_hyper_10_30_chunk
               Filter: (_ts_meta_min_2 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_21_chunk
         Filter: (val_2 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_22_chunk
         Filter: (val_2 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_23_chunk
         Filter: (val_2 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_24_chunk
         Filter: (val_2 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_25_chunk
         Filter: (val_2 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_26_chunk
         Filter: (val_2 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_27_chunk
         Filter: (val_2 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_28_chunk
         Filter: (val_2 < 'a'::text COLLATE "POSIX")
(25 rows)

--cannot pushdown when op collation does not match column's collation since min/max used different collation than what op needs
EXPLAIN (costs off) SELECT * FROM test_collation WHERE val_1 < 'a' COLLATE "POSIX";
                        QUERY PLAN                        
----------------------------------------------------------
 Append
   ->  Custom Scan (DecompressChunk) on _hyper_9_19_chunk
         Filter: (val_1 < 'a'::text COLLATE "POSIX")
         ->  Seq Scan on compress_hyper_10_29_chunk
   ->  Custom Scan (DecompressChunk) on _hyper_9_20_chunk
         Filter: (val_1 < 'a'::text COLLATE "POSIX")
         ->  Seq Scan on compress_hyper_10_30_chunk
   ->  Seq Scan on _hyper_9_21_chunk
         Filter: (val_1 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_22_chunk
         Filter: (val_1 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_23_chunk
         Filter: (val_1 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_24_chunk
         Filter: (val_1 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_25_chunk
         Filter: (val_1 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_26_chunk
         Filter: (val_1 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_27_chunk
         Filter: (val_1 < 'a'::text COLLATE "POSIX")
   ->  Seq Scan on _hyper_9_28_chunk
         Filter: (val_1 < 'a'::text COLLATE "POSIX")
(23 rows)

EXPLAIN (costs off) SELECT * FROM test_collation WHERE val_2 < 'a' COLLATE "C";
                        QUERY PLAN                        
----------------------------------------------------------
 Append
   ->  Custom Scan (DecompressChunk) on _hyper_9_19_chunk
         Filter: (val_2 < 'a'::text COLLATE "C")
         ->  Seq Scan on compress_hyper_10_29_chunk
   ->  Custom Scan (DecompressChunk) on _hyper_9_20_chunk
         Filter: (val_2 < 'a'::text COLLATE "C")
         ->  Seq Scan on compress_hyper_10_30_chunk
   ->  Seq Scan on _hyper_9_21_chunk
         Filter: (val_2 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_22_chunk
         Filter: (val_2 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_23_chunk
         Filter: (val_2 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_24_chunk
         Filter: (val_2 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_25_chunk
         Filter: (val_2 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_26_chunk
         Filter: (val_2 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_27_chunk
         Filter: (val_2 < 'a'::text COLLATE "C")
   ->  Seq Scan on _hyper_9_28_chunk
         Filter: (val_2 < 'a'::text COLLATE "C")
(23 rows)

--test datatypes
CREATE TABLE datatype_test(
  time timestamptz NOT NULL,
  int2_column int2,
  int4_column int4,
  int8_column int8,
  float4_column float4,
  float8_column float8,
  date_column date,
  timestamp_column timestamp,
  timestamptz_column timestamptz,
  interval_column interval,
  numeric_column numeric,
  decimal_column decimal,
  text_column text,
  char_column char
);
SELECT create_hypertable('datatype_test','time');
WARNING:  column type "timestamp without time zone" used for "timestamp_column" does not follow best practices
      create_hypertable      
-----------------------------
 (11,public,datatype_test,t)
(1 row)

ALTER TABLE datatype_test SET (timescaledb.compress);
INSERT INTO datatype_test VALUES ('2000-01-01',2,4,8,4.0,8.0,'2000-01-01','2001-01-01 12:00','2001-01-01 6:00','1 week', 3.41, 4.2, 'text', 'x');
SELECT count(compress_chunk(ch)) FROM show_chunks('datatype_test') ch;
 count 
-------
     1
(1 row)

SELECT
  attname, alg.name
FROM _timescaledb_catalog.hypertable ht
  INNER JOIN _timescaledb_catalog.hypertable_compression htc ON ht.id=htc.hypertable_id
  INNER JOIN _timescaledb_catalog.compression_algorithm alg ON alg.id=htc.compression_algorithm_id
WHERE ht.table_name='datatype_test'
ORDER BY attname;
      attname       |               name               
--------------------+----------------------------------
 char_column        | COMPRESSION_ALGORITHM_DICTIONARY
 date_column        | COMPRESSION_ALGORITHM_DELTADELTA
 decimal_column     | COMPRESSION_ALGORITHM_ARRAY
 float4_column      | COMPRESSION_ALGORITHM_GORILLA
 float8_column      | COMPRESSION_ALGORITHM_GORILLA
 int2_column        | COMPRESSION_ALGORITHM_DELTADELTA
 int4_column        | COMPRESSION_ALGORITHM_DELTADELTA
 int8_column        | COMPRESSION_ALGORITHM_DELTADELTA
 interval_column    | COMPRESSION_ALGORITHM_DICTIONARY
 numeric_column     | COMPRESSION_ALGORITHM_ARRAY
 text_column        | COMPRESSION_ALGORITHM_DICTIONARY
 time               | COMPRESSION_ALGORITHM_DELTADELTA
 timestamp_column   | COMPRESSION_ALGORITHM_DELTADELTA
 timestamptz_column | COMPRESSION_ALGORITHM_DELTADELTA
(14 rows)

--TEST try to compress a hypertable that has a continuous aggregate
CREATE TABLE metrics(time timestamptz, device_id int, v1 float, v2 float);
SELECT create_hypertable('metrics','time');
NOTICE:  adding not-null constraint to column "time"
   create_hypertable   
-----------------------
 (13,public,metrics,t)
(1 row)

INSERT INTO metrics SELECT generate_series('2000-01-01'::timestamptz,'2000-01-10','1m'),1,0.25,0.75;
-- check expressions in view definition
CREATE MATERIALIZED VIEW cagg_expr WITH (timescaledb.continuous)
AS
SELECT
  time_bucket('1d', time) AS time,
  'Const'::text AS Const,
  4.3::numeric AS "numeric",
  first(metrics,time),
  CASE WHEN true THEN 'foo' ELSE 'bar' END,
  COALESCE(NULL,'coalesce'),
  avg(v1) + avg(v2) AS avg1,
  avg(v1+v2) AS avg2,
  count(*) AS cnt
FROM metrics
GROUP BY 1 WITH NO DATA;
CALL refresh_continuous_aggregate('cagg_expr', NULL, NULL);
SELECT * FROM cagg_expr ORDER BY time LIMIT 5;
             time             | const | numeric |                    first                     | case | coalesce | avg1 | avg2 | cnt  
------------------------------+-------+---------+----------------------------------------------+------+----------+------+------+------
 Fri Dec 31 16:00:00 1999 PST | Const |     4.3 | ("Sat Jan 01 00:00:00 2000 PST",1,0.25,0.75) | foo  | coalesce |    1 |    1 |  960
 Sat Jan 01 16:00:00 2000 PST | Const |     4.3 | ("Sat Jan 01 16:00:00 2000 PST",1,0.25,0.75) | foo  | coalesce |    1 |    1 | 1440
 Sun Jan 02 16:00:00 2000 PST | Const |     4.3 | ("Sun Jan 02 16:00:00 2000 PST",1,0.25,0.75) | foo  | coalesce |    1 |    1 | 1440
 Mon Jan 03 16:00:00 2000 PST | Const |     4.3 | ("Mon Jan 03 16:00:00 2000 PST",1,0.25,0.75) | foo  | coalesce |    1 |    1 | 1440
 Tue Jan 04 16:00:00 2000 PST | Const |     4.3 | ("Tue Jan 04 16:00:00 2000 PST",1,0.25,0.75) | foo  | coalesce |    1 |    1 | 1440
(5 rows)

ALTER TABLE metrics set(timescaledb.compress);
-- test rescan in compress chunk dml blocker
CREATE TABLE rescan_test(id integer NOT NULL, t timestamptz NOT NULL, val double precision, PRIMARY KEY(id, t));
SELECT create_hypertable('rescan_test', 't', chunk_time_interval => interval '1 day');
     create_hypertable     
---------------------------
 (16,public,rescan_test,t)
(1 row)

-- compression
ALTER TABLE rescan_test SET (timescaledb.compress, timescaledb.compress_segmentby = 'id');
-- INSERT dummy data
INSERT INTO rescan_test SELECT 1, time, random() FROM generate_series('2000-01-01'::timestamptz, '2000-01-05'::timestamptz, '1h'::interval) g(time);
SELECT count(*) FROM rescan_test;
 count 
-------
    97
(1 row)

-- compress first chunk
SELECT compress_chunk(ch) FROM show_chunks('rescan_test') ch LIMIT 1;
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_16_36_chunk
(1 row)

-- count should be equal to count before compression
SELECT count(*) FROM rescan_test;
 count 
-------
    97
(1 row)

-- single row update is fine
UPDATE rescan_test SET val = val + 1 WHERE rescan_test.id = 1 AND rescan_test.t = '2000-01-03 00:00:00+00';
-- multi row update via WHERE is fine
UPDATE rescan_test SET val = val + 1 WHERE rescan_test.id = 1 AND rescan_test.t > '2000-01-03 00:00:00+00';
-- single row update with FROM is allowed if no compressed chunks are hit
UPDATE rescan_test SET val = tmp.val
FROM (SELECT x.id, x.t, x.val FROM unnest(array[(1, '2000-01-03 00:00:00+00', 2.045)]::rescan_test[]) AS x) AS tmp
WHERE rescan_test.id = tmp.id AND rescan_test.t = tmp.t AND rescan_test.t >= '2000-01-03';
-- single row update with FROM is blocked
\set ON_ERROR_STOP 0
UPDATE rescan_test SET val = tmp.val
FROM (SELECT x.id, x.t, x.val FROM unnest(array[(1, '2000-01-03 00:00:00+00', 2.045)]::rescan_test[]) AS x) AS tmp
WHERE rescan_test.id = tmp.id AND rescan_test.t = tmp.t;
-- bulk row update with FROM is blocked
UPDATE rescan_test SET val = tmp.val
FROM (SELECT x.id, x.t, x.val FROM unnest(array[(1, '2000-01-03 00:00:00+00', 2.045), (1, '2000-01-03 01:00:00+00', 8.045)]::rescan_test[]) AS x) AS tmp
WHERE rescan_test.id = tmp.id AND rescan_test.t = tmp.t;
\set ON_ERROR_STOP 1
-- Test FK constraint drop and recreate during compression and decompression on a chunk
CREATE TABLE meta (device_id INT PRIMARY KEY);
CREATE TABLE hyper(
    time INT NOT NULL,
    device_id INT REFERENCES meta(device_id) ON DELETE CASCADE ON UPDATE CASCADE,
    val INT);
SELECT * FROM create_hypertable('hyper', 'time', chunk_time_interval => 10);
 hypertable_id | schema_name | table_name | created 
---------------+-------------+------------+---------
            18 | public      | hyper      | t
(1 row)

ALTER TABLE hyper SET (
    timescaledb.compress,
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_segmentby = 'device_id');
INSERT INTO meta VALUES (1), (2), (3), (4), (5);
INSERT INTO hyper VALUES (1, 1, 1), (2, 2, 1), (3, 3, 1), (10, 3, 2), (11, 4, 2), (11, 5, 2);
SELECT ch1.table_name AS "CHUNK_NAME", ch1.schema_name|| '.' || ch1.table_name AS "CHUNK_FULL_NAME"
FROM _timescaledb_catalog.chunk ch1, _timescaledb_catalog.hypertable ht
WHERE ch1.hypertable_id = ht.id AND ht.table_name LIKE 'hyper'
ORDER BY ch1.id LIMIT 1 \gset
SELECT constraint_schema, constraint_name, table_schema, table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = :'CHUNK_NAME' AND constraint_type = 'FOREIGN KEY'
ORDER BY constraint_name;
   constraint_schema   |      constraint_name      |     table_schema      |     table_name     | constraint_type 
-----------------------+---------------------------+-----------------------+--------------------+-----------------
 _timescaledb_internal | 42_6_hyper_device_id_fkey | _timescaledb_internal | _hyper_18_42_chunk | FOREIGN KEY
(1 row)

SELECT compress_chunk(:'CHUNK_FULL_NAME');
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_18_42_chunk
(1 row)

SELECT constraint_schema, constraint_name, table_schema, table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = :'CHUNK_NAME' AND constraint_type = 'FOREIGN KEY'
ORDER BY constraint_name;
 constraint_schema | constraint_name | table_schema | table_name | constraint_type 
-------------------+-----------------+--------------+------------+-----------------
(0 rows)

-- Delete data from compressed chunk directly fails
\set ON_ERROR_STOP 0
DELETE FROM hyper WHERE device_id = 3;
\set ON_ERROR_STOP 0
-- Delete data from FK-referenced table deletes data from compressed chunk
SELECT * FROM hyper ORDER BY time, device_id;
 time | device_id | val 
------+-----------+-----
    1 |         1 |   1
    2 |         2 |   1
   11 |         4 |   2
   11 |         5 |   2
(4 rows)

DELETE FROM meta WHERE device_id = 3;
SELECT * FROM hyper ORDER BY time, device_id;
 time | device_id | val 
------+-----------+-----
    1 |         1 |   1
    2 |         2 |   1
   11 |         4 |   2
   11 |         5 |   2
(4 rows)

SELECT decompress_chunk(:'CHUNK_FULL_NAME');
             decompress_chunk             
------------------------------------------
 _timescaledb_internal._hyper_18_42_chunk
(1 row)

SELECT constraint_schema, constraint_name, table_schema, table_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = :'CHUNK_NAME' AND constraint_type = 'FOREIGN KEY'
ORDER BY constraint_name;
   constraint_schema   |      constraint_name      |     table_schema      |     table_name     | constraint_type 
-----------------------+---------------------------+-----------------------+--------------------+-----------------
 _timescaledb_internal | 42_9_hyper_device_id_fkey | _timescaledb_internal | _hyper_18_42_chunk | FOREIGN KEY
(1 row)

-- create hypertable with 2 chunks
CREATE TABLE ht5(time TIMESTAMPTZ NOT NULL);
SELECT create_hypertable('ht5','time');
 create_hypertable 
-------------------
 (20,public,ht5,t)
(1 row)

INSERT INTO ht5 SELECT '2000-01-01'::TIMESTAMPTZ;
INSERT INTO ht5 SELECT '2001-01-01'::TIMESTAMPTZ;
-- compressed chunk stats should not show dropped chunks
ALTER TABLE ht5 SET (timescaledb.compress);
SELECT compress_chunk(i) FROM show_chunks('ht5') i;
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_20_45_chunk
 _timescaledb_internal._hyper_20_46_chunk
(2 rows)

SELECT drop_chunks('ht5', newer_than => '2000-01-01'::TIMESTAMPTZ);
               drop_chunks                
------------------------------------------
 _timescaledb_internal._hyper_20_46_chunk
(1 row)

select chunk_name from chunk_compression_stats('ht5')
order by chunk_name;
     chunk_name     
--------------------
 _hyper_20_45_chunk
(1 row)

-- Test enabling compression for a table with compound foreign key
-- (Issue https://github.com/timescale/timescaledb/issues/2000)
CREATE TABLE table2(col1 INT, col2 int, primary key (col1,col2));
CREATE TABLE table1(col1 INT NOT NULL, col2 INT);
ALTER TABLE table1 ADD CONSTRAINT fk_table1 FOREIGN KEY (col1,col2) REFERENCES table2(col1,col2);
SELECT create_hypertable('table1','col1', chunk_time_interval => 10);
  create_hypertable   
----------------------
 (22,public,table1,t)
(1 row)

-- Trying to list an incomplete set of fields of the compound key (should fail with a nice message)
ALTER TABLE table1 SET (timescaledb.compress, timescaledb.compress_segmentby = 'col1');
ERROR:  column "col2" must be used for segmenting
-- Listing all fields of the compound key should succeed:
ALTER TABLE table1 SET (timescaledb.compress, timescaledb.compress_segmentby = 'col1,col2');
SELECT * FROM timescaledb_information.compression_settings ORDER BY hypertable_name;
 hypertable_schema | hypertable_name |   attname   | segmentby_column_index | orderby_column_index | orderby_asc | orderby_nullsfirst 
-------------------+-----------------+-------------+------------------------+----------------------+-------------+--------------------
 public            | conditions      | location    |                      1 |                      |             | 
 public            | conditions      | time        |                        |                    1 | t           | f
 public            | datatype_test   | time        |                        |                    1 | f           | t
 public            | foo             | a           |                      1 |                      |             | 
 public            | foo             | b           |                      2 |                      |             | 
 public            | foo             | c           |                        |                    1 | f           | t
 public            | foo             | d           |                        |                    2 | t           | f
 public            | ht5             | time        |                        |                    1 | f           | t
 public            | hyper           | device_id   |                      1 |                      |             | 
 public            | hyper           | time        |                        |                    1 | t           | f
 public            | metrics         | time        |                        |                    1 | f           | t
 public            | plan_inval      | time        |                        |                    1 | f           | t
 public            | rescan_test     | id          |                      1 |                      |             | 
 public            | rescan_test     | t           |                        |                    1 | f           | t
 public            | table1          | col1        |                      1 |                      |             | 
 public            | table1          | col2        |                      2 |                      |             | 
 public            | test_collation  | device_id   |                      1 |                      |             | 
 public            | test_collation  | device_id_2 |                      2 |                      |             | 
 public            | test_collation  | val_1       |                        |                    1 | t           | f
 public            | test_collation  | val_2       |                        |                    2 | t           | f
 public            | test_collation  | time        |                        |                    3 | t           | f
(21 rows)

-- test delete/update on non-compressed tables involving hypertables with compression
CREATE TABLE uncompressed_ht (
  time timestamptz NOT NULL,
  value double precision,
  series_id integer
);
SELECT table_name FROM create_hypertable ('uncompressed_ht', 'time');
   table_name    
-----------------
 uncompressed_ht
(1 row)

INSERT INTO uncompressed_ht
  VALUES ('2020-04-20 01:01', 100, 1), ('2020-05-20 01:01', 100, 1), ('2020-04-20 01:01', 200, 2);
CREATE TABLE compressed_ht (
  time timestamptz NOT NULL,
  value double precision,
  series_id integer
);
SELECT table_name FROM create_hypertable ('compressed_ht', 'time');
  table_name   
---------------
 compressed_ht
(1 row)

ALTER TABLE compressed_ht SET (timescaledb.compress);
INSERT INTO compressed_ht
  VALUES ('2020-04-20 01:01', 100, 1), ('2020-05-20 01:01', 100, 1);
SELECT count(compress_chunk(ch)) FROM show_chunks('compressed_ht') ch;
 count 
-------
     2
(1 row)

BEGIN;
WITH compressed AS (
  SELECT series_id
  FROM compressed_ht
  WHERE time >= '2020-04-17 17:14:24.161989+00'
)
DELETE FROM uncompressed_ht
WHERE series_id IN (SELECT series_id FROM compressed);
ROLLBACK;
-- test delete inside CTE
WITH compressed AS (
  DELETE FROM compressed_ht RETURNING series_id
)
SELECT * FROM uncompressed_ht
WHERE series_id IN (SELECT series_id FROM compressed);
             time             | value | series_id 
------------------------------+-------+-----------
 Mon Apr 20 01:01:00 2020 PDT |   100 |         1
 Wed May 20 01:01:00 2020 PDT |   100 |         1
(2 rows)

-- test update inside CTE is blocked
WITH compressed AS (
  UPDATE compressed_ht SET value = 0.2 RETURNING *
)
SELECT * FROM uncompressed_ht
WHERE series_id IN (SELECT series_id FROM compressed);
 time | value | series_id 
------+-------+-----------
(0 rows)

DROP TABLE compressed_ht;
DROP TABLE uncompressed_ht;
-- Test that pg_stats and pg_class stats for uncompressed chunks are correctly updated after compression.
-- Note that approximate_row_count pulls from pg_class
CREATE TABLE stattest(time TIMESTAMPTZ NOT NULL, c1 int);
SELECT create_hypertable('stattest', 'time');
   create_hypertable    
------------------------
 (27,public,stattest,t)
(1 row)

INSERT INTO stattest SELECT '2020/02/20 01:00'::TIMESTAMPTZ + ('1 hour'::interval * v), 250 * v FROM generate_series(0,25) v;
SELECT table_name INTO TEMPORARY temptable FROM _timescaledb_catalog.chunk WHERE hypertable_id = (SELECT id FROM _timescaledb_catalog.hypertable WHERE table_name = 'stattest');
\set statchunk '(select table_name from temptable)'
SELECT * FROM pg_stats WHERE tablename = :statchunk;
 schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram 
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------
(0 rows)

ALTER TABLE stattest SET (timescaledb.compress);
-- check that approximate_row_count works with all normal chunks
SELECT approximate_row_count('stattest');
 approximate_row_count 
-----------------------
                     0
(1 row)

SELECT compress_chunk(c) FROM show_chunks('stattest') c;
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_27_55_chunk
(1 row)

-- check that approximate_row_count works with all compressed chunks
SELECT approximate_row_count('stattest');
 approximate_row_count 
-----------------------
                    26
(1 row)

-- actual count should match with the above
SELECT count(*) from stattest;
 count 
-------
    26
(1 row)

-- Uncompressed chunk table is empty since we just compressed the chunk and moved everything to compressed chunk table.
-- reltuples is initially -1 on PG14 before VACUUM/ANALYZE was run
SELECT relpages, CASE WHEN reltuples > 0 THEN reltuples ELSE 0 END as reltuples FROM pg_class WHERE relname = :statchunk;
 relpages | reltuples 
----------+-----------
        0 |         0
(1 row)

SELECT histogram_bounds FROM pg_stats WHERE tablename = :statchunk AND attname = 'c1';
                                                       histogram_bounds                                                        
-------------------------------------------------------------------------------------------------------------------------------
 {0,250,500,750,1000,1250,1500,1750,2000,2250,2500,2750,3000,3250,3500,3750,4000,4250,4500,4750,5000,5250,5500,5750,6000,6250}
(1 row)

SELECT compch.table_name  as "STAT_COMP_CHUNK_NAME"
FROM _timescaledb_catalog.hypertable ht, _timescaledb_catalog.chunk ch
       , _timescaledb_catalog.chunk compch
  WHERE ht.table_name = 'stattest' AND ch.hypertable_id = ht.id
        AND compch.id = ch.compressed_chunk_id AND ch.compressed_chunk_id > 0  \gset
-- reltuples is initially -1 on PG14 before VACUUM/ANALYZE was run
SELECT relpages, CASE WHEN reltuples > 0 THEN reltuples ELSE 0 END as reltuples FROM pg_class WHERE relname = :'STAT_COMP_CHUNK_NAME';
 relpages | reltuples 
----------+-----------
        0 |         0
(1 row)

-- Now verify stats are updated on compressed chunk table when we analyze the hypertable.
ANALYZE stattest;
SELECT histogram_bounds FROM pg_stats WHERE tablename = :statchunk AND attname = 'c1';
                                                       histogram_bounds                                                        
-------------------------------------------------------------------------------------------------------------------------------
 {0,250,500,750,1000,1250,1500,1750,2000,2250,2500,2750,3000,3250,3500,3750,4000,4250,4500,4750,5000,5250,5500,5750,6000,6250}
(1 row)

-- Unfortunately, the stats on the hypertable won't find any rows to sample from the chunk
SELECT histogram_bounds FROM pg_stats WHERE tablename = 'stattest' AND attname = 'c1';
 histogram_bounds 
------------------
(0 rows)

SELECT relpages, reltuples FROM pg_class WHERE relname = :statchunk;
 relpages | reltuples 
----------+-----------
        0 |         0
(1 row)

-- verify that corresponding compressed chunk table stats is updated as well.
SELECT relpages, reltuples FROM pg_class WHERE relname = :'STAT_COMP_CHUNK_NAME';
 relpages | reltuples 
----------+-----------
        1 |         1
(1 row)

-- verify that approximate_row_count works fine on a chunk with compressed data
SELECT approximate_row_count('_timescaledb_internal.' || :'STAT_COMP_CHUNK_NAME');
 approximate_row_count 
-----------------------
                    26
(1 row)

-- Verify partial chunk stats are handled correctly when analyzing
-- for both uncompressed and compressed chunk tables
INSERT INTO stattest SELECT '2020/02/20 01:00'::TIMESTAMPTZ + ('1 hour'::interval * v), 250 * v FROM generate_series(25,50) v;
ANALYZE stattest;
SELECT histogram_bounds FROM pg_stats WHERE tablename = :statchunk AND attname = 'c1';
                                                                histogram_bounds                                                                
------------------------------------------------------------------------------------------------------------------------------------------------
 {6250,6500,6750,7000,7250,7500,7750,8000,8250,8500,8750,9000,9250,9500,9750,10000,10250,10500,10750,11000,11250,11500,11750,12000,12250,12500}
(1 row)

-- Hypertable will now see the histogram bounds since we have data in the uncompressed chunk table.
SELECT histogram_bounds FROM pg_stats WHERE tablename = 'stattest' AND attname = 'c1';
                                                                histogram_bounds                                                                
------------------------------------------------------------------------------------------------------------------------------------------------
 {6250,6500,6750,7000,7250,7500,7750,8000,8250,8500,8750,9000,9250,9500,9750,10000,10250,10500,10750,11000,11250,11500,11750,12000,12250,12500}
(1 row)

-- verify that corresponding uncompressed chunk table stats is updated as well.
SELECT relpages, reltuples FROM pg_class WHERE relname = :statchunk;
 relpages | reltuples 
----------+-----------
        1 |        26
(1 row)

-- verify that corresponding compressed chunk table stats have not changed since
-- we didn't compress anything new.
SELECT relpages, reltuples FROM pg_class WHERE relname = :'STAT_COMP_CHUNK_NAME';
 relpages | reltuples 
----------+-----------
        1 |         1
(1 row)

-- verify that approximate_row_count works fine on a chunk with a mix of uncompressed
-- and compressed data
SELECT table_name  as "STAT_CHUNK_NAME" from temptable \gset
SELECT approximate_row_count('_timescaledb_internal.' || :'STAT_CHUNK_NAME');
 approximate_row_count 
-----------------------
                    52
(1 row)

-- should match with the result via the hypertable post in-memory decompression
SELECT count(*) from stattest;
 count 
-------
    52
(1 row)

SELECT count(*) from show_chunks('stattest');
 count 
-------
     1
(1 row)

-- Verify that decompressing the chunk restores autoanalyze to the hypertable's setting
SELECT reloptions FROM pg_class WHERE relname = :statchunk;
 reloptions 
------------
 
(1 row)

SELECT decompress_chunk(c) FROM show_chunks('stattest') c;
             decompress_chunk             
------------------------------------------
 _timescaledb_internal._hyper_27_55_chunk
(1 row)

SELECT reloptions FROM pg_class WHERE relname = :statchunk;
 reloptions 
------------
 
(1 row)

SELECT compress_chunk(c) FROM show_chunks('stattest') c;
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_27_55_chunk
(1 row)

SELECT reloptions FROM pg_class WHERE relname = :statchunk;
 reloptions 
------------
 
(1 row)

ALTER TABLE stattest SET (autovacuum_enabled = false);
SELECT decompress_chunk(c) FROM show_chunks('stattest') c;
             decompress_chunk             
------------------------------------------
 _timescaledb_internal._hyper_27_55_chunk
(1 row)

SELECT reloptions FROM pg_class WHERE relname = :statchunk;
         reloptions         
----------------------------
 {autovacuum_enabled=false}
(1 row)

-- Verify that even a global analyze works as well, changing message scope here
-- to hide WARNINGs for skipped tables
SET client_min_messages TO ERROR;
ANALYZE;
SET client_min_messages TO NOTICE;
SELECT histogram_bounds FROM pg_stats WHERE tablename = :statchunk and attname = 'c1';
                                                                                                                         histogram_bounds                                                                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {0,250,500,750,1000,1250,1500,1750,2000,2250,2500,2750,3000,3250,3500,3750,4000,4250,4500,4750,5000,5250,5500,5750,6000,6500,6750,7000,7250,7500,7750,8000,8250,8500,8750,9000,9250,9500,9750,10000,10250,10500,10750,11000,11250,11500,11750,12000,12250,12500}
(1 row)

SELECT relpages, reltuples FROM pg_class WHERE relname = :statchunk;
 relpages | reltuples 
----------+-----------
        1 |        52
(1 row)

--- Test that analyze on compression internal table updates stats on original chunks
CREATE TABLE stattest2(time TIMESTAMPTZ NOT NULL, c1 int, c2 int);
SELECT create_hypertable('stattest2', 'time', chunk_time_interval=>'1 day'::interval);
    create_hypertable    
-------------------------
 (29,public,stattest2,t)
(1 row)

ALTER TABLE stattest2 SET (timescaledb.compress, timescaledb.compress_segmentby='c1');
INSERT INTO stattest2 SELECT '2020/06/20 01:00'::TIMESTAMPTZ ,1 , generate_series(1, 200, 1);
INSERT INTO stattest2 SELECT '2020/07/20 01:00'::TIMESTAMPTZ ,1 , generate_series(1, 200, 1);
SELECT compress_chunk(ch) FROM show_chunks('stattest2') ch LIMIT 1;
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_29_58_chunk
(1 row)

-- reltuples is initially -1 on PG14 before VACUUM/ANALYZE has been run
SELECT relname, CASE WHEN reltuples > 0 THEN reltuples ELSE 0 END AS reltuples, relpages, relallvisible FROM pg_class
 WHERE relname in ( SELECT ch.table_name FROM
                   _timescaledb_catalog.chunk ch, _timescaledb_catalog.hypertable ht
  WHERE ht.table_name = 'stattest2' AND ch.hypertable_id = ht.id )
order by relname;
      relname       | reltuples | relpages | relallvisible 
--------------------+-----------+----------+---------------
 _hyper_29_58_chunk |         0 |        0 |             0
 _hyper_29_59_chunk |         0 |        0 |             0
(2 rows)

\c :TEST_DBNAME :ROLE_SUPERUSER
--overwrite pg_class stats for the compressed chunk.
UPDATE pg_class
SET reltuples = 0, relpages = 0
 WHERE relname in ( SELECT ch.table_name FROM
    _timescaledb_catalog.chunk ch,
    _timescaledb_catalog.hypertable ht
  WHERE ht.table_name = 'stattest2' AND ch.hypertable_id = ht.id
        AND ch.compressed_chunk_id > 0 );
\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER
-- reltuples is initially -1 on PG14 before VACUUM/ANALYZE has been run
SELECT relname, CASE WHEN reltuples > 0 THEN reltuples ELSE 0 END AS reltuples, relpages, relallvisible FROM pg_class
 WHERE relname in ( SELECT ch.table_name FROM
                   _timescaledb_catalog.chunk ch, _timescaledb_catalog.hypertable ht
  WHERE ht.table_name = 'stattest2' AND ch.hypertable_id = ht.id )
order by relname;
      relname       | reltuples | relpages | relallvisible 
--------------------+-----------+----------+---------------
 _hyper_29_58_chunk |         0 |        0 |             0
 _hyper_29_59_chunk |         0 |        0 |             0
(2 rows)

SELECT '_timescaledb_internal.' || compht.table_name as "STAT_COMP_TABLE",
             compht.table_name  as "STAT_COMP_TABLE_NAME"
FROM _timescaledb_catalog.hypertable ht, _timescaledb_catalog.hypertable compht
WHERE ht.table_name = 'stattest2' AND ht.compressed_hypertable_id = compht.id \gset
--analyze the compressed table, will update stats for the raw table.
ANALYZE :STAT_COMP_TABLE;
-- reltuples is initially -1 on PG14 before VACUUM/ANALYZE has been run
SELECT relname, CASE WHEN reltuples > 0 THEN reltuples ELSE 0 END AS reltuples, relpages, relallvisible FROM pg_class
 WHERE relname in ( SELECT ch.table_name FROM
                   _timescaledb_catalog.chunk ch, _timescaledb_catalog.hypertable ht
  WHERE ht.table_name = 'stattest2' AND ch.hypertable_id = ht.id )
ORDER BY relname;
      relname       | reltuples | relpages | relallvisible 
--------------------+-----------+----------+---------------
 _hyper_29_58_chunk |         0 |        0 |             0
 _hyper_29_59_chunk |         0 |        0 |             0
(2 rows)

SELECT relname, reltuples, relpages, relallvisible FROM pg_class
 WHERE relname in ( SELECT ch.table_name FROM
                   _timescaledb_catalog.chunk ch, _timescaledb_catalog.hypertable ht
  WHERE ht.table_name = :'STAT_COMP_TABLE_NAME' AND ch.hypertable_id = ht.id )
ORDER BY relname;
          relname           | reltuples | relpages | relallvisible 
----------------------------+-----------+----------+---------------
 compress_hyper_30_60_chunk |         1 |        1 |             0
(1 row)

--analyze on stattest2 should not overwrite
ANALYZE stattest2;
SELECT relname, reltuples, relpages, relallvisible FROM pg_class
 WHERE relname in ( SELECT ch.table_name FROM
                   _timescaledb_catalog.chunk ch, _timescaledb_catalog.hypertable ht
  WHERE ht.table_name = 'stattest2' AND ch.hypertable_id = ht.id )
ORDER BY relname;
      relname       | reltuples | relpages | relallvisible 
--------------------+-----------+----------+---------------
 _hyper_29_58_chunk |         0 |        0 |             0
 _hyper_29_59_chunk |       200 |        2 |             0
(2 rows)

SELECT relname, reltuples, relpages, relallvisible FROM pg_class
 WHERE relname in ( SELECT ch.table_name FROM
                   _timescaledb_catalog.chunk ch, _timescaledb_catalog.hypertable ht
  WHERE ht.table_name = :'STAT_COMP_TABLE_NAME' AND ch.hypertable_id = ht.id )
ORDER BY relname;
          relname           | reltuples | relpages | relallvisible 
----------------------------+-----------+----------+---------------
 compress_hyper_30_60_chunk |         1 |        1 |             0
(1 row)

-- analyze on compressed hypertable should restore stats
-- Test approximate_row_count() with compressed hypertable
--
CREATE TABLE approx_count(time timestamptz not null, device int, temp float);
SELECT create_hypertable('approx_count', 'time');
     create_hypertable      
----------------------------
 (31,public,approx_count,t)
(1 row)

INSERT INTO approx_count SELECT t, (abs(timestamp_hash(t::timestamp)) % 10) + 1, random()*80
FROM generate_series('2018-03-02 1:00'::TIMESTAMPTZ, '2018-03-04 1:00', '1 hour') t;
SELECT count(*) FROM approx_count;
 count 
-------
    49
(1 row)

ALTER TABLE approx_count SET (timescaledb.compress, timescaledb.compress_segmentby='device', timescaledb.compress_orderby = 'time DESC');
SELECT approximate_row_count('approx_count');
 approximate_row_count 
-----------------------
                     0
(1 row)

ANALYZE approx_count;
SELECT approximate_row_count('approx_count');
 approximate_row_count 
-----------------------
                    49
(1 row)

DROP TABLE approx_count;
--TEST drop_chunks from a compressed hypertable (that has caggs defined).
-- chunk metadata is still retained. verify correct status for chunk
SELECT count(compress_chunk(ch)) FROM show_chunks('metrics') ch;
 count 
-------
     2
(1 row)

SELECT drop_chunks('metrics', older_than=>'1 day'::interval);
               drop_chunks                
------------------------------------------
 _timescaledb_internal._hyper_13_33_chunk
 _timescaledb_internal._hyper_13_34_chunk
(2 rows)

SELECT
   c.table_name as chunk_name,
   c.status as chunk_status, c.dropped, c.compressed_chunk_id as comp_id
FROM _timescaledb_catalog.hypertable h, _timescaledb_catalog.chunk c
WHERE h.id = c.hypertable_id and h.table_name = 'metrics'
ORDER BY 1;
     chunk_name     | chunk_status | dropped | comp_id 
--------------------+--------------+---------+---------
 _hyper_13_33_chunk |            0 | t       |        
 _hyper_13_34_chunk |            0 | t       |        
(2 rows)

SELECT "time", cnt  FROM cagg_expr ORDER BY time LIMIT 5;
             time             | cnt  
------------------------------+------
 Fri Dec 31 16:00:00 1999 PST |  960
 Sat Jan 01 16:00:00 2000 PST | 1440
 Sun Jan 02 16:00:00 2000 PST | 1440
 Mon Jan 03 16:00:00 2000 PST | 1440
 Tue Jan 04 16:00:00 2000 PST | 1440
(5 rows)

--now reload data into the dropped chunks region, then compress
-- then verify chunk status/dropped column
INSERT INTO metrics SELECT generate_series('2000-01-01'::timestamptz,'2000-01-10','1m'),1,0.25,0.75;
SELECT count(compress_chunk(ch)) FROM show_chunks('metrics') ch;
 count 
-------
     2
(1 row)

SELECT
   c.table_name as chunk_name,
   c.status as chunk_status, c.dropped, c.compressed_chunk_id as comp_id
FROM _timescaledb_catalog.hypertable h, _timescaledb_catalog.chunk c
WHERE h.id = c.hypertable_id and h.table_name = 'metrics'
ORDER BY 1;
     chunk_name     | chunk_status | dropped | comp_id 
--------------------+--------------+---------+---------
 _hyper_13_33_chunk |            1 | f       |      64
 _hyper_13_34_chunk |            1 | f       |      65
(2 rows)

SELECT count(*) FROM metrics;
 count 
-------
 12961
(1 row)

-- test sequence number is local to segment by
CREATE TABLE local_seq(time timestamptz, device int);
SELECT table_name FROM create_hypertable('local_seq','time');
NOTICE:  adding not-null constraint to column "time"
 table_name 
------------
 local_seq
(1 row)

ALTER TABLE local_seq SET(timescaledb.compress,timescaledb.compress_segmentby='device');
INSERT INTO local_seq SELECT '2000-01-01',1 FROM generate_series(1,3000);
INSERT INTO local_seq SELECT '2000-01-01',2 FROM generate_series(1,3500);
INSERT INTO local_seq SELECT '2000-01-01',3 FROM generate_series(1,3000);
INSERT INTO local_seq SELECT '2000-01-01',4 FROM generate_series(1,3000);
INSERT INTO local_seq SELECT '2000-01-01', generate_series(5,8);
SELECT compress_chunk(c) FROM show_chunks('local_seq') c;
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_33_66_chunk
(1 row)

SELECT
	format('%s.%s',chunk.schema_name,chunk.table_name) AS "COMP_CHUNK"
FROM _timescaledb_catalog.hypertable ht
  INNER JOIN _timescaledb_catalog.hypertable ht_comp ON ht_comp.id = ht.compressed_hypertable_id
  INNER JOIN _timescaledb_catalog.chunk ON chunk.hypertable_id = ht_comp.id
WHERE ht.table_name = 'local_seq' \gset
SELECT device, _ts_meta_sequence_num, _ts_meta_count FROM :COMP_CHUNK ORDER BY 1,2;
 device | _ts_meta_sequence_num | _ts_meta_count 
--------+-----------------------+----------------
      1 |                    10 |           1000
      1 |                    20 |           1000
      1 |                    30 |           1000
      2 |                    10 |           1000
      2 |                    20 |           1000
      2 |                    30 |           1000
      2 |                    40 |            500
      3 |                    10 |           1000
      3 |                    20 |           1000
      3 |                    30 |           1000
      4 |                    10 |           1000
      4 |                    20 |           1000
      4 |                    30 |           1000
      5 |                    10 |              1
      6 |                    10 |              1
      7 |                    10 |              1
      8 |                    10 |              1
(17 rows)

-- github issue 4872
-- If subplan of ConstraintAwareAppend is TidRangeScan, then SELECT on
-- hypertable fails with error "invalid child of chunk append: Node (26)"
CREATE TABLE tidrangescan_test(time timestamptz, device_id int, v1 float, v2 float);
SELECT create_hypertable('tidrangescan_test','time');
NOTICE:  adding not-null constraint to column "time"
        create_hypertable        
---------------------------------
 (35,public,tidrangescan_test,t)
(1 row)

INSERT INTO tidrangescan_test SELECT generate_series('2000-01-01'::timestamptz,'2000-01-10','1m'),1,0.25,0.75;
CREATE MATERIALIZED VIEW tidrangescan_expr WITH (timescaledb.continuous)
 AS
 SELECT
   time_bucket('1d', time) AS time,
   'Const'::text AS Const,
   4.3::numeric AS "numeric",
   first(tidrangescan_test,time),
   CASE WHEN true THEN 'foo' ELSE 'bar' END,
   COALESCE(NULL,'coalesce'),
   avg(v1) + avg(v2) AS avg1,
   avg(v1+v2) AS avg2,
   count(*) AS cnt
 FROM tidrangescan_test
 WHERE ctid < '(1,1)'::tid GROUP BY 1 WITH NO DATA;
CALL refresh_continuous_aggregate('tidrangescan_expr', NULL, NULL);
SET timescaledb.enable_chunk_append to off;
SET enable_indexscan to off;
SELECT time, const, numeric,first, avg1, avg2 FROM tidrangescan_expr ORDER BY time LIMIT 5;
             time             | const | numeric |                    first                     | avg1 | avg2 
------------------------------+-------+---------+----------------------------------------------+------+------
 Fri Dec 31 16:00:00 1999 PST | Const |     4.3 | ("Sat Jan 01 00:00:00 2000 PST",1,0.25,0.75) |    1 |    1
 Wed Jan 05 16:00:00 2000 PST | Const |     4.3 | ("Wed Jan 05 16:00:00 2000 PST",1,0.25,0.75) |    1 |    1
(2 rows)

RESET timescaledb.enable_chunk_append;
RESET enable_indexscan;
-- Test the number of allocated parallel workers for decompression
-- Test that a parallel plan is generated
-- with different number of parallel workers
CREATE TABLE f_sensor_data(
      time timestamptz NOT NULL,
      sensor_id integer NOT NULL,
      cpu double precision NULL,
      temperature double precision NULL
    );
SELECT FROM create_hypertable('f_sensor_data','time');
--
(1 row)

SELECT set_chunk_time_interval('f_sensor_data', INTERVAL '1 year');
 set_chunk_time_interval 
-------------------------
 
(1 row)

-- Create one chunk manually to ensure, all data is inserted into one chunk
SELECT * FROM _timescaledb_functions.create_chunk('f_sensor_data',' {"time": [181900977000000, 515024000000000]}');
 chunk_id | hypertable_id |      schema_name      |     table_name     | relkind |                    slices                    | created 
----------+---------------+-----------------------+--------------------+---------+----------------------------------------------+---------
       71 |            37 | _timescaledb_internal | _hyper_37_71_chunk | r       | {"time": [181900977000000, 515024000000000]} | t
(1 row)

INSERT INTO f_sensor_data
SELECT
    time AS time,
    sensor_id,
    100.0,
    36.6
FROM
    generate_series('1980-01-01 00:00'::timestamp, '1980-02-28 12:00', INTERVAL '1 day') AS g1(time),
    generate_series(1, 1700, 1 ) AS g2(sensor_id)
ORDER BY
    time;
ALTER TABLE f_sensor_data SET (timescaledb.compress, timescaledb.compress_segmentby='sensor_id' ,timescaledb.compress_orderby = 'time DESC');
SELECT compress_chunk(i) FROM show_chunks('f_sensor_data') i;
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_37_71_chunk
(1 row)

CALL reindex_compressed_hypertable('f_sensor_data');
-- Encourage use of parallel plans
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
SET min_parallel_table_scan_size TO '0';
\set explain 'EXPLAIN (VERBOSE, COSTS OFF)'
SHOW min_parallel_table_scan_size;
 min_parallel_table_scan_size 
------------------------------
 0
(1 row)

SHOW max_parallel_workers;
 max_parallel_workers 
----------------------
 8
(1 row)

SHOW max_parallel_workers_per_gather;
 max_parallel_workers_per_gather 
---------------------------------
 2
(1 row)

SET max_parallel_workers_per_gather = 4;
SHOW max_parallel_workers_per_gather;
 max_parallel_workers_per_gather 
---------------------------------
 4
(1 row)

-- We disable enable_parallel_append here to ensure
-- that we create the same query plan in all PG 14.X versions
SET enable_parallel_append = false;
:explain
SELECT sum(cpu) FROM f_sensor_data;
                                                                                                                                                                              QUERY PLAN                                                                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate
   Output: sum(_hyper_37_71_chunk.cpu)
   ->  Gather
         Output: (PARTIAL sum(_hyper_37_71_chunk.cpu))
         Workers Planned: 4
         ->  Partial Aggregate
               Output: PARTIAL sum(_hyper_37_71_chunk.cpu)
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_37_71_chunk
                     Output: _hyper_37_71_chunk.cpu
                     ->  Parallel Seq Scan on _timescaledb_internal.compress_hyper_38_72_chunk
                           Output: compress_hyper_38_72_chunk."time", compress_hyper_38_72_chunk.sensor_id, compress_hyper_38_72_chunk.cpu, compress_hyper_38_72_chunk.temperature, compress_hyper_38_72_chunk._ts_meta_count, compress_hyper_38_72_chunk._ts_meta_sequence_num, compress_hyper_38_72_chunk._ts_meta_min_1, compress_hyper_38_72_chunk._ts_meta_max_1
(11 rows)

-- Encourage use of Index Scan
SET enable_seqscan = false;
SET enable_indexscan = true;
SET min_parallel_index_scan_size = 0;
SET min_parallel_table_scan_size = 0;
CREATE INDEX ON f_sensor_data (time, sensor_id);
:explain
SELECT * FROM f_sensor_data WHERE sensor_id > 100;
                                                                                                                                                                        QUERY PLAN                                                                                                                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather
   Output: _hyper_37_71_chunk."time", _hyper_37_71_chunk.sensor_id, _hyper_37_71_chunk.cpu, _hyper_37_71_chunk.temperature
   Workers Planned: 2
   ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_37_71_chunk
         Output: _hyper_37_71_chunk."time", _hyper_37_71_chunk.sensor_id, _hyper_37_71_chunk.cpu, _hyper_37_71_chunk.temperature
         ->  Parallel Index Scan using compress_hyper_38_72_chunk__compressed_hypertable_38_sensor_id_ on _timescaledb_internal.compress_hyper_38_72_chunk
               Output: compress_hyper_38_72_chunk."time", compress_hyper_38_72_chunk.sensor_id, compress_hyper_38_72_chunk.cpu, compress_hyper_38_72_chunk.temperature, compress_hyper_38_72_chunk._ts_meta_count, compress_hyper_38_72_chunk._ts_meta_sequence_num, compress_hyper_38_72_chunk._ts_meta_min_1, compress_hyper_38_72_chunk._ts_meta_max_1
               Index Cond: (compress_hyper_38_72_chunk.sensor_id > 100)
(8 rows)

RESET enable_parallel_append;
-- Test for partially compressed chunks
INSERT INTO f_sensor_data
SELECT
    time AS time,
    sensor_id,
    100.0,
    36.6
FROM
    generate_series('1980-01-01 00:00'::timestamp, '1980-01-30 12:00', INTERVAL '1 day') AS g1(time),
    generate_series(1700, 1800, 1 ) AS g2(sensor_id)
ORDER BY
    time;
:explain
SELECT sum(cpu) FROM f_sensor_data;
                                                                                                                                                                                 QUERY PLAN                                                                                                                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate
   Output: sum(_hyper_37_71_chunk.cpu)
   ->  Gather
         Output: (PARTIAL sum(_hyper_37_71_chunk.cpu))
         Workers Planned: 4
         ->  Parallel Append
               ->  Partial Aggregate
                     Output: PARTIAL sum(_hyper_37_71_chunk.cpu)
                     ->  Parallel Seq Scan on _timescaledb_internal._hyper_37_71_chunk
                           Output: _hyper_37_71_chunk.cpu
               ->  Partial Aggregate
                     Output: PARTIAL sum(_hyper_37_71_chunk.cpu)
                     ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_37_71_chunk
                           Output: _hyper_37_71_chunk.cpu
                           ->  Parallel Seq Scan on _timescaledb_internal.compress_hyper_38_72_chunk
                                 Output: compress_hyper_38_72_chunk."time", compress_hyper_38_72_chunk.sensor_id, compress_hyper_38_72_chunk.cpu, compress_hyper_38_72_chunk.temperature, compress_hyper_38_72_chunk._ts_meta_count, compress_hyper_38_72_chunk._ts_meta_sequence_num, compress_hyper_38_72_chunk._ts_meta_min_1, compress_hyper_38_72_chunk._ts_meta_max_1
(16 rows)

:explain
SELECT * FROM f_sensor_data WHERE sensor_id > 100;
                                                                                                                                                                           QUERY PLAN                                                                                                                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather
   Output: _hyper_37_71_chunk."time", _hyper_37_71_chunk.sensor_id, _hyper_37_71_chunk.cpu, _hyper_37_71_chunk.temperature
   Workers Planned: 3
   ->  Parallel Append
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_37_71_chunk
               Output: _hyper_37_71_chunk."time", _hyper_37_71_chunk.sensor_id, _hyper_37_71_chunk.cpu, _hyper_37_71_chunk.temperature
               Filter: (_hyper_37_71_chunk.sensor_id > 100)
               ->  Parallel Index Scan using compress_hyper_38_72_chunk__compressed_hypertable_38_sensor_id_ on _timescaledb_internal.compress_hyper_38_72_chunk
                     Output: compress_hyper_38_72_chunk."time", compress_hyper_38_72_chunk.sensor_id, compress_hyper_38_72_chunk.cpu, compress_hyper_38_72_chunk.temperature, compress_hyper_38_72_chunk._ts_meta_count, compress_hyper_38_72_chunk._ts_meta_sequence_num, compress_hyper_38_72_chunk._ts_meta_min_1, compress_hyper_38_72_chunk._ts_meta_max_1
                     Index Cond: (compress_hyper_38_72_chunk.sensor_id > 100)
         ->  Parallel Index Scan using _hyper_37_71_chunk_f_sensor_data_time_sensor_id_idx on _timescaledb_internal._hyper_37_71_chunk
               Output: _hyper_37_71_chunk."time", _hyper_37_71_chunk.sensor_id, _hyper_37_71_chunk.cpu, _hyper_37_71_chunk.temperature
               Index Cond: (_hyper_37_71_chunk.sensor_id > 100)
(13 rows)

-- Test non-partial paths below append are not executed multiple times
CREATE TABLE ts_device_table(time INTEGER, device INTEGER, location INTEGER, value INTEGER);
CREATE UNIQUE INDEX device_time_idx on ts_device_table(time, device);
SELECT create_hypertable('ts_device_table', 'time', chunk_time_interval => 1000);
NOTICE:  adding not-null constraint to column "time"
       create_hypertable       
-------------------------------
 (39,public,ts_device_table,t)
(1 row)

INSERT INTO ts_device_table SELECT generate_series(0,999,1), 1, 100, 20;
ALTER TABLE ts_device_table set(timescaledb.compress, timescaledb.compress_segmentby='location', timescaledb.compress_orderby='time');
WARNING:  column "device" should be used for segmenting or ordering
SELECT compress_chunk(i) AS chunk_name FROM show_chunks('ts_device_table') i \gset
SELECT count(*) FROM ts_device_table;
 count 
-------
  1000
(1 row)

SELECT count(*) FROM :chunk_name;
 count 
-------
  1000
(1 row)

INSERT INTO ts_device_table VALUES (1, 1, 100, 100) ON CONFLICT DO NOTHING;
SELECT count(*) FROM :chunk_name;
 count 
-------
  1000
(1 row)

SET parallel_setup_cost TO '0';
SET parallel_tuple_cost TO '0';
SET min_parallel_table_scan_size TO '8';
SET min_parallel_index_scan_size TO '8';
SET random_page_cost TO '0';
SELECT count(*) FROM :chunk_name;
 count 
-------
  1000
(1 row)

ANALYZE :chunk_name;
SELECT count(*) FROM :chunk_name;
 count 
-------
  1000
(1 row)

-- Test that parallel plans are chosen even if partial and small chunks are involved
RESET min_parallel_index_scan_size;
RESET min_parallel_table_scan_size;
CREATE TABLE ht_metrics_partially_compressed(time timestamptz, device int, value float);
SELECT create_hypertable('ht_metrics_partially_compressed','time',create_default_indexes:=false);
NOTICE:  adding not-null constraint to column "time"
               create_hypertable               
-----------------------------------------------
 (41,public,ht_metrics_partially_compressed,t)
(1 row)

ALTER TABLE ht_metrics_partially_compressed SET (timescaledb.compress, timescaledb.compress_segmentby='device');
INSERT INTO ht_metrics_partially_compressed
SELECT time, device, device * 0.1 FROM
   generate_series('2020-01-01'::timestamptz,'2020-01-02'::timestamptz, INTERVAL '1 m') g(time),
   LATERAL (SELECT generate_series(1,2) AS device) g2;
SELECT compress_chunk(c) FROM show_chunks('ht_metrics_partially_compressed') c;
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_41_75_chunk
 _timescaledb_internal._hyper_41_76_chunk
(2 rows)

INSERT INTO ht_metrics_partially_compressed VALUES ('2020-01-01'::timestamptz, 1, 0.1);
:explain
SELECT * FROM ht_metrics_partially_compressed ORDER BY time DESC, device LIMIT 1;
                                                                                                                                                            QUERY PLAN                                                                                                                                                             
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit
   Output: ht_metrics_partially_compressed."time", ht_metrics_partially_compressed.device, ht_metrics_partially_compressed.value
   ->  Custom Scan (ChunkAppend) on public.ht_metrics_partially_compressed
         Output: ht_metrics_partially_compressed."time", ht_metrics_partially_compressed.device, ht_metrics_partially_compressed.value
         Order: ht_metrics_partially_compressed."time" DESC, ht_metrics_partially_compressed.device
         Startup Exclusion: false
         Runtime Exclusion: false
         ->  Sort
               Output: _hyper_41_76_chunk."time", _hyper_41_76_chunk.device, _hyper_41_76_chunk.value
               Sort Key: _hyper_41_76_chunk."time" DESC, _hyper_41_76_chunk.device
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_41_76_chunk
                     Output: _hyper_41_76_chunk."time", _hyper_41_76_chunk.device, _hyper_41_76_chunk.value
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_42_78_chunk
                           Output: compress_hyper_42_78_chunk."time", compress_hyper_42_78_chunk.device, compress_hyper_42_78_chunk.value, compress_hyper_42_78_chunk._ts_meta_count, compress_hyper_42_78_chunk._ts_meta_sequence_num, compress_hyper_42_78_chunk._ts_meta_min_1, compress_hyper_42_78_chunk._ts_meta_max_1
         ->  Merge Append
               Sort Key: _hyper_41_75_chunk."time" DESC, _hyper_41_75_chunk.device
               ->  Sort
                     Output: _hyper_41_75_chunk."time", _hyper_41_75_chunk.device, _hyper_41_75_chunk.value
                     Sort Key: _hyper_41_75_chunk."time" DESC, _hyper_41_75_chunk.device
                     ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_41_75_chunk
                           Output: _hyper_41_75_chunk."time", _hyper_41_75_chunk.device, _hyper_41_75_chunk.value
                           ->  Seq Scan on _timescaledb_internal.compress_hyper_42_77_chunk
                                 Output: compress_hyper_42_77_chunk."time", compress_hyper_42_77_chunk.device, compress_hyper_42_77_chunk.value, compress_hyper_42_77_chunk._ts_meta_count, compress_hyper_42_77_chunk._ts_meta_sequence_num, compress_hyper_42_77_chunk._ts_meta_min_1, compress_hyper_42_77_chunk._ts_meta_max_1
               ->  Sort
                     Output: _hyper_41_75_chunk."time", _hyper_41_75_chunk.device, _hyper_41_75_chunk.value
                     Sort Key: _hyper_41_75_chunk."time" DESC, _hyper_41_75_chunk.device
                     ->  Seq Scan on _timescaledb_internal._hyper_41_75_chunk
                           Output: _hyper_41_75_chunk."time", _hyper_41_75_chunk.device, _hyper_41_75_chunk.value
(28 rows)

-- Test parameter change on rescan
-- issue 6069
CREATE TABLE IF NOT EXISTS i6069 (
	timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
	attr_id SMALLINT NOT NULL,
	number_val DOUBLE PRECISION DEFAULT NULL
);
SELECT table_name FROM create_hypertable(
	'i6069', 'timestamp',
	create_default_indexes => FALSE, if_not_exists => TRUE, chunk_time_interval => INTERVAL '1 day'
);
WARNING:  column type "timestamp without time zone" used for "timestamp" does not follow best practices
 table_name 
------------
 i6069
(1 row)

ALTER TABLE i6069 SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'attr_id'
);
INSERT INTO i6069 VALUES('2023-07-01', 1, 1),('2023-07-03', 2, 1),('2023-07-05', 3, 1),
	('2023-07-01', 4, 1),('2023-07-03', 5, 1),('2023-07-05', 6, 1),
	('2023-07-01', 7, 1),('2023-07-03', 8, 1),('2023-07-05', 9, 1),
	('2023-07-01', 10, 1),('2023-07-03', 11, 1),('2023-07-05', 12, 1),
	('2023-07-01', 13, 1),('2023-07-03', 14, 1),('2023-07-05', 15, 1),
	('2023-07-01', 16, 1),('2023-07-03', 17, 1),('2023-07-05', 18, 1),
	('2023-07-01', 19, 1),('2023-07-03', 20, 1),('2023-07-05', 21, 1),
	('2023-07-01', 22, 1),('2023-07-03', 23, 1),('2023-07-05', 24, 1),
	('2023-07-01', 25, 1),('2023-07-03', 26, 1),('2023-07-05', 27, 1),
	('2023-07-01', 28, 1),('2023-07-03', 29, 1),('2023-07-05', 30, 1),
	('2023-07-01', 31, 1),('2023-07-03', 32, 1),('2023-07-05', 33, 1),
	('2023-07-01', 34, 1),('2023-07-03', 35, 1),('2023-07-05', 36, 1),
	('2023-07-01', 37, 1),('2023-07-03', 38, 1),('2023-07-05', 39, 1),
	('2023-07-01', 40, 1),('2023-07-03', 41, 1),('2023-07-05', 42, 1),
	('2023-07-01', 43, 1),('2023-07-03', 44, 1),('2023-07-05', 45, 1),
	('2023-07-01', 46, 1),('2023-07-03', 47, 1),('2023-07-05', 48, 1),
	('2023-07-01', 49, 1),('2023-07-03', 50, 1),('2023-07-05', 51, 1),
	('2023-07-01', 52, 1),('2023-07-03', 53, 1),('2023-07-05', 54, 1),
	('2023-07-01', 55, 1),('2023-07-03', 56, 1),('2023-07-05', 57, 1),
	('2023-07-01', 58, 1),('2023-07-03', 59, 1),('2023-07-05', 60, 1),
	('2023-07-01', 61, 1),('2023-07-03', 62, 1),('2023-07-05', 63, 1),
	('2023-07-01', 64, 1),('2023-07-03', 65, 1),('2023-07-05', 66, 1),
	('2023-07-01', 67, 1),('2023-07-03', 68, 1),('2023-07-05', 69, 1),
	('2023-07-01', 70, 1),('2023-07-03', 71, 1),('2023-07-05', 72, 1),
	('2023-07-01', 73, 1),('2023-07-03', 74, 1),('2023-07-05', 75, 1),
	('2023-07-01', 76, 1),('2023-07-03', 77, 1),('2023-07-05', 78, 1),
	('2023-07-01', 79, 1),('2023-07-03', 80, 1),('2023-07-05', 81, 1),
	('2023-07-01', 82, 1),('2023-07-03', 83, 1),('2023-07-05', 84, 1),
	('2023-07-01', 85, 1),('2023-07-03', 86, 1),('2023-07-05', 87, 1),
	('2023-07-01', 88, 1),('2023-07-03', 89, 1),('2023-07-05', 90, 1),
	('2023-07-01', 91, 1),('2023-07-03', 92, 1),('2023-07-05', 93, 1),
	('2023-07-01', 94, 1),('2023-07-03', 95, 1),('2023-07-05', 96, 1),
	('2023-07-01', 97, 1),('2023-07-03', 98, 1),('2023-07-05', 99, 1),
	('2023-07-01', 100, 1),('2023-07-03', 101, 1),('2023-07-05', 102, 1),
	('2023-07-01', 103, 1),('2023-07-03', 104, 1),('2023-07-05', 105, 1),
	('2023-07-01', 106, 1),('2023-07-03', 107, 1),('2023-07-05', 108, 1),
	('2023-07-01', 109, 1),('2023-07-03', 110, 1),('2023-07-05', 111, 1),
	('2023-07-01', 112, 1),('2023-07-03', 113, 1),('2023-07-05', 114, 1),
	('2023-07-01', 115, 1),('2023-07-03', 116, 1),('2023-07-05', 117, 1),
	('2023-07-01', 118, 1),('2023-07-03', 119, 1),('2023-07-05', 120, 1);
SELECT compress_chunk(i, if_not_compressed => true) FROM show_chunks('i6069') i;
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_43_79_chunk
 _timescaledb_internal._hyper_43_80_chunk
 _timescaledb_internal._hyper_43_81_chunk
(3 rows)

SET enable_indexscan = ON;
SET enable_seqscan = OFF;
:explain
SELECT * FROM ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10) ) AS attr_ids(attr_id)
INNER JOIN LATERAL (
  SELECT * FROM i6069
  WHERE i6069.attr_id = attr_ids.attr_id AND
    timestamp > '2023-06-30' AND timestamp < '2023-07-06'
ORDER BY timestamp desc  LIMIT 1 ) a ON true;
                                                                                                                                                                  QUERY PLAN                                                                                                                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop
   Output: "*VALUES*".column1, i6069."timestamp", i6069.attr_id, i6069.number_val
   ->  Values Scan on "*VALUES*"
         Output: "*VALUES*".column1
   ->  Limit
         Output: i6069."timestamp", i6069.attr_id, i6069.number_val
         ->  Custom Scan (ChunkAppend) on public.i6069
               Output: i6069."timestamp", i6069.attr_id, i6069.number_val
               Order: i6069."timestamp" DESC
               Startup Exclusion: false
               Runtime Exclusion: true
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_43_81_chunk
                     Output: _hyper_43_81_chunk."timestamp", _hyper_43_81_chunk.attr_id, _hyper_43_81_chunk.number_val
                     Filter: ((_hyper_43_81_chunk."timestamp" > 'Fri Jun 30 00:00:00 2023'::timestamp without time zone) AND (_hyper_43_81_chunk."timestamp" < 'Thu Jul 06 00:00:00 2023'::timestamp without time zone))
                     Sorted merge append: true
                     ->  Sort
                           Output: compress_hyper_44_84_chunk."timestamp", compress_hyper_44_84_chunk.attr_id, compress_hyper_44_84_chunk.number_val, compress_hyper_44_84_chunk._ts_meta_count, compress_hyper_44_84_chunk._ts_meta_sequence_num, compress_hyper_44_84_chunk._ts_meta_min_1, compress_hyper_44_84_chunk._ts_meta_max_1
                           Sort Key: compress_hyper_44_84_chunk._ts_meta_max_1 DESC
                           ->  Index Scan using compress_hyper_44_84_chunk__compressed_hypertable_44_attr_id__t on _timescaledb_internal.compress_hyper_44_84_chunk
                                 Output: compress_hyper_44_84_chunk."timestamp", compress_hyper_44_84_chunk.attr_id, compress_hyper_44_84_chunk.number_val, compress_hyper_44_84_chunk._ts_meta_count, compress_hyper_44_84_chunk._ts_meta_sequence_num, compress_hyper_44_84_chunk._ts_meta_min_1, compress_hyper_44_84_chunk._ts_meta_max_1
                                 Index Cond: (compress_hyper_44_84_chunk.attr_id = "*VALUES*".column1)
                                 Filter: ((compress_hyper_44_84_chunk._ts_meta_max_1 > 'Fri Jun 30 00:00:00 2023'::timestamp without time zone) AND (compress_hyper_44_84_chunk._ts_meta_min_1 < 'Thu Jul 06 00:00:00 2023'::timestamp without time zone))
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_43_80_chunk
                     Output: _hyper_43_80_chunk."timestamp", _hyper_43_80_chunk.attr_id, _hyper_43_80_chunk.number_val
                     Filter: ((_hyper_43_80_chunk."timestamp" > 'Fri Jun 30 00:00:00 2023'::timestamp without time zone) AND (_hyper_43_80_chunk."timestamp" < 'Thu Jul 06 00:00:00 2023'::timestamp without time zone))
                     Sorted merge append: true
                     ->  Sort
                           Output: compress_hyper_44_83_chunk."timestamp", compress_hyper_44_83_chunk.attr_id, compress_hyper_44_83_chunk.number_val, compress_hyper_44_83_chunk._ts_meta_count, compress_hyper_44_83_chunk._ts_meta_sequence_num, compress_hyper_44_83_chunk._ts_meta_min_1, compress_hyper_44_83_chunk._ts_meta_max_1
                           Sort Key: compress_hyper_44_83_chunk._ts_meta_max_1 DESC
                           ->  Index Scan using compress_hyper_44_83_chunk__compressed_hypertable_44_attr_id__t on _timescaledb_internal.compress_hyper_44_83_chunk
                                 Output: compress_hyper_44_83_chunk."timestamp", compress_hyper_44_83_chunk.attr_id, compress_hyper_44_83_chunk.number_val, compress_hyper_44_83_chunk._ts_meta_count, compress_hyper_44_83_chunk._ts_meta_sequence_num, compress_hyper_44_83_chunk._ts_meta_min_1, compress_hyper_44_83_chunk._ts_meta_max_1
                                 Index Cond: (compress_hyper_44_83_chunk.attr_id = "*VALUES*".column1)
                                 Filter: ((compress_hyper_44_83_chunk._ts_meta_max_1 > 'Fri Jun 30 00:00:00 2023'::timestamp without time zone) AND (compress_hyper_44_83_chunk._ts_meta_min_1 < 'Thu Jul 06 00:00:00 2023'::timestamp without time zone))
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_43_79_chunk
                     Output: _hyper_43_79_chunk."timestamp", _hyper_43_79_chunk.attr_id, _hyper_43_79_chunk.number_val
                     Filter: ((_hyper_43_79_chunk."timestamp" > 'Fri Jun 30 00:00:00 2023'::timestamp without time zone) AND (_hyper_43_79_chunk."timestamp" < 'Thu Jul 06 00:00:00 2023'::timestamp without time zone))
                     Sorted merge append: true
                     ->  Sort
                           Output: compress_hyper_44_82_chunk."timestamp", compress_hyper_44_82_chunk.attr_id, compress_hyper_44_82_chunk.number_val, compress_hyper_44_82_chunk._ts_meta_count, compress_hyper_44_82_chunk._ts_meta_sequence_num, compress_hyper_44_82_chunk._ts_meta_min_1, compress_hyper_44_82_chunk._ts_meta_max_1
                           Sort Key: compress_hyper_44_82_chunk._ts_meta_max_1 DESC
                           ->  Index Scan using compress_hyper_44_82_chunk__compressed_hypertable_44_attr_id__t on _timescaledb_internal.compress_hyper_44_82_chunk
                                 Output: compress_hyper_44_82_chunk."timestamp", compress_hyper_44_82_chunk.attr_id, compress_hyper_44_82_chunk.number_val, compress_hyper_44_82_chunk._ts_meta_count, compress_hyper_44_82_chunk._ts_meta_sequence_num, compress_hyper_44_82_chunk._ts_meta_min_1, compress_hyper_44_82_chunk._ts_meta_max_1
                                 Index Cond: (compress_hyper_44_82_chunk.attr_id = "*VALUES*".column1)
                                 Filter: ((compress_hyper_44_82_chunk._ts_meta_max_1 > 'Fri Jun 30 00:00:00 2023'::timestamp without time zone) AND (compress_hyper_44_82_chunk._ts_meta_min_1 < 'Thu Jul 06 00:00:00 2023'::timestamp without time zone))
(44 rows)

SELECT * FROM ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10) ) AS attr_ids(attr_id)
INNER JOIN LATERAL (
  SELECT * FROM i6069
  WHERE i6069.attr_id = attr_ids.attr_id AND
    timestamp > '2023-06-30' AND timestamp < '2023-07-06'
ORDER BY timestamp desc  LIMIT 1 ) a ON true;
 attr_id |        timestamp         | attr_id | number_val 
---------+--------------------------+---------+------------
       1 | Sat Jul 01 00:00:00 2023 |       1 |          1
       2 | Mon Jul 03 00:00:00 2023 |       2 |          1
       3 | Wed Jul 05 00:00:00 2023 |       3 |          1
       4 | Sat Jul 01 00:00:00 2023 |       4 |          1
       5 | Mon Jul 03 00:00:00 2023 |       5 |          1
       6 | Wed Jul 05 00:00:00 2023 |       6 |          1
       7 | Sat Jul 01 00:00:00 2023 |       7 |          1
       8 | Mon Jul 03 00:00:00 2023 |       8 |          1
       9 | Wed Jul 05 00:00:00 2023 |       9 |          1
      10 | Sat Jul 01 00:00:00 2023 |      10 |          1
(10 rows)

RESET enable_indexscan;
RESET enable_seqscan;
-- When all chunks are compressed and a limit query is performed, only the needed
-- chunks should be accessed
CREATE TABLE sensor_data_compressed (
time timestamptz not null,
sensor_id integer not null,
cpu double precision null,
temperature double precision null);
SELECT FROM create_hypertable('sensor_data_compressed', 'time');
--
(1 row)

INSERT INTO sensor_data_compressed (time, sensor_id, cpu, temperature)
   VALUES
   ('1980-01-02 00:00:00-00', 1, 3, 12.0),
   ('1980-01-03 00:00:00-00', 3, 4, 15.0),
   ('1980-02-04 00:00:00-00', 1, 2, 17.0),
   ('1980-02-05 00:00:00-00', 3, 6, 11.0),
   ('1980-03-06 00:00:00-00', 1, 8, 13.0),
   ('1980-03-07 00:00:00-00', 3, 4, 4.0),
   ('1980-04-08 00:00:00-00', 1, 2, 1.0),
   ('1980-04-03 00:00:00-00', 3, 5, 33.0),
   ('1980-05-02 00:00:00-00', 1, 8, 41.0),
   ('1980-05-03 00:00:00-00', 3, 4, 22.0),
   ('1980-06-02 00:00:00-00', 1, 1, 45.0),
   ('1980-06-03 00:00:00-00', 3, 3, 44.0);
ALTER TABLE sensor_data_compressed SET (timescaledb.compress, timescaledb.compress_segmentby='sensor_id', timescaledb.compress_orderby = 'time DESC');
-- Compress three of the chunks
SELECT compress_chunk(ch) FROM show_chunks('sensor_data_compressed') ch LIMIT 3;
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_45_85_chunk
 _timescaledb_internal._hyper_45_86_chunk
 _timescaledb_internal._hyper_45_87_chunk
(3 rows)

ANALYZE sensor_data_compressed;
SELECT * FROM sensor_data_compressed ORDER BY time DESC LIMIT 5;
             time             | sensor_id | cpu | temperature 
------------------------------+-----------+-----+-------------
 Mon Jun 02 17:00:00 1980 PDT |         3 |   3 |          44
 Sun Jun 01 17:00:00 1980 PDT |         1 |   1 |          45
 Fri May 02 17:00:00 1980 PDT |         3 |   4 |          22
 Thu May 01 17:00:00 1980 PDT |         1 |   8 |          41
 Mon Apr 07 16:00:00 1980 PST |         1 |   2 |           1
(5 rows)

-- Only the first chunks should be accessed (sorted merge append is enabled)
:PREFIX
SELECT * FROM sensor_data_compressed ORDER BY time DESC LIMIT 5;
                                                                                                                                                                              QUERY PLAN                                                                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual rows=5 loops=1)
   Output: sensor_data_compressed."time", sensor_data_compressed.sensor_id, sensor_data_compressed.cpu, sensor_data_compressed.temperature
   ->  Custom Scan (ChunkAppend) on public.sensor_data_compressed (actual rows=5 loops=1)
         Output: sensor_data_compressed."time", sensor_data_compressed.sensor_id, sensor_data_compressed.cpu, sensor_data_compressed.temperature
         Order: sensor_data_compressed."time" DESC
         Startup Exclusion: false
         Runtime Exclusion: false
         ->  Index Scan using _hyper_45_91_chunk_sensor_data_compressed_time_idx on _timescaledb_internal._hyper_45_91_chunk (actual rows=2 loops=1)
               Output: _hyper_45_91_chunk."time", _hyper_45_91_chunk.sensor_id, _hyper_45_91_chunk.cpu, _hyper_45_91_chunk.temperature
         ->  Index Scan using _hyper_45_90_chunk_sensor_data_compressed_time_idx on _timescaledb_internal._hyper_45_90_chunk (actual rows=2 loops=1)
               Output: _hyper_45_90_chunk."time", _hyper_45_90_chunk.sensor_id, _hyper_45_90_chunk.cpu, _hyper_45_90_chunk.temperature
         ->  Index Scan using _hyper_45_89_chunk_sensor_data_compressed_time_idx on _timescaledb_internal._hyper_45_89_chunk (actual rows=1 loops=1)
               Output: _hyper_45_89_chunk."time", _hyper_45_89_chunk.sensor_id, _hyper_45_89_chunk.cpu, _hyper_45_89_chunk.temperature
         ->  Index Scan using _hyper_45_88_chunk_sensor_data_compressed_time_idx on _timescaledb_internal._hyper_45_88_chunk (never executed)
               Output: _hyper_45_88_chunk."time", _hyper_45_88_chunk.sensor_id, _hyper_45_88_chunk.cpu, _hyper_45_88_chunk.temperature
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_87_chunk (never executed)
               Output: _hyper_45_87_chunk."time", _hyper_45_87_chunk.sensor_id, _hyper_45_87_chunk.cpu, _hyper_45_87_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_46_94_chunk."time", compress_hyper_46_94_chunk.sensor_id, compress_hyper_46_94_chunk.cpu, compress_hyper_46_94_chunk.temperature, compress_hyper_46_94_chunk._ts_meta_count, compress_hyper_46_94_chunk._ts_meta_sequence_num, compress_hyper_46_94_chunk._ts_meta_min_1, compress_hyper_46_94_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_94_chunk._ts_meta_max_1 DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_94_chunk (never executed)
                           Output: compress_hyper_46_94_chunk."time", compress_hyper_46_94_chunk.sensor_id, compress_hyper_46_94_chunk.cpu, compress_hyper_46_94_chunk.temperature, compress_hyper_46_94_chunk._ts_meta_count, compress_hyper_46_94_chunk._ts_meta_sequence_num, compress_hyper_46_94_chunk._ts_meta_min_1, compress_hyper_46_94_chunk._ts_meta_max_1
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_86_chunk (never executed)
               Output: _hyper_45_86_chunk."time", _hyper_45_86_chunk.sensor_id, _hyper_45_86_chunk.cpu, _hyper_45_86_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_46_93_chunk."time", compress_hyper_46_93_chunk.sensor_id, compress_hyper_46_93_chunk.cpu, compress_hyper_46_93_chunk.temperature, compress_hyper_46_93_chunk._ts_meta_count, compress_hyper_46_93_chunk._ts_meta_sequence_num, compress_hyper_46_93_chunk._ts_meta_min_1, compress_hyper_46_93_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_93_chunk._ts_meta_max_1 DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_93_chunk (never executed)
                           Output: compress_hyper_46_93_chunk."time", compress_hyper_46_93_chunk.sensor_id, compress_hyper_46_93_chunk.cpu, compress_hyper_46_93_chunk.temperature, compress_hyper_46_93_chunk._ts_meta_count, compress_hyper_46_93_chunk._ts_meta_sequence_num, compress_hyper_46_93_chunk._ts_meta_min_1, compress_hyper_46_93_chunk._ts_meta_max_1
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_85_chunk (never executed)
               Output: _hyper_45_85_chunk."time", _hyper_45_85_chunk.sensor_id, _hyper_45_85_chunk.cpu, _hyper_45_85_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_46_92_chunk."time", compress_hyper_46_92_chunk.sensor_id, compress_hyper_46_92_chunk.cpu, compress_hyper_46_92_chunk.temperature, compress_hyper_46_92_chunk._ts_meta_count, compress_hyper_46_92_chunk._ts_meta_sequence_num, compress_hyper_46_92_chunk._ts_meta_min_1, compress_hyper_46_92_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_92_chunk._ts_meta_max_1 DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_92_chunk (never executed)
                           Output: compress_hyper_46_92_chunk."time", compress_hyper_46_92_chunk.sensor_id, compress_hyper_46_92_chunk.cpu, compress_hyper_46_92_chunk.temperature, compress_hyper_46_92_chunk._ts_meta_count, compress_hyper_46_92_chunk._ts_meta_sequence_num, compress_hyper_46_92_chunk._ts_meta_min_1, compress_hyper_46_92_chunk._ts_meta_max_1
(42 rows)

-- Only the first chunks should be accessed (sorted merge append is disabled)
SET timescaledb.enable_decompression_sorted_merge = FALSE;
:PREFIX
SELECT * FROM sensor_data_compressed ORDER BY time DESC LIMIT 5;
                                                                                                                                                                              QUERY PLAN                                                                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual rows=5 loops=1)
   Output: sensor_data_compressed."time", sensor_data_compressed.sensor_id, sensor_data_compressed.cpu, sensor_data_compressed.temperature
   ->  Custom Scan (ChunkAppend) on public.sensor_data_compressed (actual rows=5 loops=1)
         Output: sensor_data_compressed."time", sensor_data_compressed.sensor_id, sensor_data_compressed.cpu, sensor_data_compressed.temperature
         Order: sensor_data_compressed."time" DESC
         Startup Exclusion: false
         Runtime Exclusion: false
         ->  Index Scan using _hyper_45_91_chunk_sensor_data_compressed_time_idx on _timescaledb_internal._hyper_45_91_chunk (actual rows=2 loops=1)
               Output: _hyper_45_91_chunk."time", _hyper_45_91_chunk.sensor_id, _hyper_45_91_chunk.cpu, _hyper_45_91_chunk.temperature
         ->  Index Scan using _hyper_45_90_chunk_sensor_data_compressed_time_idx on _timescaledb_internal._hyper_45_90_chunk (actual rows=2 loops=1)
               Output: _hyper_45_90_chunk."time", _hyper_45_90_chunk.sensor_id, _hyper_45_90_chunk.cpu, _hyper_45_90_chunk.temperature
         ->  Index Scan using _hyper_45_89_chunk_sensor_data_compressed_time_idx on _timescaledb_internal._hyper_45_89_chunk (actual rows=1 loops=1)
               Output: _hyper_45_89_chunk."time", _hyper_45_89_chunk.sensor_id, _hyper_45_89_chunk.cpu, _hyper_45_89_chunk.temperature
         ->  Index Scan using _hyper_45_88_chunk_sensor_data_compressed_time_idx on _timescaledb_internal._hyper_45_88_chunk (never executed)
               Output: _hyper_45_88_chunk."time", _hyper_45_88_chunk.sensor_id, _hyper_45_88_chunk.cpu, _hyper_45_88_chunk.temperature
         ->  Sort (never executed)
               Output: _hyper_45_87_chunk."time", _hyper_45_87_chunk.sensor_id, _hyper_45_87_chunk.cpu, _hyper_45_87_chunk.temperature
               Sort Key: _hyper_45_87_chunk."time" DESC
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_87_chunk (never executed)
                     Output: _hyper_45_87_chunk."time", _hyper_45_87_chunk.sensor_id, _hyper_45_87_chunk.cpu, _hyper_45_87_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_94_chunk (never executed)
                           Output: compress_hyper_46_94_chunk."time", compress_hyper_46_94_chunk.sensor_id, compress_hyper_46_94_chunk.cpu, compress_hyper_46_94_chunk.temperature, compress_hyper_46_94_chunk._ts_meta_count, compress_hyper_46_94_chunk._ts_meta_sequence_num, compress_hyper_46_94_chunk._ts_meta_min_1, compress_hyper_46_94_chunk._ts_meta_max_1
         ->  Sort (never executed)
               Output: _hyper_45_86_chunk."time", _hyper_45_86_chunk.sensor_id, _hyper_45_86_chunk.cpu, _hyper_45_86_chunk.temperature
               Sort Key: _hyper_45_86_chunk."time" DESC
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_86_chunk (never executed)
                     Output: _hyper_45_86_chunk."time", _hyper_45_86_chunk.sensor_id, _hyper_45_86_chunk.cpu, _hyper_45_86_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_93_chunk (never executed)
                           Output: compress_hyper_46_93_chunk."time", compress_hyper_46_93_chunk.sensor_id, compress_hyper_46_93_chunk.cpu, compress_hyper_46_93_chunk.temperature, compress_hyper_46_93_chunk._ts_meta_count, compress_hyper_46_93_chunk._ts_meta_sequence_num, compress_hyper_46_93_chunk._ts_meta_min_1, compress_hyper_46_93_chunk._ts_meta_max_1
         ->  Sort (never executed)
               Output: _hyper_45_85_chunk."time", _hyper_45_85_chunk.sensor_id, _hyper_45_85_chunk.cpu, _hyper_45_85_chunk.temperature
               Sort Key: _hyper_45_85_chunk."time" DESC
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_85_chunk (never executed)
                     Output: _hyper_45_85_chunk."time", _hyper_45_85_chunk.sensor_id, _hyper_45_85_chunk.cpu, _hyper_45_85_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_92_chunk (never executed)
                           Output: compress_hyper_46_92_chunk."time", compress_hyper_46_92_chunk.sensor_id, compress_hyper_46_92_chunk.cpu, compress_hyper_46_92_chunk.temperature, compress_hyper_46_92_chunk._ts_meta_count, compress_hyper_46_92_chunk._ts_meta_sequence_num, compress_hyper_46_92_chunk._ts_meta_min_1, compress_hyper_46_92_chunk._ts_meta_max_1
(39 rows)

RESET timescaledb.enable_decompression_sorted_merge;
-- Compress the remaining chunks
SELECT compress_chunk(ch, if_not_compressed => true) FROM show_chunks('sensor_data_compressed') ch;
NOTICE:  chunk "_hyper_45_85_chunk" is already compressed
NOTICE:  chunk "_hyper_45_86_chunk" is already compressed
NOTICE:  chunk "_hyper_45_87_chunk" is already compressed
              compress_chunk              
------------------------------------------
 _timescaledb_internal._hyper_45_85_chunk
 _timescaledb_internal._hyper_45_86_chunk
 _timescaledb_internal._hyper_45_87_chunk
 _timescaledb_internal._hyper_45_88_chunk
 _timescaledb_internal._hyper_45_89_chunk
 _timescaledb_internal._hyper_45_90_chunk
 _timescaledb_internal._hyper_45_91_chunk
(7 rows)

SELECT * FROM sensor_data_compressed ORDER BY time DESC LIMIT 5;
             time             | sensor_id | cpu | temperature 
------------------------------+-----------+-----+-------------
 Mon Jun 02 17:00:00 1980 PDT |         3 |   3 |          44
 Sun Jun 01 17:00:00 1980 PDT |         1 |   1 |          45
 Fri May 02 17:00:00 1980 PDT |         3 |   4 |          22
 Thu May 01 17:00:00 1980 PDT |         1 |   8 |          41
 Mon Apr 07 16:00:00 1980 PST |         1 |   2 |           1
(5 rows)

-- Only the first chunks should be accessed (sorted merge append is enabled)
:PREFIX
SELECT * FROM sensor_data_compressed ORDER BY time DESC LIMIT 5;
                                                                                                                                                                              QUERY PLAN                                                                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual rows=5 loops=1)
   Output: sensor_data_compressed."time", sensor_data_compressed.sensor_id, sensor_data_compressed.cpu, sensor_data_compressed.temperature
   ->  Custom Scan (ChunkAppend) on public.sensor_data_compressed (actual rows=5 loops=1)
         Output: sensor_data_compressed."time", sensor_data_compressed.sensor_id, sensor_data_compressed.cpu, sensor_data_compressed.temperature
         Order: sensor_data_compressed."time" DESC
         Startup Exclusion: false
         Runtime Exclusion: false
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_91_chunk (actual rows=2 loops=1)
               Output: _hyper_45_91_chunk."time", _hyper_45_91_chunk.sensor_id, _hyper_45_91_chunk.cpu, _hyper_45_91_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (actual rows=2 loops=1)
                     Output: compress_hyper_46_98_chunk."time", compress_hyper_46_98_chunk.sensor_id, compress_hyper_46_98_chunk.cpu, compress_hyper_46_98_chunk.temperature, compress_hyper_46_98_chunk._ts_meta_count, compress_hyper_46_98_chunk._ts_meta_sequence_num, compress_hyper_46_98_chunk._ts_meta_min_1, compress_hyper_46_98_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_98_chunk._ts_meta_max_1 DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_98_chunk (actual rows=2 loops=1)
                           Output: compress_hyper_46_98_chunk."time", compress_hyper_46_98_chunk.sensor_id, compress_hyper_46_98_chunk.cpu, compress_hyper_46_98_chunk.temperature, compress_hyper_46_98_chunk._ts_meta_count, compress_hyper_46_98_chunk._ts_meta_sequence_num, compress_hyper_46_98_chunk._ts_meta_min_1, compress_hyper_46_98_chunk._ts_meta_max_1
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_90_chunk (actual rows=2 loops=1)
               Output: _hyper_45_90_chunk."time", _hyper_45_90_chunk.sensor_id, _hyper_45_90_chunk.cpu, _hyper_45_90_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (actual rows=2 loops=1)
                     Output: compress_hyper_46_97_chunk."time", compress_hyper_46_97_chunk.sensor_id, compress_hyper_46_97_chunk.cpu, compress_hyper_46_97_chunk.temperature, compress_hyper_46_97_chunk._ts_meta_count, compress_hyper_46_97_chunk._ts_meta_sequence_num, compress_hyper_46_97_chunk._ts_meta_min_1, compress_hyper_46_97_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_97_chunk._ts_meta_max_1 DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_97_chunk (actual rows=2 loops=1)
                           Output: compress_hyper_46_97_chunk."time", compress_hyper_46_97_chunk.sensor_id, compress_hyper_46_97_chunk.cpu, compress_hyper_46_97_chunk.temperature, compress_hyper_46_97_chunk._ts_meta_count, compress_hyper_46_97_chunk._ts_meta_sequence_num, compress_hyper_46_97_chunk._ts_meta_min_1, compress_hyper_46_97_chunk._ts_meta_max_1
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_89_chunk (actual rows=1 loops=1)
               Output: _hyper_45_89_chunk."time", _hyper_45_89_chunk.sensor_id, _hyper_45_89_chunk.cpu, _hyper_45_89_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (actual rows=2 loops=1)
                     Output: compress_hyper_46_96_chunk."time", compress_hyper_46_96_chunk.sensor_id, compress_hyper_46_96_chunk.cpu, compress_hyper_46_96_chunk.temperature, compress_hyper_46_96_chunk._ts_meta_count, compress_hyper_46_96_chunk._ts_meta_sequence_num, compress_hyper_46_96_chunk._ts_meta_min_1, compress_hyper_46_96_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_96_chunk._ts_meta_max_1 DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_96_chunk (actual rows=2 loops=1)
                           Output: compress_hyper_46_96_chunk."time", compress_hyper_46_96_chunk.sensor_id, compress_hyper_46_96_chunk.cpu, compress_hyper_46_96_chunk.temperature, compress_hyper_46_96_chunk._ts_meta_count, compress_hyper_46_96_chunk._ts_meta_sequence_num, compress_hyper_46_96_chunk._ts_meta_min_1, compress_hyper_46_96_chunk._ts_meta_max_1
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_88_chunk (never executed)
               Output: _hyper_45_88_chunk."time", _hyper_45_88_chunk.sensor_id, _hyper_45_88_chunk.cpu, _hyper_45_88_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_46_95_chunk."time", compress_hyper_46_95_chunk.sensor_id, compress_hyper_46_95_chunk.cpu, compress_hyper_46_95_chunk.temperature, compress_hyper_46_95_chunk._ts_meta_count, compress_hyper_46_95_chunk._ts_meta_sequence_num, compress_hyper_46_95_chunk._ts_meta_min_1, compress_hyper_46_95_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_95_chunk._ts_meta_max_1 DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_95_chunk (never executed)
                           Output: compress_hyper_46_95_chunk."time", compress_hyper_46_95_chunk.sensor_id, compress_hyper_46_95_chunk.cpu, compress_hyper_46_95_chunk.temperature, compress_hyper_46_95_chunk._ts_meta_count, compress_hyper_46_95_chunk._ts_meta_sequence_num, compress_hyper_46_95_chunk._ts_meta_min_1, compress_hyper_46_95_chunk._ts_meta_max_1
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_87_chunk (never executed)
               Output: _hyper_45_87_chunk."time", _hyper_45_87_chunk.sensor_id, _hyper_45_87_chunk.cpu, _hyper_45_87_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_46_94_chunk."time", compress_hyper_46_94_chunk.sensor_id, compress_hyper_46_94_chunk.cpu, compress_hyper_46_94_chunk.temperature, compress_hyper_46_94_chunk._ts_meta_count, compress_hyper_46_94_chunk._ts_meta_sequence_num, compress_hyper_46_94_chunk._ts_meta_min_1, compress_hyper_46_94_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_94_chunk._ts_meta_max_1 DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_94_chunk (never executed)
                           Output: compress_hyper_46_94_chunk."time", compress_hyper_46_94_chunk.sensor_id, compress_hyper_46_94_chunk.cpu, compress_hyper_46_94_chunk.temperature, compress_hyper_46_94_chunk._ts_meta_count, compress_hyper_46_94_chunk._ts_meta_sequence_num, compress_hyper_46_94_chunk._ts_meta_min_1, compress_hyper_46_94_chunk._ts_meta_max_1
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_86_chunk (never executed)
               Output: _hyper_45_86_chunk."time", _hyper_45_86_chunk.sensor_id, _hyper_45_86_chunk.cpu, _hyper_45_86_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_46_93_chunk."time", compress_hyper_46_93_chunk.sensor_id, compress_hyper_46_93_chunk.cpu, compress_hyper_46_93_chunk.temperature, compress_hyper_46_93_chunk._ts_meta_count, compress_hyper_46_93_chunk._ts_meta_sequence_num, compress_hyper_46_93_chunk._ts_meta_min_1, compress_hyper_46_93_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_93_chunk._ts_meta_max_1 DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_93_chunk (never executed)
                           Output: compress_hyper_46_93_chunk."time", compress_hyper_46_93_chunk.sensor_id, compress_hyper_46_93_chunk.cpu, compress_hyper_46_93_chunk.temperature, compress_hyper_46_93_chunk._ts_meta_count, compress_hyper_46_93_chunk._ts_meta_sequence_num, compress_hyper_46_93_chunk._ts_meta_min_1, compress_hyper_46_93_chunk._ts_meta_max_1
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_85_chunk (never executed)
               Output: _hyper_45_85_chunk."time", _hyper_45_85_chunk.sensor_id, _hyper_45_85_chunk.cpu, _hyper_45_85_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_46_92_chunk."time", compress_hyper_46_92_chunk.sensor_id, compress_hyper_46_92_chunk.cpu, compress_hyper_46_92_chunk.temperature, compress_hyper_46_92_chunk._ts_meta_count, compress_hyper_46_92_chunk._ts_meta_sequence_num, compress_hyper_46_92_chunk._ts_meta_min_1, compress_hyper_46_92_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_92_chunk._ts_meta_max_1 DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_92_chunk (never executed)
                           Output: compress_hyper_46_92_chunk."time", compress_hyper_46_92_chunk.sensor_id, compress_hyper_46_92_chunk.cpu, compress_hyper_46_92_chunk.temperature, compress_hyper_46_92_chunk._ts_meta_count, compress_hyper_46_92_chunk._ts_meta_sequence_num, compress_hyper_46_92_chunk._ts_meta_min_1, compress_hyper_46_92_chunk._ts_meta_max_1
(73 rows)

-- Only the first chunks should be accessed (sorted merge append is disabled)
SET timescaledb.enable_decompression_sorted_merge = FALSE;
:PREFIX
SELECT * FROM sensor_data_compressed ORDER BY time DESC LIMIT 5;
                                                                                                                                                                              QUERY PLAN                                                                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual rows=5 loops=1)
   Output: sensor_data_compressed."time", sensor_data_compressed.sensor_id, sensor_data_compressed.cpu, sensor_data_compressed.temperature
   ->  Custom Scan (ChunkAppend) on public.sensor_data_compressed (actual rows=5 loops=1)
         Output: sensor_data_compressed."time", sensor_data_compressed.sensor_id, sensor_data_compressed.cpu, sensor_data_compressed.temperature
         Order: sensor_data_compressed."time" DESC
         Startup Exclusion: false
         Runtime Exclusion: false
         ->  Sort (actual rows=2 loops=1)
               Output: _hyper_45_91_chunk."time", _hyper_45_91_chunk.sensor_id, _hyper_45_91_chunk.cpu, _hyper_45_91_chunk.temperature
               Sort Key: _hyper_45_91_chunk."time" DESC
               Sort Method: quicksort 
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_91_chunk (actual rows=2 loops=1)
                     Output: _hyper_45_91_chunk."time", _hyper_45_91_chunk.sensor_id, _hyper_45_91_chunk.cpu, _hyper_45_91_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_98_chunk (actual rows=2 loops=1)
                           Output: compress_hyper_46_98_chunk."time", compress_hyper_46_98_chunk.sensor_id, compress_hyper_46_98_chunk.cpu, compress_hyper_46_98_chunk.temperature, compress_hyper_46_98_chunk._ts_meta_count, compress_hyper_46_98_chunk._ts_meta_sequence_num, compress_hyper_46_98_chunk._ts_meta_min_1, compress_hyper_46_98_chunk._ts_meta_max_1
         ->  Sort (actual rows=2 loops=1)
               Output: _hyper_45_90_chunk."time", _hyper_45_90_chunk.sensor_id, _hyper_45_90_chunk.cpu, _hyper_45_90_chunk.temperature
               Sort Key: _hyper_45_90_chunk."time" DESC
               Sort Method: quicksort 
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_90_chunk (actual rows=2 loops=1)
                     Output: _hyper_45_90_chunk."time", _hyper_45_90_chunk.sensor_id, _hyper_45_90_chunk.cpu, _hyper_45_90_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_97_chunk (actual rows=2 loops=1)
                           Output: compress_hyper_46_97_chunk."time", compress_hyper_46_97_chunk.sensor_id, compress_hyper_46_97_chunk.cpu, compress_hyper_46_97_chunk.temperature, compress_hyper_46_97_chunk._ts_meta_count, compress_hyper_46_97_chunk._ts_meta_sequence_num, compress_hyper_46_97_chunk._ts_meta_min_1, compress_hyper_46_97_chunk._ts_meta_max_1
         ->  Sort (actual rows=1 loops=1)
               Output: _hyper_45_89_chunk."time", _hyper_45_89_chunk.sensor_id, _hyper_45_89_chunk.cpu, _hyper_45_89_chunk.temperature
               Sort Key: _hyper_45_89_chunk."time" DESC
               Sort Method: quicksort 
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_89_chunk (actual rows=2 loops=1)
                     Output: _hyper_45_89_chunk."time", _hyper_45_89_chunk.sensor_id, _hyper_45_89_chunk.cpu, _hyper_45_89_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_96_chunk (actual rows=2 loops=1)
                           Output: compress_hyper_46_96_chunk."time", compress_hyper_46_96_chunk.sensor_id, compress_hyper_46_96_chunk.cpu, compress_hyper_46_96_chunk.temperature, compress_hyper_46_96_chunk._ts_meta_count, compress_hyper_46_96_chunk._ts_meta_sequence_num, compress_hyper_46_96_chunk._ts_meta_min_1, compress_hyper_46_96_chunk._ts_meta_max_1
         ->  Sort (never executed)
               Output: _hyper_45_88_chunk."time", _hyper_45_88_chunk.sensor_id, _hyper_45_88_chunk.cpu, _hyper_45_88_chunk.temperature
               Sort Key: _hyper_45_88_chunk."time" DESC
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_88_chunk (never executed)
                     Output: _hyper_45_88_chunk."time", _hyper_45_88_chunk.sensor_id, _hyper_45_88_chunk.cpu, _hyper_45_88_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_95_chunk (never executed)
                           Output: compress_hyper_46_95_chunk."time", compress_hyper_46_95_chunk.sensor_id, compress_hyper_46_95_chunk.cpu, compress_hyper_46_95_chunk.temperature, compress_hyper_46_95_chunk._ts_meta_count, compress_hyper_46_95_chunk._ts_meta_sequence_num, compress_hyper_46_95_chunk._ts_meta_min_1, compress_hyper_46_95_chunk._ts_meta_max_1
         ->  Sort (never executed)
               Output: _hyper_45_87_chunk."time", _hyper_45_87_chunk.sensor_id, _hyper_45_87_chunk.cpu, _hyper_45_87_chunk.temperature
               Sort Key: _hyper_45_87_chunk."time" DESC
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_87_chunk (never executed)
                     Output: _hyper_45_87_chunk."time", _hyper_45_87_chunk.sensor_id, _hyper_45_87_chunk.cpu, _hyper_45_87_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_94_chunk (never executed)
                           Output: compress_hyper_46_94_chunk."time", compress_hyper_46_94_chunk.sensor_id, compress_hyper_46_94_chunk.cpu, compress_hyper_46_94_chunk.temperature, compress_hyper_46_94_chunk._ts_meta_count, compress_hyper_46_94_chunk._ts_meta_sequence_num, compress_hyper_46_94_chunk._ts_meta_min_1, compress_hyper_46_94_chunk._ts_meta_max_1
         ->  Sort (never executed)
               Output: _hyper_45_86_chunk."time", _hyper_45_86_chunk.sensor_id, _hyper_45_86_chunk.cpu, _hyper_45_86_chunk.temperature
               Sort Key: _hyper_45_86_chunk."time" DESC
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_86_chunk (never executed)
                     Output: _hyper_45_86_chunk."time", _hyper_45_86_chunk.sensor_id, _hyper_45_86_chunk.cpu, _hyper_45_86_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_93_chunk (never executed)
                           Output: compress_hyper_46_93_chunk."time", compress_hyper_46_93_chunk.sensor_id, compress_hyper_46_93_chunk.cpu, compress_hyper_46_93_chunk.temperature, compress_hyper_46_93_chunk._ts_meta_count, compress_hyper_46_93_chunk._ts_meta_sequence_num, compress_hyper_46_93_chunk._ts_meta_min_1, compress_hyper_46_93_chunk._ts_meta_max_1
         ->  Sort (never executed)
               Output: _hyper_45_85_chunk."time", _hyper_45_85_chunk.sensor_id, _hyper_45_85_chunk.cpu, _hyper_45_85_chunk.temperature
               Sort Key: _hyper_45_85_chunk."time" DESC
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_85_chunk (never executed)
                     Output: _hyper_45_85_chunk."time", _hyper_45_85_chunk.sensor_id, _hyper_45_85_chunk.cpu, _hyper_45_85_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_92_chunk (never executed)
                           Output: compress_hyper_46_92_chunk."time", compress_hyper_46_92_chunk.sensor_id, compress_hyper_46_92_chunk.cpu, compress_hyper_46_92_chunk.temperature, compress_hyper_46_92_chunk._ts_meta_count, compress_hyper_46_92_chunk._ts_meta_sequence_num, compress_hyper_46_92_chunk._ts_meta_min_1, compress_hyper_46_92_chunk._ts_meta_max_1
(66 rows)

RESET timescaledb.enable_decompression_sorted_merge;
-- Convert the last chunk into a partially compressed chunk
INSERT INTO sensor_data_compressed (time, sensor_id, cpu, temperature)
   VALUES ('1980-01-02 01:00:00-00', 2, 4, 14.0);
-- Only the first chunks should be accessed (sorted merge append is enabled)
:PREFIX
SELECT * FROM sensor_data_compressed ORDER BY time DESC LIMIT 5;
                                                                                                                                                                                 QUERY PLAN                                                                                                                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual rows=5 loops=1)
   Output: sensor_data_compressed."time", sensor_data_compressed.sensor_id, sensor_data_compressed.cpu, sensor_data_compressed.temperature
   ->  Custom Scan (ChunkAppend) on public.sensor_data_compressed (actual rows=5 loops=1)
         Output: sensor_data_compressed."time", sensor_data_compressed.sensor_id, sensor_data_compressed.cpu, sensor_data_compressed.temperature
         Order: sensor_data_compressed."time" DESC
         Startup Exclusion: false
         Runtime Exclusion: false
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_91_chunk (actual rows=2 loops=1)
               Output: _hyper_45_91_chunk."time", _hyper_45_91_chunk.sensor_id, _hyper_45_91_chunk.cpu, _hyper_45_91_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (actual rows=2 loops=1)
                     Output: compress_hyper_46_98_chunk."time", compress_hyper_46_98_chunk.sensor_id, compress_hyper_46_98_chunk.cpu, compress_hyper_46_98_chunk.temperature, compress_hyper_46_98_chunk._ts_meta_count, compress_hyper_46_98_chunk._ts_meta_sequence_num, compress_hyper_46_98_chunk._ts_meta_min_1, compress_hyper_46_98_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_98_chunk._ts_meta_max_1 DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_98_chunk (actual rows=2 loops=1)
                           Output: compress_hyper_46_98_chunk."time", compress_hyper_46_98_chunk.sensor_id, compress_hyper_46_98_chunk.cpu, compress_hyper_46_98_chunk.temperature, compress_hyper_46_98_chunk._ts_meta_count, compress_hyper_46_98_chunk._ts_meta_sequence_num, compress_hyper_46_98_chunk._ts_meta_min_1, compress_hyper_46_98_chunk._ts_meta_max_1
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_90_chunk (actual rows=2 loops=1)
               Output: _hyper_45_90_chunk."time", _hyper_45_90_chunk.sensor_id, _hyper_45_90_chunk.cpu, _hyper_45_90_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (actual rows=2 loops=1)
                     Output: compress_hyper_46_97_chunk."time", compress_hyper_46_97_chunk.sensor_id, compress_hyper_46_97_chunk.cpu, compress_hyper_46_97_chunk.temperature, compress_hyper_46_97_chunk._ts_meta_count, compress_hyper_46_97_chunk._ts_meta_sequence_num, compress_hyper_46_97_chunk._ts_meta_min_1, compress_hyper_46_97_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_97_chunk._ts_meta_max_1 DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_97_chunk (actual rows=2 loops=1)
                           Output: compress_hyper_46_97_chunk."time", compress_hyper_46_97_chunk.sensor_id, compress_hyper_46_97_chunk.cpu, compress_hyper_46_97_chunk.temperature, compress_hyper_46_97_chunk._ts_meta_count, compress_hyper_46_97_chunk._ts_meta_sequence_num, compress_hyper_46_97_chunk._ts_meta_min_1, compress_hyper_46_97_chunk._ts_meta_max_1
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_89_chunk (actual rows=1 loops=1)
               Output: _hyper_45_89_chunk."time", _hyper_45_89_chunk.sensor_id, _hyper_45_89_chunk.cpu, _hyper_45_89_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (actual rows=2 loops=1)
                     Output: compress_hyper_46_96_chunk."time", compress_hyper_46_96_chunk.sensor_id, compress_hyper_46_96_chunk.cpu, compress_hyper_46_96_chunk.temperature, compress_hyper_46_96_chunk._ts_meta_count, compress_hyper_46_96_chunk._ts_meta_sequence_num, compress_hyper_46_96_chunk._ts_meta_min_1, compress_hyper_46_96_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_96_chunk._ts_meta_max_1 DESC
                     Sort Method: quicksort 
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_96_chunk (actual rows=2 loops=1)
                           Output: compress_hyper_46_96_chunk."time", compress_hyper_46_96_chunk.sensor_id, compress_hyper_46_96_chunk.cpu, compress_hyper_46_96_chunk.temperature, compress_hyper_46_96_chunk._ts_meta_count, compress_hyper_46_96_chunk._ts_meta_sequence_num, compress_hyper_46_96_chunk._ts_meta_min_1, compress_hyper_46_96_chunk._ts_meta_max_1
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_88_chunk (never executed)
               Output: _hyper_45_88_chunk."time", _hyper_45_88_chunk.sensor_id, _hyper_45_88_chunk.cpu, _hyper_45_88_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_46_95_chunk."time", compress_hyper_46_95_chunk.sensor_id, compress_hyper_46_95_chunk.cpu, compress_hyper_46_95_chunk.temperature, compress_hyper_46_95_chunk._ts_meta_count, compress_hyper_46_95_chunk._ts_meta_sequence_num, compress_hyper_46_95_chunk._ts_meta_min_1, compress_hyper_46_95_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_95_chunk._ts_meta_max_1 DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_95_chunk (never executed)
                           Output: compress_hyper_46_95_chunk."time", compress_hyper_46_95_chunk.sensor_id, compress_hyper_46_95_chunk.cpu, compress_hyper_46_95_chunk.temperature, compress_hyper_46_95_chunk._ts_meta_count, compress_hyper_46_95_chunk._ts_meta_sequence_num, compress_hyper_46_95_chunk._ts_meta_min_1, compress_hyper_46_95_chunk._ts_meta_max_1
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_87_chunk (never executed)
               Output: _hyper_45_87_chunk."time", _hyper_45_87_chunk.sensor_id, _hyper_45_87_chunk.cpu, _hyper_45_87_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_46_94_chunk."time", compress_hyper_46_94_chunk.sensor_id, compress_hyper_46_94_chunk.cpu, compress_hyper_46_94_chunk.temperature, compress_hyper_46_94_chunk._ts_meta_count, compress_hyper_46_94_chunk._ts_meta_sequence_num, compress_hyper_46_94_chunk._ts_meta_min_1, compress_hyper_46_94_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_94_chunk._ts_meta_max_1 DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_94_chunk (never executed)
                           Output: compress_hyper_46_94_chunk."time", compress_hyper_46_94_chunk.sensor_id, compress_hyper_46_94_chunk.cpu, compress_hyper_46_94_chunk.temperature, compress_hyper_46_94_chunk._ts_meta_count, compress_hyper_46_94_chunk._ts_meta_sequence_num, compress_hyper_46_94_chunk._ts_meta_min_1, compress_hyper_46_94_chunk._ts_meta_max_1
         ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_86_chunk (never executed)
               Output: _hyper_45_86_chunk."time", _hyper_45_86_chunk.sensor_id, _hyper_45_86_chunk.cpu, _hyper_45_86_chunk.temperature
               Sorted merge append: true
               Bulk Decompression: false
               ->  Sort (never executed)
                     Output: compress_hyper_46_93_chunk."time", compress_hyper_46_93_chunk.sensor_id, compress_hyper_46_93_chunk.cpu, compress_hyper_46_93_chunk.temperature, compress_hyper_46_93_chunk._ts_meta_count, compress_hyper_46_93_chunk._ts_meta_sequence_num, compress_hyper_46_93_chunk._ts_meta_min_1, compress_hyper_46_93_chunk._ts_meta_max_1
                     Sort Key: compress_hyper_46_93_chunk._ts_meta_max_1 DESC
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_93_chunk (never executed)
                           Output: compress_hyper_46_93_chunk."time", compress_hyper_46_93_chunk.sensor_id, compress_hyper_46_93_chunk.cpu, compress_hyper_46_93_chunk.temperature, compress_hyper_46_93_chunk._ts_meta_count, compress_hyper_46_93_chunk._ts_meta_sequence_num, compress_hyper_46_93_chunk._ts_meta_min_1, compress_hyper_46_93_chunk._ts_meta_max_1
         ->  Merge Append (never executed)
               Sort Key: _hyper_45_85_chunk."time" DESC
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_85_chunk (never executed)
                     Output: _hyper_45_85_chunk."time", _hyper_45_85_chunk.sensor_id, _hyper_45_85_chunk.cpu, _hyper_45_85_chunk.temperature
                     Sorted merge append: true
                     Bulk Decompression: false
                     ->  Sort (never executed)
                           Output: compress_hyper_46_92_chunk."time", compress_hyper_46_92_chunk.sensor_id, compress_hyper_46_92_chunk.cpu, compress_hyper_46_92_chunk.temperature, compress_hyper_46_92_chunk._ts_meta_count, compress_hyper_46_92_chunk._ts_meta_sequence_num, compress_hyper_46_92_chunk._ts_meta_min_1, compress_hyper_46_92_chunk._ts_meta_max_1
                           Sort Key: compress_hyper_46_92_chunk._ts_meta_max_1 DESC
                           ->  Seq Scan on _timescaledb_internal.compress_hyper_46_92_chunk (never executed)
                                 Output: compress_hyper_46_92_chunk."time", compress_hyper_46_92_chunk.sensor_id, compress_hyper_46_92_chunk.cpu, compress_hyper_46_92_chunk.temperature, compress_hyper_46_92_chunk._ts_meta_count, compress_hyper_46_92_chunk._ts_meta_sequence_num, compress_hyper_46_92_chunk._ts_meta_min_1, compress_hyper_46_92_chunk._ts_meta_max_1
               ->  Index Scan using _hyper_45_85_chunk_sensor_data_compressed_time_idx on _timescaledb_internal._hyper_45_85_chunk (never executed)
                     Output: _hyper_45_85_chunk."time", _hyper_45_85_chunk.sensor_id, _hyper_45_85_chunk.cpu, _hyper_45_85_chunk.temperature
(77 rows)

-- Only the first chunks should be accessed (sorted merge append is disabled)
SET timescaledb.enable_decompression_sorted_merge = FALSE;
:PREFIX
SELECT * FROM sensor_data_compressed ORDER BY time DESC LIMIT 5;
                                                                                                                                                                                 QUERY PLAN                                                                                                                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (actual rows=5 loops=1)
   Output: sensor_data_compressed."time", sensor_data_compressed.sensor_id, sensor_data_compressed.cpu, sensor_data_compressed.temperature
   ->  Custom Scan (ChunkAppend) on public.sensor_data_compressed (actual rows=5 loops=1)
         Output: sensor_data_compressed."time", sensor_data_compressed.sensor_id, sensor_data_compressed.cpu, sensor_data_compressed.temperature
         Order: sensor_data_compressed."time" DESC
         Startup Exclusion: false
         Runtime Exclusion: false
         ->  Sort (actual rows=2 loops=1)
               Output: _hyper_45_91_chunk."time", _hyper_45_91_chunk.sensor_id, _hyper_45_91_chunk.cpu, _hyper_45_91_chunk.temperature
               Sort Key: _hyper_45_91_chunk."time" DESC
               Sort Method: quicksort 
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_91_chunk (actual rows=2 loops=1)
                     Output: _hyper_45_91_chunk."time", _hyper_45_91_chunk.sensor_id, _hyper_45_91_chunk.cpu, _hyper_45_91_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_98_chunk (actual rows=2 loops=1)
                           Output: compress_hyper_46_98_chunk."time", compress_hyper_46_98_chunk.sensor_id, compress_hyper_46_98_chunk.cpu, compress_hyper_46_98_chunk.temperature, compress_hyper_46_98_chunk._ts_meta_count, compress_hyper_46_98_chunk._ts_meta_sequence_num, compress_hyper_46_98_chunk._ts_meta_min_1, compress_hyper_46_98_chunk._ts_meta_max_1
         ->  Sort (actual rows=2 loops=1)
               Output: _hyper_45_90_chunk."time", _hyper_45_90_chunk.sensor_id, _hyper_45_90_chunk.cpu, _hyper_45_90_chunk.temperature
               Sort Key: _hyper_45_90_chunk."time" DESC
               Sort Method: quicksort 
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_90_chunk (actual rows=2 loops=1)
                     Output: _hyper_45_90_chunk."time", _hyper_45_90_chunk.sensor_id, _hyper_45_90_chunk.cpu, _hyper_45_90_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_97_chunk (actual rows=2 loops=1)
                           Output: compress_hyper_46_97_chunk."time", compress_hyper_46_97_chunk.sensor_id, compress_hyper_46_97_chunk.cpu, compress_hyper_46_97_chunk.temperature, compress_hyper_46_97_chunk._ts_meta_count, compress_hyper_46_97_chunk._ts_meta_sequence_num, compress_hyper_46_97_chunk._ts_meta_min_1, compress_hyper_46_97_chunk._ts_meta_max_1
         ->  Sort (actual rows=1 loops=1)
               Output: _hyper_45_89_chunk."time", _hyper_45_89_chunk.sensor_id, _hyper_45_89_chunk.cpu, _hyper_45_89_chunk.temperature
               Sort Key: _hyper_45_89_chunk."time" DESC
               Sort Method: quicksort 
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_89_chunk (actual rows=2 loops=1)
                     Output: _hyper_45_89_chunk."time", _hyper_45_89_chunk.sensor_id, _hyper_45_89_chunk.cpu, _hyper_45_89_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_96_chunk (actual rows=2 loops=1)
                           Output: compress_hyper_46_96_chunk."time", compress_hyper_46_96_chunk.sensor_id, compress_hyper_46_96_chunk.cpu, compress_hyper_46_96_chunk.temperature, compress_hyper_46_96_chunk._ts_meta_count, compress_hyper_46_96_chunk._ts_meta_sequence_num, compress_hyper_46_96_chunk._ts_meta_min_1, compress_hyper_46_96_chunk._ts_meta_max_1
         ->  Sort (never executed)
               Output: _hyper_45_88_chunk."time", _hyper_45_88_chunk.sensor_id, _hyper_45_88_chunk.cpu, _hyper_45_88_chunk.temperature
               Sort Key: _hyper_45_88_chunk."time" DESC
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_88_chunk (never executed)
                     Output: _hyper_45_88_chunk."time", _hyper_45_88_chunk.sensor_id, _hyper_45_88_chunk.cpu, _hyper_45_88_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_95_chunk (never executed)
                           Output: compress_hyper_46_95_chunk."time", compress_hyper_46_95_chunk.sensor_id, compress_hyper_46_95_chunk.cpu, compress_hyper_46_95_chunk.temperature, compress_hyper_46_95_chunk._ts_meta_count, compress_hyper_46_95_chunk._ts_meta_sequence_num, compress_hyper_46_95_chunk._ts_meta_min_1, compress_hyper_46_95_chunk._ts_meta_max_1
         ->  Sort (never executed)
               Output: _hyper_45_87_chunk."time", _hyper_45_87_chunk.sensor_id, _hyper_45_87_chunk.cpu, _hyper_45_87_chunk.temperature
               Sort Key: _hyper_45_87_chunk."time" DESC
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_87_chunk (never executed)
                     Output: _hyper_45_87_chunk."time", _hyper_45_87_chunk.sensor_id, _hyper_45_87_chunk.cpu, _hyper_45_87_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_94_chunk (never executed)
                           Output: compress_hyper_46_94_chunk."time", compress_hyper_46_94_chunk.sensor_id, compress_hyper_46_94_chunk.cpu, compress_hyper_46_94_chunk.temperature, compress_hyper_46_94_chunk._ts_meta_count, compress_hyper_46_94_chunk._ts_meta_sequence_num, compress_hyper_46_94_chunk._ts_meta_min_1, compress_hyper_46_94_chunk._ts_meta_max_1
         ->  Sort (never executed)
               Output: _hyper_45_86_chunk."time", _hyper_45_86_chunk.sensor_id, _hyper_45_86_chunk.cpu, _hyper_45_86_chunk.temperature
               Sort Key: _hyper_45_86_chunk."time" DESC
               ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_86_chunk (never executed)
                     Output: _hyper_45_86_chunk."time", _hyper_45_86_chunk.sensor_id, _hyper_45_86_chunk.cpu, _hyper_45_86_chunk.temperature
                     Bulk Decompression: true
                     ->  Seq Scan on _timescaledb_internal.compress_hyper_46_93_chunk (never executed)
                           Output: compress_hyper_46_93_chunk."time", compress_hyper_46_93_chunk.sensor_id, compress_hyper_46_93_chunk.cpu, compress_hyper_46_93_chunk.temperature, compress_hyper_46_93_chunk._ts_meta_count, compress_hyper_46_93_chunk._ts_meta_sequence_num, compress_hyper_46_93_chunk._ts_meta_min_1, compress_hyper_46_93_chunk._ts_meta_max_1
         ->  Merge Append (never executed)
               Sort Key: _hyper_45_85_chunk."time" DESC
               ->  Sort (never executed)
                     Output: _hyper_45_85_chunk."time", _hyper_45_85_chunk.sensor_id, _hyper_45_85_chunk.cpu, _hyper_45_85_chunk.temperature
                     Sort Key: _hyper_45_85_chunk."time" DESC
                     ->  Custom Scan (DecompressChunk) on _timescaledb_internal._hyper_45_85_chunk (never executed)
                           Output: _hyper_45_85_chunk."time", _hyper_45_85_chunk.sensor_id, _hyper_45_85_chunk.cpu, _hyper_45_85_chunk.temperature
                           Bulk Decompression: true
                           ->  Seq Scan on _timescaledb_internal.compress_hyper_46_92_chunk (never executed)
                                 Output: compress_hyper_46_92_chunk."time", compress_hyper_46_92_chunk.sensor_id, compress_hyper_46_92_chunk.cpu, compress_hyper_46_92_chunk.temperature, compress_hyper_46_92_chunk._ts_meta_count, compress_hyper_46_92_chunk._ts_meta_sequence_num, compress_hyper_46_92_chunk._ts_meta_min_1, compress_hyper_46_92_chunk._ts_meta_max_1
               ->  Index Scan using _hyper_45_85_chunk_sensor_data_compressed_time_idx on _timescaledb_internal._hyper_45_85_chunk (never executed)
                     Output: _hyper_45_85_chunk."time", _hyper_45_85_chunk.sensor_id, _hyper_45_85_chunk.cpu, _hyper_45_85_chunk.temperature
(70 rows)

RESET timescaledb.enable_decompression_sorted_merge;
-- create another chunk
INSERT INTO stattest SELECT '2021/02/20 01:00'::TIMESTAMPTZ + ('1 hour'::interval * v), 250 * v FROM generate_series(125,140) v;
ANALYZE stattest;
SELECT count(*) from show_chunks('stattest');
 count 
-------
     2
(1 row)

SELECT table_name INTO TEMPORARY temptable FROM _timescaledb_catalog.chunk WHERE hypertable_id = (SELECT id FROM _timescaledb_catalog.hypertable WHERE table_name = 'stattest') ORDER BY creation_time desc limit 1;
SELECT table_name  as "STAT_CHUNK2_NAME" FROM temptable \gset
-- verify that approximate_row_count works ok on normal chunks
SELECT approximate_row_count('_timescaledb_internal.' || :'STAT_CHUNK2_NAME');
 approximate_row_count 
-----------------------
                    16
(1 row)

-- verify that approximate_row_count works fine on a hypertable with a mix of uncompressed
-- and compressed data
SELECT approximate_row_count('stattest');
 approximate_row_count 
-----------------------
                    68
(1 row)

DROP TABLE stattest;