append-16.out

-- This file and its contents are licensed under the Apache License 2.0.
-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.
\set TEST_BASE_NAME append
SELECT format('include/%s_load.sql', :'TEST_BASE_NAME') as "TEST_LOAD_NAME",
       format('include/%s_query.sql', :'TEST_BASE_NAME') as "TEST_QUERY_NAME",
       format('%s/results/%s_results_optimized.out', :'TEST_OUTPUT_DIR', :'TEST_BASE_NAME') as "TEST_RESULTS_OPTIMIZED",
       format('%s/results/%s_results_unoptimized.out', :'TEST_OUTPUT_DIR', :'TEST_BASE_NAME') as "TEST_RESULTS_UNOPTIMIZED"
\gset
SELECT format('\! diff -u --label "Unoptimized results" --label "Optimized results" %s %s', :'TEST_RESULTS_UNOPTIMIZED', :'TEST_RESULTS_OPTIMIZED') as "DIFF_CMD"
\gset
SET timescaledb.enable_now_constify TO false;
-- disable memoize node to avoid flaky results
SET enable_memoize TO 'off';
-- disable index only scans to avoid some flaky results
SET enable_indexonlyscan TO FALSE;
\set PREFIX 'EXPLAIN (analyze, buffers off, costs off, timing off, summary off)'
\ir :TEST_LOAD_NAME
-- This file and its contents are licensed under the Apache License 2.0.
-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.
-- create a now() function for repeatable testing that always returns
-- the same timestamp. It needs to be marked STABLE
CREATE OR REPLACE FUNCTION now_s()
RETURNS timestamptz LANGUAGE PLPGSQL STABLE PARALLEL SAFE AS
$BODY$
BEGIN
    RAISE NOTICE 'Stable function now_s() called!';
    RETURN '2017-08-22T10:00:00'::timestamptz;
END;
$BODY$;
CREATE OR REPLACE FUNCTION now_i()
RETURNS timestamptz LANGUAGE PLPGSQL IMMUTABLE AS
$BODY$
BEGIN
    RAISE NOTICE 'Immutable function now_i() called!';
    RETURN '2017-08-22T10:00:00'::timestamptz;
END;
$BODY$;
CREATE OR REPLACE FUNCTION now_v()
RETURNS timestamptz LANGUAGE PLPGSQL VOLATILE AS
$BODY$
BEGIN
    RAISE NOTICE 'Volatile function now_v() called!';
    RETURN '2017-08-22T10:00:00'::timestamptz;
END;
$BODY$;
CREATE OR REPLACE PROCEDURE force_parallel(on_or_off bool)
LANGUAGE PLPGSQL AS
$$
BEGIN
    IF current_setting('server_version_num')::int < 160000 THEN
        IF on_or_off THEN
            set force_parallel_mode = 'on';
        ELSE
            set force_parallel_mode = 'off';
        END IF;
    ELSE
        IF on_or_off THEN
            set debug_parallel_query = 'on';
        ELSE
            set debug_parallel_query = 'off';
        END IF;
    END IF;
END;
$$;
CREATE TABLE append_test(time timestamptz, temp float, colorid integer, attr jsonb);
SELECT create_hypertable('append_test', 'time', chunk_time_interval => 2628000000000);
    create_hypertable     
--------------------------
 (1,public,append_test,t)

-- create three chunks
INSERT INTO append_test VALUES ('2017-03-22T09:18:22', 23.5, 1, '{"a": 1, "b": 2}'),
                               ('2017-03-22T09:18:23', 21.5, 1, '{"a": 1, "b": 2}'),
                               ('2017-05-22T09:18:22', 36.2, 2, '{"c": 3, "b": 2}'),
                               ('2017-05-22T09:18:23', 15.2, 2, '{"c": 3}'),
                               ('2017-08-22T09:18:22', 34.1, 3, '{"c": 4}');
VACUUM (ANALYZE) append_test;
-- Create another hypertable to join with
CREATE TABLE join_test(time timestamptz, temp float, colorid integer);
SELECT create_hypertable('join_test', 'time', chunk_time_interval => 2628000000000);
   create_hypertable    
------------------------
 (2,public,join_test,t)

INSERT INTO join_test VALUES ('2017-01-22T09:18:22', 15.2, 1),
                             ('2017-02-22T09:18:22', 24.5, 2),
                             ('2017-08-22T09:18:22', 23.1, 3);
VACUUM (ANALYZE) join_test;
-- Create another table to join with which is not a hypertable.
CREATE TABLE join_test_plain(time timestamptz, temp float, colorid integer, attr jsonb);
INSERT INTO join_test_plain VALUES ('2017-01-22T09:18:22', 15.2, 1, '{"a": 1}'),
                             ('2017-02-22T09:18:22', 24.5, 2, '{"b": 2}'),
                             ('2017-08-22T09:18:22', 23.1, 3, '{"c": 3}');
VACUUM (ANALYZE) join_test_plain;
-- create hypertable with DATE time dimension
CREATE TABLE metrics_date(time DATE NOT NULL);
SELECT create_hypertable('metrics_date','time');
     create_hypertable     
---------------------------
 (3,public,metrics_date,t)

INSERT INTO metrics_date SELECT generate_series('2000-01-01'::date, '2000-02-01'::date, '5m'::interval);
VACUUM (ANALYZE) metrics_date;
-- create hypertable with TIMESTAMP time dimension
CREATE TABLE metrics_timestamp(time TIMESTAMP NOT NULL);
SELECT create_hypertable('metrics_timestamp','time');
psql:include/append_load.sql:91: WARNING:  column type "timestamp without time zone" used for "time" does not follow best practices
       create_hypertable        
--------------------------------
 (4,public,metrics_timestamp,t)

INSERT INTO metrics_timestamp SELECT generate_series('2000-01-01'::date, '2000-02-01'::date, '5m'::interval);
VACUUM (ANALYZE) metrics_timestamp;
-- create hypertable with TIMESTAMPTZ time dimension
CREATE TABLE metrics_timestamptz(time TIMESTAMPTZ NOT NULL, device_id INT NOT NULL);
CREATE INDEX ON metrics_timestamptz(device_id,time);
SELECT create_hypertable('metrics_timestamptz','time');
        create_hypertable         
----------------------------------
 (5,public,metrics_timestamptz,t)

INSERT INTO metrics_timestamptz SELECT generate_series('2000-01-01'::date, '2000-02-01'::date, '5m'::interval), 1;
INSERT INTO metrics_timestamptz SELECT generate_series('2000-01-01'::date, '2000-02-01'::date, '5m'::interval), 2;
INSERT INTO metrics_timestamptz SELECT generate_series('2000-01-01'::date, '2000-02-01'::date, '5m'::interval), 3;
VACUUM (ANALYZE) metrics_timestamptz;
-- create space partitioned hypertable
CREATE TABLE metrics_space(time timestamptz NOT NULL, device_id int NOT NULL, v1 float, v2 float, v3 text);
SELECT create_hypertable('metrics_space','time','device_id',3);
     create_hypertable      
----------------------------
 (6,public,metrics_space,t)

INSERT INTO metrics_space
SELECT time, device_id, device_id + 0.25, device_id + 0.75, device_id
FROM generate_series('2000-01-01'::timestamptz, '2000-01-14'::timestamptz, '5m'::interval) g1(time),
  generate_series(1,10,1) g2(device_id)
ORDER BY time, device_id;
VACUUM (ANALYZE) metrics_space;
-- test ChunkAppend projection #2661
CREATE TABLE i2661 (
  machine_id int4 NOT NULL,
  "name" varchar(255) NOT NULL,
  "timestamp" timestamptz NOT NULL,
  "first" float4 NULL
);
SELECT create_hypertable('i2661', 'timestamp');
psql:include/append_load.sql:123: WARNING:  column type "character varying" used for "name" does not follow best practices
 create_hypertable  
--------------------
 (7,public,i2661,t)

INSERT INTO i2661 SELECT 1, 'speed', generate_series('2019-12-31 00:00:00', '2020-01-10 00:00:00', '2m'::interval), 0;
VACUUM (ANALYZE) i2661;
\ir :TEST_QUERY_NAME
-- This file and its contents are licensed under the Apache License 2.0.
-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.
-- canary for results diff
-- this should be the only output of the results diff
SELECT setting, current_setting(setting) AS value from (VALUES ('timescaledb.enable_optimizations'),('timescaledb.enable_chunk_append')) v(setting);
             setting              | value 
----------------------------------+-------
 timescaledb.enable_optimizations | on
 timescaledb.enable_chunk_append  | on

-- query should exclude all chunks with optimization on
:PREFIX
SELECT * FROM append_test WHERE time > now_s() + '1 month'
ORDER BY time DESC;
psql:include/append_query.sql:12: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:12: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:12: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:12: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:12: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:12: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:12: NOTICE:  Stable function now_s() called!
--- QUERY PLAN ---
 Sort (actual rows=0.00 loops=1)
   Sort Key: append_test."time" DESC
   Sort Method: quicksort 
   ->  Custom Scan (ChunkAppend) on append_test (actual rows=0.00 loops=1)
         Chunks excluded during startup: 3

--query should exclude all chunks and be a MergeAppend
:PREFIX
SELECT * FROM append_test WHERE time > now_s() + '1 month'
ORDER BY time DESC limit 1;
psql:include/append_query.sql:17: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:17: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:17: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:17: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:17: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:17: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:17: NOTICE:  Stable function now_s() called!
--- QUERY PLAN ---
 Limit (actual rows=0.00 loops=1)
   ->  Custom Scan (ChunkAppend) on append_test (actual rows=0.00 loops=1)
         Order: append_test."time" DESC
         Chunks excluded during startup: 3

-- when optimized, the plan should be a constraint-aware append and
-- cover only one chunk. It should be a backward index scan due to
-- descending index on time. Should also skip the main table, since it
-- cannot hold tuples
:PREFIX
SELECT * FROM append_test WHERE time > now_s() - interval '2 months';
psql:include/append_query.sql:24: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:24: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:24: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:24: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:24: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:24: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:24: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:24: NOTICE:  Stable function now_s() called!
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on append_test (actual rows=1.00 loops=1)
   Chunks excluded during startup: 2
   ->  Seq Scan on _hyper_1_3_chunk (actual rows=1.00 loops=1)
         Filter: ("time" > (now_s() - '@ 2 mons'::interval))

-- adding ORDER BY and LIMIT should turn the plan into an optimized
-- ordered append plan
:PREFIX
SELECT * FROM append_test WHERE time > now_s() - interval '2 months'
ORDER BY time LIMIT 3;
psql:include/append_query.sql:30: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:30: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:30: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:30: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:30: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:30: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:30: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:30: NOTICE:  Stable function now_s() called!
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Sort (actual rows=1.00 loops=1)
         Sort Key: append_test."time"
         Sort Method: quicksort 
         ->  Custom Scan (ChunkAppend) on append_test (actual rows=1.00 loops=1)
               Chunks excluded during startup: 2
               ->  Seq Scan on _hyper_1_3_chunk (actual rows=1.00 loops=1)
                     Filter: ("time" > (now_s() - '@ 2 mons'::interval))

-- no optimized plan for queries with restrictions that can be
-- constified at planning time. Regular planning-time constraint
-- exclusion should occur.
:PREFIX
SELECT * FROM append_test WHERE time > now_i() - interval '2 months'
ORDER BY time;
psql:include/append_query.sql:37: NOTICE:  Immutable function now_i() called!
--- QUERY PLAN ---
 Sort (actual rows=1.00 loops=1)
   Sort Key: append_test."time"
   Sort Method: quicksort 
   ->  Custom Scan (ChunkAppend) on append_test (actual rows=1.00 loops=1)
         Chunks excluded during startup: 2
         ->  Seq Scan on _hyper_1_3_chunk (actual rows=1.00 loops=1)
               Filter: ("time" > ('Tue Aug 22 10:00:00 2017 PDT'::timestamp with time zone - '@ 2 mons'::interval))

-- currently, we cannot distinguish between stable and volatile
-- functions as far as applying our modified plan. However, volatile
-- function should not be pre-evaluated to constants, so no chunk
-- exclusion should occur.
:PREFIX
SELECT * FROM append_test WHERE time > now_v() - interval '2 months'
ORDER BY time;
psql:include/append_query.sql:45: NOTICE:  Volatile function now_v() called!
psql:include/append_query.sql:45: NOTICE:  Volatile function now_v() called!
psql:include/append_query.sql:45: NOTICE:  Volatile function now_v() called!
psql:include/append_query.sql:45: NOTICE:  Volatile function now_v() called!
psql:include/append_query.sql:45: NOTICE:  Volatile function now_v() called!
--- QUERY PLAN ---
 Sort (actual rows=1.00 loops=1)
   Sort Key: append_test."time"
   Sort Method: quicksort 
   ->  Custom Scan (ChunkAppend) on append_test (actual rows=1.00 loops=1)
         Chunks excluded during startup: 0
         ->  Seq Scan on _hyper_1_1_chunk (actual rows=0.00 loops=1)
               Filter: ("time" > (now_v() - '@ 2 mons'::interval))
               Rows Removed by Filter: 2
         ->  Seq Scan on _hyper_1_2_chunk (actual rows=0.00 loops=1)
               Filter: ("time" > (now_v() - '@ 2 mons'::interval))
               Rows Removed by Filter: 2
         ->  Seq Scan on _hyper_1_3_chunk (actual rows=1.00 loops=1)
               Filter: ("time" > (now_v() - '@ 2 mons'::interval))

-- prepared statement output should be the same regardless of
-- optimizations
PREPARE query_opt AS
SELECT * FROM append_test WHERE time > now_s() - interval '2 months'
ORDER BY time;
:PREFIX EXECUTE query_opt;
psql:include/append_query.sql:53: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:53: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:53: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:53: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:53: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:53: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:53: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:53: NOTICE:  Stable function now_s() called!
--- QUERY PLAN ---
 Sort (actual rows=1.00 loops=1)
   Sort Key: append_test."time"
   Sort Method: quicksort 
   ->  Custom Scan (ChunkAppend) on append_test (actual rows=1.00 loops=1)
         Chunks excluded during startup: 2
         ->  Seq Scan on _hyper_1_3_chunk (actual rows=1.00 loops=1)
               Filter: ("time" > (now_s() - '@ 2 mons'::interval))

DEALLOCATE query_opt;
-- aggregates should produce same output
:PREFIX
SELECT date_trunc('year', time) t, avg(temp) FROM append_test
WHERE time > now_s() - interval '4 months'
GROUP BY t
ORDER BY t DESC;
psql:include/append_query.sql:62: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:62: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:62: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:62: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:62: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:62: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:62: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:62: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:62: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:62: NOTICE:  Stable function now_s() called!
--- QUERY PLAN ---
 GroupAggregate (actual rows=1.00 loops=1)
   Group Key: (date_trunc('year'::text, append_test."time"))
   ->  Sort (actual rows=3.00 loops=1)
         Sort Key: (date_trunc('year'::text, append_test."time")) DESC
         Sort Method: quicksort 
         ->  Result (actual rows=3.00 loops=1)
               ->  Custom Scan (ChunkAppend) on append_test (actual rows=3.00 loops=1)
                     Chunks excluded during startup: 1
                     ->  Seq Scan on _hyper_1_3_chunk (actual rows=1.00 loops=1)
                           Filter: ("time" > (now_s() - '@ 4 mons'::interval))
                     ->  Seq Scan on _hyper_1_2_chunk (actual rows=2.00 loops=1)
                           Filter: ("time" > (now_s() - '@ 4 mons'::interval))

-- querying outside the time range should return nothing. This tests
-- that ConstraintAwareAppend can handle the case when an Append node
-- is turned into a Result node due to no children
:PREFIX
SELECT date_trunc('year', time) t, avg(temp)
FROM append_test
WHERE time < '2016-03-22'
AND date_part('dow', time) between 1 and 5
GROUP BY t
ORDER BY t DESC;
--- QUERY PLAN ---
 GroupAggregate (actual rows=0.00 loops=1)
   Group Key: (date_trunc('year'::text, "time"))
   ->  Sort (actual rows=0.00 loops=1)
         Sort Key: (date_trunc('year'::text, "time")) DESC
         Sort Method: quicksort 
         ->  Result (actual rows=0.00 loops=1)
               One-Time Filter: false

-- a parameterized query can safely constify params, so won't be
-- optimized by constraint-aware append since regular constraint
-- exclusion works just fine
PREPARE query_param AS
SELECT * FROM append_test WHERE time > $1 ORDER BY time;
:PREFIX
EXECUTE query_param(now_s() - interval '2 months');
psql:include/append_query.sql:82: NOTICE:  Stable function now_s() called!
--- QUERY PLAN ---
 Sort (actual rows=1.00 loops=1)
   Sort Key: _hyper_1_3_chunk."time"
   Sort Method: quicksort 
   ->  Seq Scan on _hyper_1_3_chunk (actual rows=1.00 loops=1)

DEALLOCATE query_param;
--test with cte
:PREFIX
WITH data AS (
    SELECT time_bucket(INTERVAL '30 day', TIME) AS btime, AVG(temp) AS VALUE
    FROM append_test
    WHERE
        TIME > now_s() - INTERVAL '400 day'
    AND colorid > 0
    GROUP BY btime
),
period AS (
    SELECT time_bucket(INTERVAL '30 day', TIME) AS btime
      FROM  GENERATE_SERIES('2017-03-22T01:01:01', '2017-08-23T01:01:01', INTERVAL '30 day') TIME
  )
SELECT period.btime, VALUE
    FROM period
    LEFT JOIN DATA USING (btime)
    ORDER BY period.btime;
psql:include/append_query.sql:102: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:102: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:102: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:102: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:102: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:102: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:102: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:102: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:102: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:102: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:102: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:102: NOTICE:  Stable function now_s() called!
--- QUERY PLAN ---
 Sort (actual rows=6.00 loops=1)
   Sort Key: (time_bucket('@ 30 days'::interval, "time"."time"))
   Sort Method: quicksort 
   ->  Hash Left Join (actual rows=6.00 loops=1)
         Hash Cond: (time_bucket('@ 30 days'::interval, "time"."time") = data.btime)
         ->  Function Scan on generate_series "time" (actual rows=6.00 loops=1)
         ->  Hash (actual rows=3.00 loops=1)
               ->  Subquery Scan on data (actual rows=3.00 loops=1)
                     ->  HashAggregate (actual rows=3.00 loops=1)
                           Group Key: time_bucket('@ 30 days'::interval, append_test."time")
                           ->  Result (actual rows=5.00 loops=1)
                                 ->  Custom Scan (ChunkAppend) on append_test (actual rows=5.00 loops=1)
                                       Chunks excluded during startup: 0
                                       ->  Seq Scan on _hyper_1_1_chunk (actual rows=2.00 loops=1)
                                             Filter: ((colorid > 0) AND ("time" > (now_s() - '@ 400 days'::interval)))
                                       ->  Seq Scan on _hyper_1_2_chunk (actual rows=2.00 loops=1)
                                             Filter: ((colorid > 0) AND ("time" > (now_s() - '@ 400 days'::interval)))
                                       ->  Seq Scan on _hyper_1_3_chunk (actual rows=1.00 loops=1)
                                             Filter: ((colorid > 0) AND ("time" > (now_s() - '@ 400 days'::interval)))

WITH data AS (
    SELECT time_bucket(INTERVAL '30 day', TIME) AS btime, AVG(temp) AS VALUE
    FROM append_test
    WHERE
        TIME > now_s() - INTERVAL '400 day'
    AND colorid > 0
    GROUP BY btime
),
period AS (
    SELECT time_bucket(INTERVAL '30 day', TIME) AS btime
      FROM  GENERATE_SERIES('2017-03-22T01:01:01', '2017-08-23T01:01:01', INTERVAL '30 day') TIME
  )
SELECT period.btime, VALUE
    FROM period
    LEFT JOIN DATA USING (btime)
    ORDER BY period.btime;
psql:include/append_query.sql:119: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:119: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:119: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:119: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:119: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:119: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:119: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:119: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:119: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:119: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:119: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:119: NOTICE:  Stable function now_s() called!
            btime             | value 
------------------------------+-------
 Fri Mar 03 16:00:00 2017 PST |  22.5
 Sun Apr 02 17:00:00 2017 PDT |      
 Tue May 02 17:00:00 2017 PDT |  25.7
 Thu Jun 01 17:00:00 2017 PDT |      
 Sat Jul 01 17:00:00 2017 PDT |      
 Mon Jul 31 17:00:00 2017 PDT |  34.1

-- force nested loop join with no materialization. This tests that the
-- inner ConstraintAwareScan supports resetting its scan for every
-- iteration of the outer relation loop
set enable_hashjoin = 'off';
set enable_mergejoin = 'off';
set enable_material = 'off';
:PREFIX
SELECT * FROM append_test a INNER JOIN join_test j ON (a.colorid = j.colorid)
WHERE a.time > now_s() - interval '3 hours' AND j.time > now_s() - interval '3 hours';
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:130: NOTICE:  Stable function now_s() called!
--- QUERY PLAN ---
 Nested Loop (actual rows=1.00 loops=1)
   Join Filter: (a.colorid = j.colorid)
   ->  Custom Scan (ChunkAppend) on append_test a (actual rows=1.00 loops=1)
         Chunks excluded during startup: 2
         ->  Seq Scan on _hyper_1_3_chunk a_1 (actual rows=1.00 loops=1)
               Filter: ("time" > (now_s() - '@ 3 hours'::interval))
   ->  Custom Scan (ChunkAppend) on join_test j (actual rows=1.00 loops=1)
         Chunks excluded during startup: 2
         ->  Seq Scan on _hyper_2_6_chunk j_1 (actual rows=1.00 loops=1)
               Filter: ("time" > (now_s() - '@ 3 hours'::interval))

reset enable_hashjoin;
reset enable_mergejoin;
reset enable_material;
-- test constraint_exclusion with date time dimension and DATE/TIMESTAMP/TIMESTAMPTZ constraints
-- the queries should all have 3 chunks
:PREFIX SELECT * FROM metrics_date WHERE time > '2000-01-15'::date ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_date (actual rows=4609.00 loops=1)
   Order: metrics_date."time"
   ->  Index Scan Backward using _hyper_3_9_chunk_metrics_date_time_idx on _hyper_3_9_chunk (actual rows=1152.00 loops=1)
         Index Cond: ("time" > '01-15-2000'::date)
   ->  Index Scan Backward using _hyper_3_10_chunk_metrics_date_time_idx on _hyper_3_10_chunk (actual rows=2016.00 loops=1)
   ->  Index Scan Backward using _hyper_3_11_chunk_metrics_date_time_idx on _hyper_3_11_chunk (actual rows=1441.00 loops=1)

:PREFIX SELECT * FROM metrics_date WHERE time > '2000-01-15'::timestamp ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_date (actual rows=4609.00 loops=1)
   Order: metrics_date."time"
   ->  Index Scan Backward using _hyper_3_9_chunk_metrics_date_time_idx on _hyper_3_9_chunk (actual rows=1152.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone)
   ->  Index Scan Backward using _hyper_3_10_chunk_metrics_date_time_idx on _hyper_3_10_chunk (actual rows=2016.00 loops=1)
   ->  Index Scan Backward using _hyper_3_11_chunk_metrics_date_time_idx on _hyper_3_11_chunk (actual rows=1441.00 loops=1)

:PREFIX SELECT * FROM metrics_date WHERE time > '2000-01-15'::timestamptz ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_date (actual rows=4609.00 loops=1)
   Order: metrics_date."time"
   Chunks excluded during startup: 2
   ->  Index Scan Backward using _hyper_3_9_chunk_metrics_date_time_idx on _hyper_3_9_chunk (actual rows=1152.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)
   ->  Index Scan Backward using _hyper_3_10_chunk_metrics_date_time_idx on _hyper_3_10_chunk (actual rows=2016.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)
   ->  Index Scan Backward using _hyper_3_11_chunk_metrics_date_time_idx on _hyper_3_11_chunk (actual rows=1441.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)

-- test Const OP Var
-- the queries should all have 3 chunks
:PREFIX SELECT * FROM metrics_date WHERE '2000-01-15'::date < time ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_date (actual rows=4609.00 loops=1)
   Order: metrics_date."time"
   ->  Index Scan Backward using _hyper_3_9_chunk_metrics_date_time_idx on _hyper_3_9_chunk (actual rows=1152.00 loops=1)
         Index Cond: ("time" > '01-15-2000'::date)
   ->  Index Scan Backward using _hyper_3_10_chunk_metrics_date_time_idx on _hyper_3_10_chunk (actual rows=2016.00 loops=1)
   ->  Index Scan Backward using _hyper_3_11_chunk_metrics_date_time_idx on _hyper_3_11_chunk (actual rows=1441.00 loops=1)

:PREFIX SELECT * FROM metrics_date WHERE '2000-01-15'::timestamp < time ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_date (actual rows=4609.00 loops=1)
   Order: metrics_date."time"
   ->  Index Scan Backward using _hyper_3_9_chunk_metrics_date_time_idx on _hyper_3_9_chunk (actual rows=1152.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone)
   ->  Index Scan Backward using _hyper_3_10_chunk_metrics_date_time_idx on _hyper_3_10_chunk (actual rows=2016.00 loops=1)
   ->  Index Scan Backward using _hyper_3_11_chunk_metrics_date_time_idx on _hyper_3_11_chunk (actual rows=1441.00 loops=1)

:PREFIX SELECT * FROM metrics_date WHERE '2000-01-15'::timestamptz < time ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_date (actual rows=4609.00 loops=1)
   Order: metrics_date."time"
   Chunks excluded during startup: 2
   ->  Index Scan Backward using _hyper_3_9_chunk_metrics_date_time_idx on _hyper_3_9_chunk (actual rows=1152.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)
   ->  Index Scan Backward using _hyper_3_10_chunk_metrics_date_time_idx on _hyper_3_10_chunk (actual rows=2016.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)
   ->  Index Scan Backward using _hyper_3_11_chunk_metrics_date_time_idx on _hyper_3_11_chunk (actual rows=1441.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)

-- test 2 constraints
-- the queries should all have 2 chunks
:PREFIX SELECT * FROM metrics_date WHERE time > '2000-01-15'::date AND time < '2000-01-21'::date ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_date (actual rows=1440.00 loops=1)
   Order: metrics_date."time"
   ->  Index Scan Backward using _hyper_3_9_chunk_metrics_date_time_idx on _hyper_3_9_chunk (actual rows=1152.00 loops=1)
         Index Cond: (("time" > '01-15-2000'::date) AND ("time" < '01-21-2000'::date))
   ->  Index Scan Backward using _hyper_3_10_chunk_metrics_date_time_idx on _hyper_3_10_chunk (actual rows=288.00 loops=1)
         Index Cond: (("time" > '01-15-2000'::date) AND ("time" < '01-21-2000'::date))

:PREFIX SELECT * FROM metrics_date WHERE time > '2000-01-15'::timestamp AND time < '2000-01-21'::timestamp ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_date (actual rows=1440.00 loops=1)
   Order: metrics_date."time"
   ->  Index Scan Backward using _hyper_3_9_chunk_metrics_date_time_idx on _hyper_3_9_chunk (actual rows=1152.00 loops=1)
         Index Cond: (("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Fri Jan 21 00:00:00 2000'::timestamp without time zone))
   ->  Index Scan Backward using _hyper_3_10_chunk_metrics_date_time_idx on _hyper_3_10_chunk (actual rows=288.00 loops=1)
         Index Cond: (("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Fri Jan 21 00:00:00 2000'::timestamp without time zone))

:PREFIX SELECT * FROM metrics_date WHERE time > '2000-01-15'::timestamptz AND time < '2000-01-21'::timestamptz ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_date (actual rows=1440.00 loops=1)
   Order: metrics_date."time"
   Chunks excluded during startup: 2
   ->  Index Scan Backward using _hyper_3_9_chunk_metrics_date_time_idx on _hyper_3_9_chunk (actual rows=1152.00 loops=1)
         Index Cond: (("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Fri Jan 21 00:00:00 2000 PST'::timestamp with time zone))
   ->  Index Scan Backward using _hyper_3_10_chunk_metrics_date_time_idx on _hyper_3_10_chunk (actual rows=288.00 loops=1)
         Index Cond: (("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Fri Jan 21 00:00:00 2000 PST'::timestamp with time zone))
   ->  Index Scan Backward using _hyper_3_11_chunk_metrics_date_time_idx on _hyper_3_11_chunk (actual rows=0.00 loops=1)
         Index Cond: (("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Fri Jan 21 00:00:00 2000 PST'::timestamp with time zone))

-- test constraint_exclusion with timestamp time dimension and DATE/TIMESTAMP/TIMESTAMPTZ constraints
-- the queries should all have 3 chunks
:PREFIX SELECT * FROM metrics_timestamp WHERE time > '2000-01-15'::date ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamp (actual rows=4896.00 loops=1)
   Order: metrics_timestamp."time"
   ->  Index Scan Backward using _hyper_4_14_chunk_metrics_timestamp_time_idx on _hyper_4_14_chunk (actual rows=1439.00 loops=1)
         Index Cond: ("time" > '01-15-2000'::date)
   ->  Index Scan Backward using _hyper_4_15_chunk_metrics_timestamp_time_idx on _hyper_4_15_chunk (actual rows=2016.00 loops=1)
   ->  Index Scan Backward using _hyper_4_16_chunk_metrics_timestamp_time_idx on _hyper_4_16_chunk (actual rows=1441.00 loops=1)

:PREFIX SELECT * FROM metrics_timestamp WHERE time > '2000-01-15'::timestamp ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamp (actual rows=4896.00 loops=1)
   Order: metrics_timestamp."time"
   ->  Index Scan Backward using _hyper_4_14_chunk_metrics_timestamp_time_idx on _hyper_4_14_chunk (actual rows=1439.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone)
   ->  Index Scan Backward using _hyper_4_15_chunk_metrics_timestamp_time_idx on _hyper_4_15_chunk (actual rows=2016.00 loops=1)
   ->  Index Scan Backward using _hyper_4_16_chunk_metrics_timestamp_time_idx on _hyper_4_16_chunk (actual rows=1441.00 loops=1)

:PREFIX SELECT * FROM metrics_timestamp WHERE time > '2000-01-15'::timestamptz ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamp (actual rows=4896.00 loops=1)
   Order: metrics_timestamp."time"
   Chunks excluded during startup: 2
   ->  Index Scan Backward using _hyper_4_14_chunk_metrics_timestamp_time_idx on _hyper_4_14_chunk (actual rows=1439.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)
   ->  Index Scan Backward using _hyper_4_15_chunk_metrics_timestamp_time_idx on _hyper_4_15_chunk (actual rows=2016.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)
   ->  Index Scan Backward using _hyper_4_16_chunk_metrics_timestamp_time_idx on _hyper_4_16_chunk (actual rows=1441.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)

-- test Const OP Var
-- the queries should all have 3 chunks
:PREFIX SELECT * FROM metrics_timestamp WHERE '2000-01-15'::date < time ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamp (actual rows=4896.00 loops=1)
   Order: metrics_timestamp."time"
   ->  Index Scan Backward using _hyper_4_14_chunk_metrics_timestamp_time_idx on _hyper_4_14_chunk (actual rows=1439.00 loops=1)
         Index Cond: ("time" > '01-15-2000'::date)
   ->  Index Scan Backward using _hyper_4_15_chunk_metrics_timestamp_time_idx on _hyper_4_15_chunk (actual rows=2016.00 loops=1)
   ->  Index Scan Backward using _hyper_4_16_chunk_metrics_timestamp_time_idx on _hyper_4_16_chunk (actual rows=1441.00 loops=1)

:PREFIX SELECT * FROM metrics_timestamp WHERE '2000-01-15'::timestamp < time ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamp (actual rows=4896.00 loops=1)
   Order: metrics_timestamp."time"
   ->  Index Scan Backward using _hyper_4_14_chunk_metrics_timestamp_time_idx on _hyper_4_14_chunk (actual rows=1439.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone)
   ->  Index Scan Backward using _hyper_4_15_chunk_metrics_timestamp_time_idx on _hyper_4_15_chunk (actual rows=2016.00 loops=1)
   ->  Index Scan Backward using _hyper_4_16_chunk_metrics_timestamp_time_idx on _hyper_4_16_chunk (actual rows=1441.00 loops=1)

:PREFIX SELECT * FROM metrics_timestamp WHERE '2000-01-15'::timestamptz < time ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamp (actual rows=4896.00 loops=1)
   Order: metrics_timestamp."time"
   Chunks excluded during startup: 2
   ->  Index Scan Backward using _hyper_4_14_chunk_metrics_timestamp_time_idx on _hyper_4_14_chunk (actual rows=1439.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)
   ->  Index Scan Backward using _hyper_4_15_chunk_metrics_timestamp_time_idx on _hyper_4_15_chunk (actual rows=2016.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)
   ->  Index Scan Backward using _hyper_4_16_chunk_metrics_timestamp_time_idx on _hyper_4_16_chunk (actual rows=1441.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)

-- test 2 constraints
-- the queries should all have 2 chunks
:PREFIX SELECT * FROM metrics_timestamp WHERE time > '2000-01-15'::date AND time < '2000-01-21'::date ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamp (actual rows=1727.00 loops=1)
   Order: metrics_timestamp."time"
   ->  Index Scan Backward using _hyper_4_14_chunk_metrics_timestamp_time_idx on _hyper_4_14_chunk (actual rows=1439.00 loops=1)
         Index Cond: (("time" > '01-15-2000'::date) AND ("time" < '01-21-2000'::date))
   ->  Index Scan Backward using _hyper_4_15_chunk_metrics_timestamp_time_idx on _hyper_4_15_chunk (actual rows=288.00 loops=1)
         Index Cond: (("time" > '01-15-2000'::date) AND ("time" < '01-21-2000'::date))

:PREFIX SELECT * FROM metrics_timestamp WHERE time > '2000-01-15'::timestamp AND time < '2000-01-21'::timestamp ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamp (actual rows=1727.00 loops=1)
   Order: metrics_timestamp."time"
   ->  Index Scan Backward using _hyper_4_14_chunk_metrics_timestamp_time_idx on _hyper_4_14_chunk (actual rows=1439.00 loops=1)
         Index Cond: (("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Fri Jan 21 00:00:00 2000'::timestamp without time zone))
   ->  Index Scan Backward using _hyper_4_15_chunk_metrics_timestamp_time_idx on _hyper_4_15_chunk (actual rows=288.00 loops=1)
         Index Cond: (("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Fri Jan 21 00:00:00 2000'::timestamp without time zone))

:PREFIX SELECT * FROM metrics_timestamp WHERE time > '2000-01-15'::timestamptz AND time < '2000-01-21'::timestamptz ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamp (actual rows=1727.00 loops=1)
   Order: metrics_timestamp."time"
   Chunks excluded during startup: 3
   ->  Index Scan Backward using _hyper_4_14_chunk_metrics_timestamp_time_idx on _hyper_4_14_chunk (actual rows=1439.00 loops=1)
         Index Cond: (("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Fri Jan 21 00:00:00 2000 PST'::timestamp with time zone))
   ->  Index Scan Backward using _hyper_4_15_chunk_metrics_timestamp_time_idx on _hyper_4_15_chunk (actual rows=288.00 loops=1)
         Index Cond: (("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Fri Jan 21 00:00:00 2000 PST'::timestamp with time zone))

-- test constraint_exclusion with timestamptz time dimension and DATE/TIMESTAMP/TIMESTAMPTZ constraints
-- the queries should all have 3 chunks
:PREFIX SELECT time FROM metrics_timestamptz WHERE time > '2000-01-15'::date ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=14688.00 loops=1)
   Order: metrics_timestamptz."time"
   Chunks excluded during startup: 2
   ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk (actual rows=4029.00 loops=1)
         Index Cond: ("time" > '01-15-2000'::date)
   ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk (actual rows=6048.00 loops=1)
         Index Cond: ("time" > '01-15-2000'::date)
   ->  Index Scan Backward using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk (actual rows=4611.00 loops=1)
         Index Cond: ("time" > '01-15-2000'::date)

:PREFIX SELECT time FROM metrics_timestamptz WHERE time > '2000-01-15'::timestamp ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=14688.00 loops=1)
   Order: metrics_timestamptz."time"
   Chunks excluded during startup: 2
   ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk (actual rows=4029.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone)
   ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk (actual rows=6048.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone)
   ->  Index Scan Backward using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk (actual rows=4611.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone)

:PREFIX SELECT time FROM metrics_timestamptz WHERE time > '2000-01-15'::timestamptz ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=14688.00 loops=1)
   Order: metrics_timestamptz."time"
   ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk (actual rows=4029.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)
   ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk (actual rows=6048.00 loops=1)
   ->  Index Scan Backward using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk (actual rows=4611.00 loops=1)

-- test Const OP Var
-- the queries should all have 3 chunks
:PREFIX SELECT time FROM metrics_timestamptz WHERE '2000-01-15'::date < time ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=14688.00 loops=1)
   Order: metrics_timestamptz."time"
   Chunks excluded during startup: 2
   ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk (actual rows=4029.00 loops=1)
         Index Cond: ("time" > '01-15-2000'::date)
   ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk (actual rows=6048.00 loops=1)
         Index Cond: ("time" > '01-15-2000'::date)
   ->  Index Scan Backward using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk (actual rows=4611.00 loops=1)
         Index Cond: ("time" > '01-15-2000'::date)

:PREFIX SELECT time FROM metrics_timestamptz WHERE '2000-01-15'::timestamp < time ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=14688.00 loops=1)
   Order: metrics_timestamptz."time"
   Chunks excluded during startup: 2
   ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk (actual rows=4029.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone)
   ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk (actual rows=6048.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone)
   ->  Index Scan Backward using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk (actual rows=4611.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone)

:PREFIX SELECT time FROM metrics_timestamptz WHERE '2000-01-15'::timestamptz < time ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=14688.00 loops=1)
   Order: metrics_timestamptz."time"
   ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk (actual rows=4029.00 loops=1)
         Index Cond: ("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone)
   ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk (actual rows=6048.00 loops=1)
   ->  Index Scan Backward using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk (actual rows=4611.00 loops=1)

-- test 2 constraints
-- the queries should all have 2 chunks
:PREFIX SELECT time FROM metrics_timestamptz WHERE time > '2000-01-15'::date AND time < '2000-01-21'::date ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=5181.00 loops=1)
   Order: metrics_timestamptz."time"
   Chunks excluded during startup: 3
   ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk (actual rows=4029.00 loops=1)
         Index Cond: (("time" > '01-15-2000'::date) AND ("time" < '01-21-2000'::date))
   ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk (actual rows=1152.00 loops=1)
         Index Cond: (("time" > '01-15-2000'::date) AND ("time" < '01-21-2000'::date))

:PREFIX SELECT time FROM metrics_timestamptz WHERE time > '2000-01-15'::timestamp AND time < '2000-01-21'::timestamp ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=5181.00 loops=1)
   Order: metrics_timestamptz."time"
   Chunks excluded during startup: 3
   ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk (actual rows=4029.00 loops=1)
         Index Cond: (("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Fri Jan 21 00:00:00 2000'::timestamp without time zone))
   ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk (actual rows=1152.00 loops=1)
         Index Cond: (("time" > 'Sat Jan 15 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Fri Jan 21 00:00:00 2000'::timestamp without time zone))

:PREFIX SELECT time FROM metrics_timestamptz WHERE time > '2000-01-15'::timestamptz AND time < '2000-01-21'::timestamptz ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=5181.00 loops=1)
   Order: metrics_timestamptz."time"
   ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk (actual rows=4029.00 loops=1)
         Index Cond: (("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Fri Jan 21 00:00:00 2000 PST'::timestamp with time zone))
   ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk (actual rows=1152.00 loops=1)
         Index Cond: (("time" > 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Fri Jan 21 00:00:00 2000 PST'::timestamp with time zone))

-- test constraint_exclusion with space partitioning and DATE/TIMESTAMP/TIMESTAMPTZ constraints
-- exclusion for constraints with non-matching datatypes not working for space partitioning atm
:PREFIX SELECT time FROM metrics_space WHERE time > '2000-01-10'::date ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=11520.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_22_chunk_metrics_space_time_idx on _hyper_6_22_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
         ->  Index Scan Backward using _hyper_6_23_chunk_metrics_space_time_idx on _hyper_6_23_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
         ->  Index Scan Backward using _hyper_6_24_chunk_metrics_space_time_idx on _hyper_6_24_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
   ->  Merge Append (actual rows=7670.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=3068.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=3068.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
         ->  Index Scan Backward using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (actual rows=1534.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
   ->  Merge Append (actual rows=3850.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=1540.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=1540.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
         ->  Index Scan Backward using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (actual rows=770.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)

:PREFIX SELECT time FROM metrics_space WHERE time > '2000-01-10'::timestamp ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=11520.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_22_chunk_metrics_space_time_idx on _hyper_6_22_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
         ->  Index Scan Backward using _hyper_6_23_chunk_metrics_space_time_idx on _hyper_6_23_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
         ->  Index Scan Backward using _hyper_6_24_chunk_metrics_space_time_idx on _hyper_6_24_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
   ->  Merge Append (actual rows=7670.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=3068.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=3068.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
         ->  Index Scan Backward using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (actual rows=1534.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
   ->  Merge Append (actual rows=3850.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=1540.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=1540.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
         ->  Index Scan Backward using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (actual rows=770.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)

:PREFIX SELECT time FROM metrics_space WHERE time > '2000-01-10'::timestamptz ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=11520.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=7670.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=3068.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=3068.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
         ->  Index Scan Backward using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (actual rows=1534.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
   ->  Merge Append (actual rows=3850.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=1540.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=1540.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
         ->  Index Scan Backward using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (actual rows=770.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)

-- test Const OP Var
-- exclusion for constraints with non-matching datatypes not working for space partitioning atm
:PREFIX SELECT time FROM metrics_space WHERE '2000-01-10'::date < time ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=11520.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_22_chunk_metrics_space_time_idx on _hyper_6_22_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
         ->  Index Scan Backward using _hyper_6_23_chunk_metrics_space_time_idx on _hyper_6_23_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
         ->  Index Scan Backward using _hyper_6_24_chunk_metrics_space_time_idx on _hyper_6_24_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
   ->  Merge Append (actual rows=7670.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=3068.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=3068.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
         ->  Index Scan Backward using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (actual rows=1534.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
   ->  Merge Append (actual rows=3850.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=1540.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=1540.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)
         ->  Index Scan Backward using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (actual rows=770.00 loops=1)
               Index Cond: ("time" > '01-10-2000'::date)

:PREFIX SELECT time FROM metrics_space WHERE '2000-01-10'::timestamp < time ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=11520.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_22_chunk_metrics_space_time_idx on _hyper_6_22_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
         ->  Index Scan Backward using _hyper_6_23_chunk_metrics_space_time_idx on _hyper_6_23_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
         ->  Index Scan Backward using _hyper_6_24_chunk_metrics_space_time_idx on _hyper_6_24_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
   ->  Merge Append (actual rows=7670.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=3068.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=3068.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
         ->  Index Scan Backward using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (actual rows=1534.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
   ->  Merge Append (actual rows=3850.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=1540.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=1540.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)
         ->  Index Scan Backward using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (actual rows=770.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone)

:PREFIX SELECT time FROM metrics_space WHERE '2000-01-10'::timestamptz < time ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=11520.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=7670.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=3068.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=3068.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
         ->  Index Scan Backward using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (actual rows=1534.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
   ->  Merge Append (actual rows=3850.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=1540.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=1540.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
         ->  Index Scan Backward using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (actual rows=770.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)

-- test 2 constraints
-- exclusion for constraints with non-matching datatypes not working for space partitioning atm
:PREFIX SELECT time FROM metrics_space WHERE time > '2000-01-10'::date AND time < '2000-01-15'::date ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=11520.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_22_chunk_metrics_space_time_idx on _hyper_6_22_chunk (actual rows=0.00 loops=1)
               Index Cond: (("time" > '01-10-2000'::date) AND ("time" < '01-15-2000'::date))
         ->  Index Scan Backward using _hyper_6_23_chunk_metrics_space_time_idx on _hyper_6_23_chunk (actual rows=0.00 loops=1)
               Index Cond: (("time" > '01-10-2000'::date) AND ("time" < '01-15-2000'::date))
         ->  Index Scan Backward using _hyper_6_24_chunk_metrics_space_time_idx on _hyper_6_24_chunk (actual rows=0.00 loops=1)
               Index Cond: (("time" > '01-10-2000'::date) AND ("time" < '01-15-2000'::date))
   ->  Merge Append (actual rows=7670.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=3068.00 loops=1)
               Index Cond: (("time" > '01-10-2000'::date) AND ("time" < '01-15-2000'::date))
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=3068.00 loops=1)
               Index Cond: (("time" > '01-10-2000'::date) AND ("time" < '01-15-2000'::date))
         ->  Index Scan Backward using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (actual rows=1534.00 loops=1)
               Index Cond: (("time" > '01-10-2000'::date) AND ("time" < '01-15-2000'::date))
   ->  Merge Append (actual rows=3850.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=1540.00 loops=1)
               Index Cond: (("time" > '01-10-2000'::date) AND ("time" < '01-15-2000'::date))
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=1540.00 loops=1)
               Index Cond: (("time" > '01-10-2000'::date) AND ("time" < '01-15-2000'::date))
         ->  Index Scan Backward using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (actual rows=770.00 loops=1)
               Index Cond: (("time" > '01-10-2000'::date) AND ("time" < '01-15-2000'::date))

:PREFIX SELECT time FROM metrics_space WHERE time > '2000-01-10'::timestamp AND time < '2000-01-15'::timestamp ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=11520.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_22_chunk_metrics_space_time_idx on _hyper_6_22_chunk (actual rows=0.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000'::timestamp without time zone))
         ->  Index Scan Backward using _hyper_6_23_chunk_metrics_space_time_idx on _hyper_6_23_chunk (actual rows=0.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000'::timestamp without time zone))
         ->  Index Scan Backward using _hyper_6_24_chunk_metrics_space_time_idx on _hyper_6_24_chunk (actual rows=0.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000'::timestamp without time zone))
   ->  Merge Append (actual rows=7670.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=3068.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000'::timestamp without time zone))
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=3068.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000'::timestamp without time zone))
         ->  Index Scan Backward using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (actual rows=1534.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000'::timestamp without time zone))
   ->  Merge Append (actual rows=3850.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=1540.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000'::timestamp without time zone))
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=1540.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000'::timestamp without time zone))
         ->  Index Scan Backward using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (actual rows=770.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000'::timestamp without time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000'::timestamp without time zone))

:PREFIX SELECT time FROM metrics_space WHERE time > '2000-01-10'::timestamptz AND time < '2000-01-15'::timestamptz ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=11520.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=7670.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=3068.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone))
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=3068.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone))
         ->  Index Scan Backward using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (actual rows=1534.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone))
   ->  Merge Append (actual rows=3850.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=1540.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone))
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=1540.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone))
         ->  Index Scan Backward using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (actual rows=770.00 loops=1)
               Index Cond: (("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone) AND ("time" < 'Sat Jan 15 00:00:00 2000 PST'::timestamp with time zone))

-- test filtering on space partition
:PREFIX SELECT time FROM metrics_space WHERE time > '2000-01-10'::timestamptz AND device_id = 1 ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=1152.00 loops=1)
   Order: metrics_space."time"
   ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_device_id_time_idx on _hyper_6_25_chunk (actual rows=767.00 loops=1)
         Index Cond: ((device_id = 1) AND ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone))
   ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_device_id_time_idx on _hyper_6_28_chunk (actual rows=385.00 loops=1)
         Index Cond: ((device_id = 1) AND ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone))

:PREFIX SELECT time FROM metrics_space WHERE time > '2000-01-10'::timestamptz AND device_id IN (1,2) ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=2304.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=1534.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=767.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
               Filter: (device_id = ANY ('{1,2}'::integer[]))
               Rows Removed by Filter: 2301
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=767.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
               Filter: (device_id = ANY ('{1,2}'::integer[]))
               Rows Removed by Filter: 2301
   ->  Merge Append (actual rows=770.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=385.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
               Filter: (device_id = ANY ('{1,2}'::integer[]))
               Rows Removed by Filter: 1155
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=385.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
               Filter: (device_id = ANY ('{1,2}'::integer[]))
               Rows Removed by Filter: 1155

:PREFIX SELECT time FROM metrics_space WHERE time > '2000-01-10'::timestamptz AND device_id IN (VALUES(1)) ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=1152.00 loops=1)
   Order: metrics_space."time"
   ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_device_id_time_idx on _hyper_6_25_chunk (actual rows=767.00 loops=1)
         Index Cond: ((device_id = 1) AND ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone))
   ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_device_id_time_idx on _hyper_6_28_chunk (actual rows=385.00 loops=1)
         Index Cond: ((device_id = 1) AND ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone))

:PREFIX SELECT time FROM metrics_space WHERE time > '2000-01-10'::timestamptz AND v3 IN (VALUES('1')) ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=1152.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=767.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=767.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
               Filter: (v3 = '1'::text)
               Rows Removed by Filter: 2301
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
               Filter: (v3 = '1'::text)
               Rows Removed by Filter: 3068
         ->  Index Scan Backward using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
               Filter: (v3 = '1'::text)
               Rows Removed by Filter: 1534
   ->  Merge Append (actual rows=385.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=385.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
               Filter: (v3 = '1'::text)
               Rows Removed by Filter: 1155
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
               Filter: (v3 = '1'::text)
               Rows Removed by Filter: 1540
         ->  Index Scan Backward using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
               Filter: (v3 = '1'::text)
               Rows Removed by Filter: 770

:PREFIX SELECT * FROM metrics_space
WHERE time = (VALUES ('2019-12-24' at time zone 'UTC'))
  AND v3 NOT IN (VALUES ('1'));
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=0.00 loops=1)
   Chunks excluded during startup: 0
   Chunks excluded during runtime: 9
   InitPlan 1 (returns $0)
     ->  Result (actual rows=1.00 loops=1)
   ->  Index Scan using _hyper_6_22_chunk_metrics_space_time_idx on _hyper_6_22_chunk (never executed)
         Index Cond: ("time" = $0)
         Filter: (NOT (hashed SubPlan 2))
         SubPlan 2
           ->  Result (never executed)
   ->  Index Scan using _hyper_6_23_chunk_metrics_space_time_idx on _hyper_6_23_chunk (never executed)
         Index Cond: ("time" = $0)
         Filter: (NOT (hashed SubPlan 2))
   ->  Index Scan using _hyper_6_24_chunk_metrics_space_time_idx on _hyper_6_24_chunk (never executed)
         Index Cond: ("time" = $0)
         Filter: (NOT (hashed SubPlan 2))
   ->  Index Scan using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (never executed)
         Index Cond: ("time" = $0)
         Filter: (NOT (hashed SubPlan 2))
   ->  Index Scan using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (never executed)
         Index Cond: ("time" = $0)
         Filter: (NOT (hashed SubPlan 2))
   ->  Index Scan using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (never executed)
         Index Cond: ("time" = $0)
         Filter: (NOT (hashed SubPlan 2))
   ->  Index Scan using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (never executed)
         Index Cond: ("time" = $0)
         Filter: (NOT (hashed SubPlan 2))
   ->  Index Scan using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (never executed)
         Index Cond: ("time" = $0)
         Filter: (NOT (hashed SubPlan 2))
   ->  Index Scan using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (never executed)
         Index Cond: ("time" = $0)
         Filter: (NOT (hashed SubPlan 2))

-- test CURRENT_DATE
-- should be 0 chunks
:PREFIX SELECT time FROM metrics_date WHERE time > CURRENT_DATE ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_date (actual rows=0.00 loops=1)
   Order: metrics_date."time"
   Chunks excluded during startup: 5

:PREFIX SELECT time FROM metrics_timestamp WHERE time > CURRENT_DATE ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamp (actual rows=0.00 loops=1)
   Order: metrics_timestamp."time"
   Chunks excluded during startup: 5

:PREFIX SELECT time FROM metrics_timestamptz WHERE time > CURRENT_DATE ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=0.00 loops=1)
   Order: metrics_timestamptz."time"
   Chunks excluded during startup: 5

:PREFIX SELECT time FROM metrics_space WHERE time > CURRENT_DATE ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=0.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_22_chunk_metrics_space_time_idx on _hyper_6_22_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_DATE)
         ->  Index Scan Backward using _hyper_6_23_chunk_metrics_space_time_idx on _hyper_6_23_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_DATE)
         ->  Index Scan Backward using _hyper_6_24_chunk_metrics_space_time_idx on _hyper_6_24_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_DATE)
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_DATE)
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_DATE)
         ->  Index Scan Backward using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_DATE)
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_DATE)
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_DATE)
         ->  Index Scan Backward using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_DATE)

-- test CURRENT_TIMESTAMP
-- should be 0 chunks
:PREFIX SELECT time FROM metrics_date WHERE time > CURRENT_TIMESTAMP ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_date (actual rows=0.00 loops=1)
   Order: metrics_date."time"
   Chunks excluded during startup: 5

:PREFIX SELECT time FROM metrics_timestamp WHERE time > CURRENT_TIMESTAMP ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamp (actual rows=0.00 loops=1)
   Order: metrics_timestamp."time"
   Chunks excluded during startup: 5

:PREFIX SELECT time FROM metrics_timestamptz WHERE time > CURRENT_TIMESTAMP ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=0.00 loops=1)
   Order: metrics_timestamptz."time"
   Chunks excluded during startup: 5

:PREFIX SELECT time FROM metrics_space WHERE time > CURRENT_TIMESTAMP ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=0.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_22_chunk_metrics_space_time_idx on _hyper_6_22_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_TIMESTAMP)
         ->  Index Scan Backward using _hyper_6_23_chunk_metrics_space_time_idx on _hyper_6_23_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_TIMESTAMP)
         ->  Index Scan Backward using _hyper_6_24_chunk_metrics_space_time_idx on _hyper_6_24_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_TIMESTAMP)
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_TIMESTAMP)
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_TIMESTAMP)
         ->  Index Scan Backward using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_TIMESTAMP)
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_TIMESTAMP)
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_TIMESTAMP)
         ->  Index Scan Backward using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > CURRENT_TIMESTAMP)

-- test now()
-- should be 0 chunks
:PREFIX SELECT time FROM metrics_date WHERE time > now() ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_date (actual rows=0.00 loops=1)
   Order: metrics_date."time"
   Chunks excluded during startup: 5

:PREFIX SELECT time FROM metrics_timestamp WHERE time > now() ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamp (actual rows=0.00 loops=1)
   Order: metrics_timestamp."time"
   Chunks excluded during startup: 5

:PREFIX SELECT time FROM metrics_timestamptz WHERE time > now() ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=0.00 loops=1)
   Order: metrics_timestamptz."time"
   Chunks excluded during startup: 5

:PREFIX SELECT time FROM metrics_space WHERE time > now() ORDER BY time;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_space (actual rows=0.00 loops=1)
   Order: metrics_space."time"
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_22_chunk_metrics_space_time_idx on _hyper_6_22_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > now())
         ->  Index Scan Backward using _hyper_6_23_chunk_metrics_space_time_idx on _hyper_6_23_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > now())
         ->  Index Scan Backward using _hyper_6_24_chunk_metrics_space_time_idx on _hyper_6_24_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > now())
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_25_chunk_metrics_space_time_idx on _hyper_6_25_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > now())
         ->  Index Scan Backward using _hyper_6_26_chunk_metrics_space_time_idx on _hyper_6_26_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > now())
         ->  Index Scan Backward using _hyper_6_27_chunk_metrics_space_time_idx on _hyper_6_27_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > now())
   ->  Merge Append (actual rows=0.00 loops=1)
         Sort Key: metrics_space."time"
         ->  Index Scan Backward using _hyper_6_28_chunk_metrics_space_time_idx on _hyper_6_28_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > now())
         ->  Index Scan Backward using _hyper_6_29_chunk_metrics_space_time_idx on _hyper_6_29_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > now())
         ->  Index Scan Backward using _hyper_6_30_chunk_metrics_space_time_idx on _hyper_6_30_chunk (actual rows=0.00 loops=1)
               Index Cond: ("time" > now())

-- query with tablesample and planner exclusion
:PREFIX
SELECT * FROM metrics_date TABLESAMPLE BERNOULLI(5) REPEATABLE(0)
WHERE time > '2000-01-15'
ORDER BY time DESC;
--- QUERY PLAN ---
 Sort (actual rows=217.00 loops=1)
   Sort Key: metrics_date."time" DESC
   Sort Method: quicksort 
   ->  Append (actual rows=217.00 loops=1)
         ->  Sample Scan on _hyper_3_11_chunk (actual rows=72.00 loops=1)
               Sampling: bernoulli ('5'::real) REPEATABLE ('0'::double precision)
         ->  Sample Scan on _hyper_3_10_chunk (actual rows=94.00 loops=1)
               Sampling: bernoulli ('5'::real) REPEATABLE ('0'::double precision)
         ->  Sample Scan on _hyper_3_9_chunk (actual rows=51.00 loops=1)
               Sampling: bernoulli ('5'::real) REPEATABLE ('0'::double precision)
               Filter: ("time" > '01-15-2000'::date)
               Rows Removed by Filter: 43

-- query with tablesample and startup exclusion
:PREFIX
SELECT * FROM metrics_date TABLESAMPLE BERNOULLI(5) REPEATABLE(0)
WHERE time > '2000-01-15'::text::date
ORDER BY time DESC;
--- QUERY PLAN ---
 Sort (actual rows=217.00 loops=1)
   Sort Key: metrics_date."time" DESC
   Sort Method: quicksort 
   ->  Custom Scan (ChunkAppend) on metrics_date (actual rows=217.00 loops=1)
         Chunks excluded during startup: 2
         ->  Sample Scan on _hyper_3_11_chunk (actual rows=72.00 loops=1)
               Sampling: bernoulli ('5'::real) REPEATABLE ('0'::double precision)
               Filter: ("time" > ('2000-01-15'::cstring)::date)
         ->  Sample Scan on _hyper_3_10_chunk (actual rows=94.00 loops=1)
               Sampling: bernoulli ('5'::real) REPEATABLE ('0'::double precision)
               Filter: ("time" > ('2000-01-15'::cstring)::date)
         ->  Sample Scan on _hyper_3_9_chunk (actual rows=51.00 loops=1)
               Sampling: bernoulli ('5'::real) REPEATABLE ('0'::double precision)
               Filter: ("time" > ('2000-01-15'::cstring)::date)
               Rows Removed by Filter: 43

-- query with tablesample, space partitioning and planner exclusion
:PREFIX
SELECT * FROM metrics_space TABLESAMPLE BERNOULLI(5) REPEATABLE(0)
WHERE time > '2000-01-10'::timestamptz
ORDER BY time DESC, device_id;
--- QUERY PLAN ---
 Sort (actual rows=522.00 loops=1)
   Sort Key: metrics_space."time" DESC, metrics_space.device_id
   Sort Method: quicksort 
   ->  Append (actual rows=522.00 loops=1)
         ->  Sample Scan on _hyper_6_30_chunk (actual rows=35.00 loops=1)
               Sampling: bernoulli ('5'::real) REPEATABLE ('0'::double precision)
               Filter: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
         ->  Sample Scan on _hyper_6_29_chunk (actual rows=61.00 loops=1)
               Sampling: bernoulli ('5'::real) REPEATABLE ('0'::double precision)
               Filter: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
         ->  Sample Scan on _hyper_6_28_chunk (actual rows=61.00 loops=1)
               Sampling: bernoulli ('5'::real) REPEATABLE ('0'::double precision)
               Filter: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
         ->  Sample Scan on _hyper_6_27_chunk (actual rows=65.00 loops=1)
               Sampling: bernoulli ('5'::real) REPEATABLE ('0'::double precision)
               Filter: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
               Rows Removed by Filter: 113
         ->  Sample Scan on _hyper_6_26_chunk (actual rows=150.00 loops=1)
               Sampling: bernoulli ('5'::real) REPEATABLE ('0'::double precision)
               Filter: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
               Rows Removed by Filter: 218
         ->  Sample Scan on _hyper_6_25_chunk (actual rows=150.00 loops=1)
               Sampling: bernoulli ('5'::real) REPEATABLE ('0'::double precision)
               Filter: ("time" > 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
               Rows Removed by Filter: 218

-- test runtime exclusion
-- test runtime exclusion with LATERAL and 2 hypertables
:PREFIX SELECT m1.time, m2.time FROM metrics_timestamptz m1 LEFT JOIN LATERAL(SELECT time FROM metrics_timestamptz m2 WHERE m1.time = m2.time LIMIT 1) m2 ON true ORDER BY m1.time;
--- QUERY PLAN ---
 Nested Loop Left Join (actual rows=26787.00 loops=1)
   ->  Custom Scan (ChunkAppend) on metrics_timestamptz m1 (actual rows=26787.00 loops=1)
         Order: m1."time"
         ->  Index Scan Backward using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk m1_1 (actual rows=4032.00 loops=1)
         ->  Index Scan Backward using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk m1_2 (actual rows=6048.00 loops=1)
         ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk m1_3 (actual rows=6048.00 loops=1)
         ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk m1_4 (actual rows=6048.00 loops=1)
         ->  Index Scan Backward using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk m1_5 (actual rows=4611.00 loops=1)
   ->  Limit (actual rows=1.00 loops=26787)
         ->  Custom Scan (ChunkAppend) on metrics_timestamptz m2 (actual rows=1.00 loops=26787)
               Chunks excluded during runtime: 4
               ->  Index Scan using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk m2_1 (actual rows=1.00 loops=4032)
                     Index Cond: ("time" = m1."time")
               ->  Index Scan using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk m2_2 (actual rows=1.00 loops=6048)
                     Index Cond: ("time" = m1."time")
               ->  Index Scan using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk m2_3 (actual rows=1.00 loops=6048)
                     Index Cond: ("time" = m1."time")
               ->  Index Scan using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk m2_4 (actual rows=1.00 loops=6048)
                     Index Cond: ("time" = m1."time")
               ->  Index Scan using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk m2_5 (actual rows=1.00 loops=4611)
                     Index Cond: ("time" = m1."time")

-- test runtime exclusion and startup exclusions
:PREFIX SELECT m1.time, m2.time FROM metrics_timestamptz m1 LEFT JOIN LATERAL(SELECT time FROM metrics_timestamptz m2 WHERE m1.time = m2.time AND m2.time < '2000-01-10'::text::timestamptz LIMIT 1) m2 ON true ORDER BY m1.time;
--- QUERY PLAN ---
 Nested Loop Left Join (actual rows=26787.00 loops=1)
   ->  Custom Scan (ChunkAppend) on metrics_timestamptz m1 (actual rows=26787.00 loops=1)
         Order: m1."time"
         ->  Index Scan Backward using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk m1_1 (actual rows=4032.00 loops=1)
         ->  Index Scan Backward using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk m1_2 (actual rows=6048.00 loops=1)
         ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk m1_3 (actual rows=6048.00 loops=1)
         ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk m1_4 (actual rows=6048.00 loops=1)
         ->  Index Scan Backward using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk m1_5 (actual rows=4611.00 loops=1)
   ->  Limit (actual rows=0.00 loops=26787)
         ->  Custom Scan (ChunkAppend) on metrics_timestamptz m2 (actual rows=0.00 loops=26787)
               Chunks excluded during startup: 3
               Chunks excluded during runtime: 1
               ->  Index Scan using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk m2_1 (actual rows=1.00 loops=4032)
                     Index Cond: (("time" < ('2000-01-10'::cstring)::timestamp with time zone) AND ("time" = m1."time"))
               ->  Index Scan using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk m2_2 (actual rows=1.00 loops=6048)
                     Index Cond: (("time" < ('2000-01-10'::cstring)::timestamp with time zone) AND ("time" = m1."time"))

-- test runtime exclusion does not activate for constraints on non-partitioning columns
-- should not use runtime exclusion
:PREFIX SELECT * FROM append_test a LEFT JOIN LATERAL(SELECT * FROM join_test j WHERE a.colorid = j.colorid ORDER BY time DESC LIMIT 1) j ON true ORDER BY a.time LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Nested Loop Left Join (actual rows=1.00 loops=1)
         ->  Custom Scan (ChunkAppend) on append_test a (actual rows=1.00 loops=1)
               Order: a."time"
               ->  Index Scan Backward using _hyper_1_1_chunk_append_test_time_idx on _hyper_1_1_chunk a_1 (actual rows=1.00 loops=1)
               ->  Index Scan Backward using _hyper_1_2_chunk_append_test_time_idx on _hyper_1_2_chunk a_2 (never executed)
               ->  Index Scan Backward using _hyper_1_3_chunk_append_test_time_idx on _hyper_1_3_chunk a_3 (never executed)
         ->  Limit (actual rows=1.00 loops=1)
               ->  Custom Scan (ChunkAppend) on join_test j (actual rows=1.00 loops=1)
                     Order: j."time" DESC
                     Hypertables excluded during runtime: 0
                     ->  Index Scan using _hyper_2_6_chunk_join_test_time_idx on _hyper_2_6_chunk j_1 (actual rows=0.00 loops=1)
                           Filter: (a.colorid = colorid)
                           Rows Removed by Filter: 1
                     ->  Index Scan using _hyper_2_5_chunk_join_test_time_idx on _hyper_2_5_chunk j_2 (actual rows=0.00 loops=1)
                           Filter: (a.colorid = colorid)
                           Rows Removed by Filter: 1
                     ->  Index Scan using _hyper_2_4_chunk_join_test_time_idx on _hyper_2_4_chunk j_3 (actual rows=1.00 loops=1)
                           Filter: (a.colorid = colorid)

-- test runtime exclusion with LATERAL and generate_series
:PREFIX SELECT g.time FROM generate_series('2000-01-01'::timestamptz, '2000-02-01'::timestamptz, '1d'::interval) g(time) LEFT JOIN LATERAL(SELECT time FROM metrics_timestamptz m WHERE m.time=g.time LIMIT 1) m ON true;
--- QUERY PLAN ---
 Nested Loop Left Join (actual rows=32.00 loops=1)
   ->  Function Scan on generate_series g (actual rows=32.00 loops=1)
   ->  Limit (actual rows=1.00 loops=32)
         ->  Result (actual rows=1.00 loops=32)
               ->  Custom Scan (ChunkAppend) on metrics_timestamptz m (actual rows=1.00 loops=32)
                     Chunks excluded during runtime: 4
                     ->  Index Scan using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk m_1 (actual rows=1.00 loops=5)
                           Index Cond: ("time" = g."time")
                     ->  Index Scan using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk m_2 (actual rows=1.00 loops=7)
                           Index Cond: ("time" = g."time")
                     ->  Index Scan using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk m_3 (actual rows=1.00 loops=7)
                           Index Cond: ("time" = g."time")
                     ->  Index Scan using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk m_4 (actual rows=1.00 loops=7)
                           Index Cond: ("time" = g."time")
                     ->  Index Scan using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk m_5 (actual rows=1.00 loops=6)
                           Index Cond: ("time" = g."time")

:PREFIX SELECT * FROM generate_series('2000-01-01'::timestamptz,'2000-02-01'::timestamptz,'1d'::interval) AS g(time) INNER JOIN LATERAL (SELECT time FROM metrics_timestamptz m WHERE time=g.time) m ON true;
--- QUERY PLAN ---
 Hash Join (actual rows=96.00 loops=1)
   Hash Cond: (g."time" = m."time")
   ->  Function Scan on generate_series g (actual rows=32.00 loops=1)
   ->  Hash (actual rows=26787.00 loops=1)
         ->  Append (actual rows=26787.00 loops=1)
               ->  Seq Scan on _hyper_5_17_chunk m_1 (actual rows=4032.00 loops=1)
               ->  Seq Scan on _hyper_5_18_chunk m_2 (actual rows=6048.00 loops=1)
               ->  Seq Scan on _hyper_5_19_chunk m_3 (actual rows=6048.00 loops=1)
               ->  Seq Scan on _hyper_5_20_chunk m_4 (actual rows=6048.00 loops=1)
               ->  Seq Scan on _hyper_5_21_chunk m_5 (actual rows=4611.00 loops=1)

:PREFIX SELECT * FROM generate_series('2000-01-01'::timestamptz,'2000-02-01'::timestamptz,'1d'::interval) AS g(time) INNER JOIN LATERAL (SELECT time FROM metrics_timestamptz m WHERE time=g.time ORDER BY time) m ON true;
--- QUERY PLAN ---
 Nested Loop (actual rows=96.00 loops=1)
   ->  Function Scan on generate_series g (actual rows=32.00 loops=1)
   ->  Custom Scan (ChunkAppend) on metrics_timestamptz m (actual rows=3.00 loops=32)
         Chunks excluded during runtime: 4
         ->  Index Scan using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk m_1 (actual rows=3.00 loops=5)
               Index Cond: ("time" = g."time")
         ->  Index Scan using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk m_2 (actual rows=3.00 loops=7)
               Index Cond: ("time" = g."time")
         ->  Index Scan using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk m_3 (actual rows=3.00 loops=7)
               Index Cond: ("time" = g."time")
         ->  Index Scan using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk m_4 (actual rows=3.00 loops=7)
               Index Cond: ("time" = g."time")
         ->  Index Scan using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk m_5 (actual rows=3.00 loops=6)
               Index Cond: ("time" = g."time")

:PREFIX SELECT * FROM generate_series('2000-01-01'::timestamptz,'2000-02-01'::timestamptz,'1d'::interval) AS g(time) INNER JOIN LATERAL (SELECT time FROM metrics_timestamptz m WHERE time>g.time + '1 day' ORDER BY time LIMIT 1) m ON true;
--- QUERY PLAN ---
 Nested Loop (actual rows=30.00 loops=1)
   ->  Function Scan on generate_series g (actual rows=32.00 loops=1)
   ->  Limit (actual rows=1.00 loops=32)
         ->  Custom Scan (ChunkAppend) on metrics_timestamptz m (actual rows=1.00 loops=32)
               Order: m."time"
               Chunks excluded during startup: 0
               Chunks excluded during runtime: 2
               ->  Index Scan Backward using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk m_1 (actual rows=1.00 loops=4)
                     Index Cond: ("time" > (g."time" + '@ 1 day'::interval))
               ->  Index Scan Backward using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk m_2 (actual rows=1.00 loops=7)
                     Index Cond: ("time" > (g."time" + '@ 1 day'::interval))
               ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk m_3 (actual rows=1.00 loops=7)
                     Index Cond: ("time" > (g."time" + '@ 1 day'::interval))
               ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk m_4 (actual rows=1.00 loops=7)
                     Index Cond: ("time" > (g."time" + '@ 1 day'::interval))
               ->  Index Scan Backward using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk m_5 (actual rows=1.00 loops=7)
                     Index Cond: ("time" > (g."time" + '@ 1 day'::interval))

-- test runtime exclusion with subquery
:PREFIX SELECT m1.time FROM metrics_timestamptz m1 WHERE m1.time=(SELECT max(time) FROM metrics_timestamptz);
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz m1 (actual rows=3.00 loops=1)
   Chunks excluded during runtime: 4
   InitPlan 2 (returns $1)
     ->  Result (actual rows=1.00 loops=1)
           InitPlan 1 (returns $0)
             ->  Limit (actual rows=1.00 loops=1)
                   ->  Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=1.00 loops=1)
                         Order: metrics_timestamptz."time" DESC
                         ->  Index Scan using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk (actual rows=1.00 loops=1)
                               Index Cond: ("time" IS NOT NULL)
                         ->  Index Scan using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk (never executed)
                               Index Cond: ("time" IS NOT NULL)
                         ->  Index Scan using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk (never executed)
                               Index Cond: ("time" IS NOT NULL)
                         ->  Index Scan using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk (never executed)
                               Index Cond: ("time" IS NOT NULL)
                         ->  Index Scan using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk (never executed)
                               Index Cond: ("time" IS NOT NULL)
   ->  Index Scan using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk m1_1 (never executed)
         Index Cond: ("time" = $1)
   ->  Index Scan using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk m1_2 (never executed)
         Index Cond: ("time" = $1)
   ->  Index Scan using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk m1_3 (never executed)
         Index Cond: ("time" = $1)
   ->  Index Scan using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk m1_4 (never executed)
         Index Cond: ("time" = $1)
   ->  Index Scan using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk m1_5 (actual rows=3.00 loops=1)
         Index Cond: ("time" = $1)

-- test runtime exclusion with correlated subquery
:PREFIX SELECT m1.time, (SELECT m2.time FROM metrics_timestamptz m2 WHERE m2.time < m1.time ORDER BY m2.time DESC LIMIT 1) FROM metrics_timestamptz m1 WHERE m1.time < '2000-01-10' ORDER BY m1.time;
--- QUERY PLAN ---
 Result (actual rows=7776.00 loops=1)
   ->  Custom Scan (ChunkAppend) on metrics_timestamptz m1 (actual rows=7776.00 loops=1)
         Order: m1."time"
         ->  Index Scan Backward using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk m1_1 (actual rows=4032.00 loops=1)
         ->  Index Scan Backward using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk m1_2 (actual rows=3744.00 loops=1)
               Index Cond: ("time" < 'Mon Jan 10 00:00:00 2000 PST'::timestamp with time zone)
   SubPlan 1
     ->  Limit (actual rows=1.00 loops=7776)
           ->  Custom Scan (ChunkAppend) on metrics_timestamptz m2 (actual rows=1.00 loops=7776)
                 Order: m2."time" DESC
                 Chunks excluded during runtime: 3
                 ->  Index Scan using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk m2_1 (never executed)
                       Index Cond: ("time" < m1."time")
                 ->  Index Scan using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk m2_2 (never executed)
                       Index Cond: ("time" < m1."time")
                 ->  Index Scan using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk m2_3 (never executed)
                       Index Cond: ("time" < m1."time")
                 ->  Index Scan using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk m2_4 (actual rows=1.00 loops=3741)
                       Index Cond: ("time" < m1."time")
                 ->  Index Scan using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk m2_5 (actual rows=1.00 loops=4035)
                       Index Cond: ("time" < m1."time")

-- test EXISTS
:PREFIX SELECT m1.time FROM metrics_timestamptz m1 WHERE EXISTS(SELECT 1 FROM metrics_timestamptz m2 WHERE m1.time < m2.time) ORDER BY m1.time DESC limit 1000;
--- QUERY PLAN ---
 Limit (actual rows=1000.00 loops=1)
   ->  Nested Loop Semi Join (actual rows=1000.00 loops=1)
         ->  Custom Scan (ChunkAppend) on metrics_timestamptz m1 (actual rows=1003.00 loops=1)
               Order: m1."time" DESC
               ->  Index Scan using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk m1_1 (actual rows=1003.00 loops=1)
               ->  Index Scan using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk m1_2 (never executed)
               ->  Index Scan using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk m1_3 (never executed)
               ->  Index Scan using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk m1_4 (never executed)
               ->  Index Scan using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk m1_5 (never executed)
         ->  Append (actual rows=1.00 loops=1003)
               ->  Index Scan using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk m2_1 (actual rows=0.00 loops=1003)
                     Index Cond: ("time" > m1."time")
               ->  Index Scan using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk m2_2 (actual rows=0.00 loops=1003)
                     Index Cond: ("time" > m1."time")
               ->  Index Scan using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk m2_3 (actual rows=0.00 loops=1003)
                     Index Cond: ("time" > m1."time")
               ->  Index Scan using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk m2_4 (actual rows=0.00 loops=1003)
                     Index Cond: ("time" > m1."time")
               ->  Index Scan using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk m2_5 (actual rows=1.00 loops=1003)
                     Index Cond: ("time" > m1."time")

-- test constraint exclusion for subqueries with append
-- should include 2 chunks
:PREFIX SELECT time FROM (SELECT time FROM metrics_timestamptz WHERE time < '2000-01-10'::text::timestamptz ORDER BY time) m;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=7776.00 loops=1)
   Order: metrics_timestamptz."time"
   Chunks excluded during startup: 3
   ->  Index Scan Backward using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk (actual rows=4032.00 loops=1)
         Index Cond: ("time" < ('2000-01-10'::cstring)::timestamp with time zone)
   ->  Index Scan Backward using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk (actual rows=3744.00 loops=1)
         Index Cond: ("time" < ('2000-01-10'::cstring)::timestamp with time zone)

-- test constraint exclusion for subqueries with mergeappend
-- should include 2 chunks
:PREFIX SELECT device_id, time FROM (SELECT device_id, time FROM metrics_timestamptz WHERE time < '2000-01-10'::text::timestamptz ORDER BY device_id, time) m;
--- QUERY PLAN ---
 Custom Scan (ConstraintAwareAppend) (actual rows=7776.00 loops=1)
   Hypertable: metrics_timestamptz
   Chunks excluded during startup: 3
   ->  Merge Append (actual rows=7776.00 loops=1)
         Sort Key: metrics_timestamptz.device_id, metrics_timestamptz."time"
         ->  Index Scan using _hyper_5_17_chunk_metrics_timestamptz_device_id_time_idx on _hyper_5_17_chunk (actual rows=4032.00 loops=1)
               Index Cond: ("time" < ('2000-01-10'::cstring)::timestamp with time zone)
         ->  Index Scan using _hyper_5_18_chunk_metrics_timestamptz_device_id_time_idx on _hyper_5_18_chunk (actual rows=3744.00 loops=1)
               Index Cond: ("time" < ('2000-01-10'::cstring)::timestamp with time zone)

-- test LIMIT pushdown
-- no aggregates/window functions/SRF should pushdown limit
:PREFIX SELECT FROM metrics_timestamptz ORDER BY time LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Custom Scan (ChunkAppend) on metrics_timestamptz (actual rows=1.00 loops=1)
         Order: metrics_timestamptz."time"
         ->  Index Scan Backward using _hyper_5_17_chunk_metrics_timestamptz_time_idx on _hyper_5_17_chunk (actual rows=1.00 loops=1)
         ->  Index Scan Backward using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk (never executed)
         ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk (never executed)
         ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk (never executed)
         ->  Index Scan Backward using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk (never executed)

-- aggregates should prevent pushdown
:PREFIX SELECT count(*) FROM metrics_timestamptz LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Aggregate (actual rows=1.00 loops=1)
         ->  Append (actual rows=26787.00 loops=1)
               ->  Seq Scan on _hyper_5_17_chunk (actual rows=4032.00 loops=1)
               ->  Seq Scan on _hyper_5_18_chunk (actual rows=6048.00 loops=1)
               ->  Seq Scan on _hyper_5_19_chunk (actual rows=6048.00 loops=1)
               ->  Seq Scan on _hyper_5_20_chunk (actual rows=6048.00 loops=1)
               ->  Seq Scan on _hyper_5_21_chunk (actual rows=4611.00 loops=1)

:PREFIX SELECT count(*) FROM metrics_space LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Aggregate (actual rows=1.00 loops=1)
         ->  Append (actual rows=37450.00 loops=1)
               ->  Seq Scan on _hyper_6_22_chunk (actual rows=5376.00 loops=1)
               ->  Seq Scan on _hyper_6_23_chunk (actual rows=5376.00 loops=1)
               ->  Seq Scan on _hyper_6_24_chunk (actual rows=2688.00 loops=1)
               ->  Seq Scan on _hyper_6_25_chunk (actual rows=8064.00 loops=1)
               ->  Seq Scan on _hyper_6_26_chunk (actual rows=8064.00 loops=1)
               ->  Seq Scan on _hyper_6_27_chunk (actual rows=4032.00 loops=1)
               ->  Seq Scan on _hyper_6_28_chunk (actual rows=1540.00 loops=1)
               ->  Seq Scan on _hyper_6_29_chunk (actual rows=1540.00 loops=1)
               ->  Seq Scan on _hyper_6_30_chunk (actual rows=770.00 loops=1)

-- HAVING should prevent pushdown
:PREFIX SELECT 1 FROM metrics_timestamptz HAVING count(*) > 1 LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Aggregate (actual rows=1.00 loops=1)
         Filter: (count(*) > 1)
         ->  Append (actual rows=26787.00 loops=1)
               ->  Seq Scan on _hyper_5_17_chunk (actual rows=4032.00 loops=1)
               ->  Seq Scan on _hyper_5_18_chunk (actual rows=6048.00 loops=1)
               ->  Seq Scan on _hyper_5_19_chunk (actual rows=6048.00 loops=1)
               ->  Seq Scan on _hyper_5_20_chunk (actual rows=6048.00 loops=1)
               ->  Seq Scan on _hyper_5_21_chunk (actual rows=4611.00 loops=1)

:PREFIX SELECT 1 FROM metrics_space HAVING count(*) > 1 LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Aggregate (actual rows=1.00 loops=1)
         Filter: (count(*) > 1)
         ->  Append (actual rows=37450.00 loops=1)
               ->  Seq Scan on _hyper_6_22_chunk (actual rows=5376.00 loops=1)
               ->  Seq Scan on _hyper_6_23_chunk (actual rows=5376.00 loops=1)
               ->  Seq Scan on _hyper_6_24_chunk (actual rows=2688.00 loops=1)
               ->  Seq Scan on _hyper_6_25_chunk (actual rows=8064.00 loops=1)
               ->  Seq Scan on _hyper_6_26_chunk (actual rows=8064.00 loops=1)
               ->  Seq Scan on _hyper_6_27_chunk (actual rows=4032.00 loops=1)
               ->  Seq Scan on _hyper_6_28_chunk (actual rows=1540.00 loops=1)
               ->  Seq Scan on _hyper_6_29_chunk (actual rows=1540.00 loops=1)
               ->  Seq Scan on _hyper_6_30_chunk (actual rows=770.00 loops=1)

-- DISTINCT should prevent pushdown
SET enable_hashagg TO false;
:PREFIX SELECT DISTINCT device_id FROM metrics_timestamptz ORDER BY device_id LIMIT 3;
--- QUERY PLAN ---
 Limit (actual rows=3.00 loops=1)
   ->  Unique (actual rows=3.00 loops=1)
         ->  Merge Append (actual rows=17859.00 loops=1)
               Sort Key: metrics_timestamptz.device_id
               ->  Index Scan using _hyper_5_17_chunk_metrics_timestamptz_device_id_time_idx on _hyper_5_17_chunk (actual rows=2689.00 loops=1)
               ->  Index Scan using _hyper_5_18_chunk_metrics_timestamptz_device_id_time_idx on _hyper_5_18_chunk (actual rows=4033.00 loops=1)
               ->  Index Scan using _hyper_5_19_chunk_metrics_timestamptz_device_id_time_idx on _hyper_5_19_chunk (actual rows=4033.00 loops=1)
               ->  Index Scan using _hyper_5_20_chunk_metrics_timestamptz_device_id_time_idx on _hyper_5_20_chunk (actual rows=4033.00 loops=1)
               ->  Index Scan using _hyper_5_21_chunk_metrics_timestamptz_device_id_time_idx on _hyper_5_21_chunk (actual rows=3075.00 loops=1)

:PREFIX SELECT DISTINCT device_id FROM metrics_space ORDER BY device_id LIMIT 3;
--- QUERY PLAN ---
 Limit (actual rows=3.00 loops=1)
   ->  Unique (actual rows=3.00 loops=1)
         ->  Merge Append (actual rows=7491.00 loops=1)
               Sort Key: metrics_space.device_id
               ->  Index Scan using _hyper_6_22_chunk_metrics_space_device_id_time_idx on _hyper_6_22_chunk (actual rows=1345.00 loops=1)
               ->  Index Scan using _hyper_6_23_chunk_metrics_space_device_id_time_idx on _hyper_6_23_chunk (actual rows=1345.00 loops=1)
               ->  Index Scan using _hyper_6_24_chunk_metrics_space_device_id_time_idx on _hyper_6_24_chunk (actual rows=1.00 loops=1)
               ->  Index Scan using _hyper_6_25_chunk_metrics_space_device_id_time_idx on _hyper_6_25_chunk (actual rows=2017.00 loops=1)
               ->  Index Scan using _hyper_6_26_chunk_metrics_space_device_id_time_idx on _hyper_6_26_chunk (actual rows=2017.00 loops=1)
               ->  Index Scan using _hyper_6_27_chunk_metrics_space_device_id_time_idx on _hyper_6_27_chunk (actual rows=1.00 loops=1)
               ->  Index Scan using _hyper_6_28_chunk_metrics_space_device_id_time_idx on _hyper_6_28_chunk (actual rows=386.00 loops=1)
               ->  Index Scan using _hyper_6_29_chunk_metrics_space_device_id_time_idx on _hyper_6_29_chunk (actual rows=386.00 loops=1)
               ->  Index Scan using _hyper_6_30_chunk_metrics_space_device_id_time_idx on _hyper_6_30_chunk (actual rows=1.00 loops=1)

RESET enable_hashagg;
-- JOINs should prevent pushdown
-- when LIMIT gets pushed to a Sort node it will switch to top-N heapsort
-- if more tuples then LIMIT are requested this will trigger an error
-- to trigger this we need a Sort node that is below ChunkAppend
CREATE TABLE join_limit (time timestamptz, device_id int);
SELECT table_name FROM create_hypertable('join_limit','time',create_default_indexes:=false);
 table_name 
------------
 join_limit

CREATE INDEX ON join_limit(time,device_id);
INSERT INTO join_limit
SELECT time, device_id
FROM generate_series('2000-01-01'::timestamptz,'2000-01-21','30m') g1(time),
  generate_series(1,10,1) g2(device_id)
ORDER BY time, device_id;
VACUUM (ANALYZE) join_limit;
-- get 2nd chunk oid
SELECT tableoid AS "CHUNK_OID" FROM join_limit WHERE time > '2000-01-07' ORDER BY time LIMIT 1
\gset
--get index name for 2nd chunk
SELECT indexrelid::regclass AS "INDEX_NAME" FROM pg_index WHERE indrelid = :CHUNK_OID
\gset
DROP INDEX :INDEX_NAME;
:PREFIX SELECT * FROM metrics_timestamptz m1 INNER JOIN join_limit m2 ON m1.time = m2.time AND m1.device_id=m2.device_id WHERE m1.time > '2000-01-07' ORDER BY m1.time, m1.device_id LIMIT 3;
--- QUERY PLAN ---
 Limit (actual rows=3.00 loops=1)
   ->  Merge Join (actual rows=3.00 loops=1)
         Merge Cond: (m2."time" = m1."time")
         Join Filter: (m2.device_id = m1.device_id)
         Rows Removed by Join Filter: 4
         ->  Custom Scan (ChunkAppend) on join_limit m2 (actual rows=3.00 loops=1)
               Order: m2."time", m2.device_id
               ->  Sort (actual rows=3.00 loops=1)
                     Sort Key: m2_1."time", m2_1.device_id
                     Sort Method: quicksort 
                     ->  Seq Scan on _hyper_8_35_chunk m2_1 (actual rows=2710.00 loops=1)
                           Filter: ("time" > 'Fri Jan 07 00:00:00 2000 PST'::timestamp with time zone)
                           Rows Removed by Filter: 650
               ->  Index Scan using _hyper_8_36_chunk_join_limit_time_device_id_idx on _hyper_8_36_chunk m2_2 (never executed)
               ->  Index Scan using _hyper_8_37_chunk_join_limit_time_device_id_idx on _hyper_8_37_chunk m2_3 (never executed)
         ->  Materialize (actual rows=22.00 loops=1)
               ->  Custom Scan (ChunkAppend) on metrics_timestamptz m1 (actual rows=19.00 loops=1)
                     Order: m1."time"
                     ->  Index Scan Backward using _hyper_5_18_chunk_metrics_timestamptz_time_idx on _hyper_5_18_chunk m1_1 (actual rows=19.00 loops=1)
                           Index Cond: ("time" > 'Fri Jan 07 00:00:00 2000 PST'::timestamp with time zone)
                     ->  Index Scan Backward using _hyper_5_19_chunk_metrics_timestamptz_time_idx on _hyper_5_19_chunk m1_2 (never executed)
                     ->  Index Scan Backward using _hyper_5_20_chunk_metrics_timestamptz_time_idx on _hyper_5_20_chunk m1_3 (never executed)
                     ->  Index Scan Backward using _hyper_5_21_chunk_metrics_timestamptz_time_idx on _hyper_5_21_chunk m1_4 (never executed)

DROP TABLE join_limit;
-- test ChunkAppend projection #2661
:PREFIX SELECT ts.timestamp, ht.timestamp
FROM (
  SELECT generate_series(
    to_timestamp(FLOOR(EXTRACT (EPOCH FROM '2020-01-01T00:01:00Z'::timestamp) / 300) * 300) AT TIME ZONE 'UTC',
    '2020-01-01T01:00:00Z',
    '5 minutes'::interval
  ) AS timestamp
) ts
LEFT JOIN i2661 ht ON
  (FLOOR(EXTRACT (EPOCH FROM ht."timestamp") / 300) * 300 = EXTRACT (EPOCH FROM ts.timestamp))
  AND ht.timestamp > '2019-12-30T00:00:00Z'::timestamp
ORDER BY ts.timestamp, ht.timestamp;
--- QUERY PLAN ---
 Sort (actual rows=33.00 loops=1)
   Sort Key: ts."timestamp", ht."timestamp"
   Sort Method: quicksort 
   ->  Merge Left Join (actual rows=33.00 loops=1)
         Merge Cond: ((EXTRACT(epoch FROM ts."timestamp")) = ((floor((EXTRACT(epoch FROM ht."timestamp") / '300'::numeric)) * '300'::numeric)))
         ->  Sort (actual rows=13.00 loops=1)
               Sort Key: (EXTRACT(epoch FROM ts."timestamp"))
               Sort Method: quicksort 
               ->  Subquery Scan on ts (actual rows=13.00 loops=1)
                     ->  ProjectSet (actual rows=13.00 loops=1)
                           ->  Result (actual rows=1.00 loops=1)
         ->  Sort (actual rows=514.00 loops=1)
               Sort Key: ((floor((EXTRACT(epoch FROM ht."timestamp") / '300'::numeric)) * '300'::numeric))
               Sort Method: quicksort 
               ->  Result (actual rows=7201.00 loops=1)
                     ->  Custom Scan (ChunkAppend) on i2661 ht (actual rows=7201.00 loops=1)
                           Chunks excluded during startup: 0
                           ->  Seq Scan on _hyper_7_31_chunk ht_1 (actual rows=1200.00 loops=1)
                                 Filter: ("timestamp" > 'Mon Dec 30 00:00:00 2019'::timestamp without time zone)
                           ->  Seq Scan on _hyper_7_32_chunk ht_2 (actual rows=5040.00 loops=1)
                                 Filter: ("timestamp" > 'Mon Dec 30 00:00:00 2019'::timestamp without time zone)
                           ->  Seq Scan on _hyper_7_33_chunk ht_3 (actual rows=961.00 loops=1)
                                 Filter: ("timestamp" > 'Mon Dec 30 00:00:00 2019'::timestamp without time zone)

-- #3030 test chunkappend keeps pathkeys when subpath is append
-- on PG11 this will not use ChunkAppend but MergeAppend
SET enable_seqscan TO FALSE;
CREATE TABLE i3030(time timestamptz NOT NULL, a int, b int);
SELECT table_name FROM create_hypertable('i3030', 'time', create_default_indexes=>false);
 table_name 
------------
 i3030

CREATE INDEX ON i3030(a,time);
INSERT INTO i3030 (time,a) SELECT time, a FROM generate_series('2000-01-01'::timestamptz,'2000-01-01 3:00:00'::timestamptz,'1min'::interval) time, generate_series(1,30) a;
VACUUM (ANALYZE) i3030;
:PREFIX SELECT * FROM i3030 where time BETWEEN '2000-01-01'::text::timestamptz AND '2000-01-03'::text::timestamptz ORDER BY a,time LIMIT 1;
--- QUERY PLAN ---
 Limit (actual rows=1.00 loops=1)
   ->  Custom Scan (ChunkAppend) on i3030 (actual rows=1.00 loops=1)
         Order: i3030.a, i3030."time"
         Chunks excluded during startup: 0
         ->  Index Scan using _hyper_9_38_chunk_i3030_a_time_idx on _hyper_9_38_chunk (actual rows=1.00 loops=1)
               Index Cond: (("time" >= ('2000-01-01'::cstring)::timestamp with time zone) AND ("time" <= ('2000-01-03'::cstring)::timestamp with time zone))

DROP TABLE i3030;
RESET enable_seqscan;
--parent runtime exclusion tests:
--optimization works with ANY (array)
:PREFIX
SELECT *
FROM append_test a
WHERE a.attr @> ANY((SELECT coalesce(array_agg(attr), array[]::jsonb[]) FROM join_test_plain WHERE temp > 100)::jsonb[]);
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on append_test a (actual rows=0.00 loops=1)
   Hypertables excluded during runtime: 1
   InitPlan 1 (returns $0)
     ->  Aggregate (actual rows=1.00 loops=1)
           ->  Seq Scan on join_test_plain (actual rows=0.00 loops=1)
                 Filter: (temp > '100'::double precision)
                 Rows Removed by Filter: 3
   ->  Seq Scan on _hyper_1_1_chunk a_1 (never executed)
         Filter: (attr @> ANY ($0))
   ->  Seq Scan on _hyper_1_2_chunk a_2 (never executed)
         Filter: (attr @> ANY ($0))
   ->  Seq Scan on _hyper_1_3_chunk a_3 (never executed)
         Filter: (attr @> ANY ($0))

--optimization does not work for ANY subquery (does not force an initplan)
:PREFIX
SELECT *
FROM append_test a
WHERE a.attr @> ANY((SELECT attr FROM join_test_plain WHERE temp > 100));
--- QUERY PLAN ---
 Nested Loop Semi Join (actual rows=0.00 loops=1)
   Join Filter: (a.attr @> join_test_plain.attr)
   ->  Append (actual rows=5.00 loops=1)
         ->  Seq Scan on _hyper_1_1_chunk a_1 (actual rows=2.00 loops=1)
         ->  Seq Scan on _hyper_1_2_chunk a_2 (actual rows=2.00 loops=1)
         ->  Seq Scan on _hyper_1_3_chunk a_3 (actual rows=1.00 loops=1)
   ->  Materialize (actual rows=0.00 loops=5)
         ->  Seq Scan on join_test_plain (actual rows=0.00 loops=1)
               Filter: (temp > '100'::double precision)
               Rows Removed by Filter: 3

--works on any strict operator without ANY
:PREFIX
SELECT *
FROM append_test a
WHERE a.attr @> (SELECT attr FROM join_test_plain WHERE temp > 100 limit 1);
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on append_test a (actual rows=0.00 loops=1)
   Hypertables excluded during runtime: 1
   InitPlan 1 (returns $0)
     ->  Limit (actual rows=0.00 loops=1)
           ->  Seq Scan on join_test_plain (actual rows=0.00 loops=1)
                 Filter: (temp > '100'::double precision)
                 Rows Removed by Filter: 3
   ->  Seq Scan on _hyper_1_1_chunk a_1 (never executed)
         Filter: (attr @> $0)
   ->  Seq Scan on _hyper_1_2_chunk a_2 (never executed)
         Filter: (attr @> $0)
   ->  Seq Scan on _hyper_1_3_chunk a_3 (never executed)
         Filter: (attr @> $0)

--optimization works with function calls
CREATE OR REPLACE FUNCTION select_tag(_min_temp int)
 RETURNS jsonb[]
 LANGUAGE sql
 STABLE PARALLEL SAFE
AS $function$
   SELECT coalesce(array_agg(attr), array[]::jsonb[])
  FROM join_test_plain
  WHERE temp > _min_temp
$function$;
:PREFIX
SELECT *
FROM append_test a
WHERE a.attr @> ANY((SELECT select_tag(100))::jsonb[]);
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on append_test a (actual rows=0.00 loops=1)
   Hypertables excluded during runtime: 1
   InitPlan 1 (returns $0)
     ->  Result (actual rows=1.00 loops=1)
   ->  Seq Scan on _hyper_1_1_chunk a_1 (never executed)
         Filter: (attr @> ANY ($0))
   ->  Seq Scan on _hyper_1_2_chunk a_2 (never executed)
         Filter: (attr @> ANY ($0))
   ->  Seq Scan on _hyper_1_3_chunk a_3 (never executed)
         Filter: (attr @> ANY ($0))

--optimization does not work when result is null
:PREFIX
SELECT *
FROM append_test a
WHERE a.attr @> ANY((SELECT array_agg(attr) FROM join_test_plain WHERE temp > 100)::jsonb[]);
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on append_test a (actual rows=0.00 loops=1)
   Hypertables excluded during runtime: 0
   InitPlan 1 (returns $0)
     ->  Aggregate (actual rows=1.00 loops=1)
           ->  Seq Scan on join_test_plain (actual rows=0.00 loops=1)
                 Filter: (temp > '100'::double precision)
                 Rows Removed by Filter: 3
   ->  Seq Scan on _hyper_1_1_chunk a_1 (actual rows=0.00 loops=1)
         Filter: (attr @> ANY ($0))
         Rows Removed by Filter: 2
   ->  Seq Scan on _hyper_1_2_chunk a_2 (actual rows=0.00 loops=1)
         Filter: (attr @> ANY ($0))
         Rows Removed by Filter: 2
   ->  Seq Scan on _hyper_1_3_chunk a_3 (actual rows=0.00 loops=1)
         Filter: (attr @> ANY ($0))
         Rows Removed by Filter: 1

-- Test that ConstraintAwareAppend properly locks relations in
-- parallel query mode
set timescaledb.enable_chunk_append=false;
call force_parallel(true);
:PREFIX
select time, avg(temp), colorid from append_test
where time > now_s() - interval '3 months 20 days'
group by time, colorid;
psql:include/append_query.sql:415: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:415: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:415: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:415: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:415: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:415: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:415: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:415: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:415: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:415: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:415: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:415: NOTICE:  Stable function now_s() called!
psql:include/append_query.sql:415: NOTICE:  Stable function now_s() called!
--- QUERY PLAN ---
 Gather (actual rows=3.00 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Single Copy: true
   ->  HashAggregate (actual rows=3.00 loops=1)
         Group Key: append_test."time", append_test.colorid
         ->  Custom Scan (ConstraintAwareAppend) (actual rows=3.00 loops=1)
               Hypertable: append_test
               Chunks excluded during startup: 1
               ->  Append (actual rows=3.00 loops=1)
                     ->  Seq Scan on _hyper_1_2_chunk (actual rows=2.00 loops=1)
                           Filter: ("time" > (now_s() - '@ 3 mons 20 days'::interval))
                     ->  Seq Scan on _hyper_1_3_chunk (actual rows=1.00 loops=1)
                           Filter: ("time" > (now_s() - '@ 3 mons 20 days'::interval))

reset timescaledb.enable_chunk_append;
reset max_parallel_workers_per_gather;
call force_parallel(false);
--generate the results into two different files
\set ECHO errors
--- Unoptimized results
+++ Optimized results
@@ -1,6 +1,6 @@
              setting              | value 
 ----------------------------------+-------
- timescaledb.enable_optimizations | off
+ timescaledb.enable_optimizations | on
  timescaledb.enable_chunk_append  | on
 
--- Unoptimized results
+++ Optimized results
@@ -1,7 +1,7 @@
              setting              | value 
 ----------------------------------+-------
- timescaledb.enable_optimizations | off
- timescaledb.enable_chunk_append  | on
+ timescaledb.enable_optimizations | on
+ timescaledb.enable_chunk_append  | off
 
  time | temp | colorid | attr