1-question.md

create table keys (
  id       integer      primary key,
  name     varchar(255) not null
);

create table data (
  point     timestamptz not null,
  key_id    integer not null,
  value     real not null
) partition by hash(key_id);

create table data_0 partition of data for values with (modulus 3, remainder 0);
create table data_1 partition of data for values with (modulus 3, remainder 1);
create table data_2 partition of data for values with (modulus 3, remainder 2);

INSERT INTO keys (id, name)
SELECT i, 'metric-' || i
FROM generate_series(0, 100) AS g (i);

INSERT INTO data (point, key_id, value)
SELECT now(), floor(random() * 100), random()
FROM generate_series(0, 1000000) AS g (i);

explain analyze
select * from data
join keys on keys.id = data.key_id
where keys.name = 'metric-1';

explain analyze
select * from data
where key_id = 1;

                                                               QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1002.27..15991.89 rows=5000 width=29) (actual time=61.521..86.004 rows=9941 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Hash Join  (cost=2.28..14491.89 rows=2083 width=29) (actual time=39.370..63.086 rows=3314 loops=3)
         Hash Cond: (data_2.key_id = keys.id)
         ->  Parallel Append  (cost=0.00..13372.69 rows=416667 width=16) (actual time=0.019..40.732 rows=333334 loops=3)
               ->  Parallel Seq Scan on data_2  (cost=0.00..4284.18 rows=223218 width=16) (actual time=0.009..8.850 rows=126490 loops=3)
               ->  Parallel Seq Scan on data_1  (cost=0.00..3618.64 rows=188564 width=16) (actual time=0.010..12.831 rows=160279 loops=2)
               ->  Parallel Seq Scan on data_0  (cost=0.00..3386.54 rows=176454 width=16) (actual time=0.028..21.730 rows=299972 loops=1)
         ->  Hash  (cost=2.26..2.26 rows=1 width=13) (actual time=0.027..0.027 rows=1 loops=3)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Seq Scan on keys  (cost=0.00..2.26 rows=1 width=13) (actual time=0.014..0.019 rows=1 loops=3)
                     Filter: ((name)::text = 'metric-1'::text)
                     Rows Removed by Filter: 100
 Planning Time: 0.220 ms
 Execution Time: 86.373 ms
(16 rows)

                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Seq Scan on data_2  (cost=0.00..6795.39 rows=10385 width=16) (actual time=0.013..26.420 rows=9941 loops=1)
   Filter: (key_id = 1)
   Rows Removed by Filter: 369530
 Planning Time: 0.079 ms
 Execution Time: 26.701 ms
(5 rows)

2-with-index-on-data-key_id

postgres=# create table keys (
postgres(#   id       integer      primary key,
postgres(#   name     varchar(255) not null
postgres(# );
CREATE TABLE
postgres=# 
postgres=# create table data (
postgres(#   point     timestamptz not null,
postgres(#   key_id    integer not null,
postgres(#   value     real not null
postgres(# ) partition by hash(key_id);
CREATE TABLE
postgres=# 
postgres=# create index on data (key_id);
CREATE INDEX
postgres=# 
postgres=# create table data_0 partition of data for values with (modulus 3, remainder 0);
CREATE TABLE
postgres=# create table data_1 partition of data for values with (modulus 3, remainder 1);
CREATE TABLE
postgres=# create table data_2 partition of data for values with (modulus 3, remainder 2);
CREATE TABLE
postgres=# 
postgres=# INSERT INTO keys (id, name)
postgres-# SELECT i, 'metric-' || i
postgres-# FROM generate_series(0, 100) AS g (i);
INSERT 0 101
postgres=# 
postgres=# INSERT INTO data (point, key_id, value)
postgres-# SELECT now(), floor(random() * 100), random()
postgres-# FROM generate_series(0, 1000000) AS g (i);
INSERT 0 1000001
postgres=# 
postgres=# explain analyze
postgres-# select * from data
postgres-# join keys on keys.id = data.key_id
postgres-# where keys.name = 'metric-1';
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=28.06..5766.29 rows=5001 width=536) (actual time=1.298..6.636 rows=9851 loops=1)
   ->  Seq Scan on keys  (cost=0.00..11.75 rows=1 width=520) (actual time=0.010..0.018 rows=1 loops=1)
         Filter: ((name)::text = 'metric-1'::text)
         Rows Removed by Filter: 100
   ->  Append  (cost=28.06..5704.53 rows=5001 width=16) (actual time=1.286..5.712 rows=9851 loops=1)
         ->  Bitmap Heap Scan on data_0  (cost=28.06..1705.16 rows=1502 width=16) (never executed)
               Recheck Cond: (key_id = keys.id)
               ->  Bitmap Index Scan on data_0_key_id_idx  (cost=0.00..27.69 rows=1502 width=0) (never executed)
                     Index Cond: (key_id = keys.id)
         ->  Bitmap Heap Scan on data_1  (cost=32.80..1816.17 rows=1597 width=16) (never executed)
               Recheck Cond: (key_id = keys.id)
               ->  Bitmap Index Scan on data_1_key_id_idx  (cost=0.00..32.40 rows=1597 width=0) (never executed)
                     Index Cond: (key_id = keys.id)
         ->  Bitmap Heap Scan on data_2  (cost=35.16..2158.20 rows=1902 width=16) (actual time=1.283..5.148 rows=9851 loops=1)
               Recheck Cond: (key_id = keys.id)
               Heap Blocks: exact=2042
               ->  Bitmap Index Scan on data_2_key_id_idx  (cost=0.00..34.69 rows=1902 width=0) (actual time=0.917..0.917 rows=9851 loops=1)
                     Index Cond: (key_id = keys.id)
 Planning Time: 0.494 ms
 Execution Time: 6.923 ms
(20 rows)

postgres=# 
postgres=# explain analyze
postgres-# select * from data
postgres-# where key_id = 1;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on data_2  (cost=35.16..2158.20 rows=1902 width=16) (actual time=0.732..3.825 rows=9851 loops=1)
   Recheck Cond: (key_id = 1)
   Heap Blocks: exact=2042
   ->  Bitmap Index Scan on data_2_key_id_idx  (cost=0.00..34.69 rows=1902 width=0) (actual time=0.528..0.528 rows=9851 loops=1)
         Index Cond: (key_id = 1)
 Planning Time: 0.074 ms
 Execution Time: 4.088 ms
(7 rows)

3-timescale

postgres=# create table keys (
postgres(#   id       integer      primary key,
postgres(#   name     varchar(255) not null
postgres(# );
CREATE TABLE
postgres=# 
postgres=# create table data (
postgres(#   point     timestamptz not null,
postgres(#   key_id    integer not null,
postgres(#   value     real not null
postgres(# );
CREATE TABLE
postgres=# 
postgres=# create index on data (key_id);
CREATE INDEX
postgres=# 
postgres=# select create_hypertable(
postgres(#   'data', 'point',
postgres(#   partitioning_column => 'key_id',
postgres(#   number_partitions => 3
postgres(# );
 create_hypertable 
-------------------
 (1,public,data,t)
(1 row)

postgres=# 
postgres=# INSERT INTO keys (id, name)
postgres-# SELECT i, 'metric-' || i
postgres-# FROM generate_series(0, 100) AS g (i);
INSERT 0 101
postgres=# 
postgres=# INSERT INTO data (point, key_id, value)
postgres-# SELECT now(), floor(random() * 100), random()
postgres-# FROM generate_series(0, 1000000) AS g (i);
INSERT 0 1000001
postgres=# 
postgres=# explain analyze
postgres-# select * from data
postgres-# join keys on keys.id = data.key_id
postgres-# where keys.name = 'metric-1';
                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=16.78..3701.07 rows=5001 width=536) (actual time=0.622..7.077 rows=9865 loops=1)
   ->  Seq Scan on keys  (cost=0.00..11.75 rows=1 width=520) (actual time=0.009..0.031 rows=1 loops=1)
         Filter: ((name)::text = 'metric-1'::text)
         Rows Removed by Filter: 100
   ->  Append  (cost=16.78..3639.31 rows=5001 width=16) (actual time=0.610..5.961 rows=9865 loops=1)
         ->  Bitmap Heap Scan on _hyper_1_1_chunk  (cost=16.78..1012.89 rows=1401 width=16) (actual time=0.007..0.007 rows=0 loops=1)
               Recheck Cond: (key_id = keys.id)
               ->  Bitmap Index Scan on _hyper_1_1_chunk_data_key_id_idx  (cost=0.00..16.43 rows=1401 width=0) (actual time=0.005..0.005 rows=0 loops=1)
                     Index Cond: (key_id = keys.id)
         ->  Bitmap Heap Scan on _hyper_1_2_chunk  (cost=19.81..1192.52 rows=1650 width=16) (actual time=0.601..5.337 rows=9865 loops=1)
               Recheck Cond: (key_id = keys.id)
               Heap Blocks: exact=1781
               ->  Bitmap Index Scan on _hyper_1_2_chunk_data_key_id_idx  (cost=0.00..19.40 rows=1650 width=0) (actual time=0.430..0.430 rows=9865 loops=1)
                     Index Cond: (key_id = keys.id)
         ->  Bitmap Heap Scan on _hyper_1_3_chunk  (cost=23.24..1408.89 rows=1950 width=16) (actual time=0.013..0.013 rows=0 loops=1)
               Recheck Cond: (key_id = keys.id)
               ->  Bitmap Index Scan on _hyper_1_3_chunk_data_key_id_idx  (cost=0.00..22.75 rows=1950 width=0) (actual time=0.010..0.010 rows=0 loops=1)
                     Index Cond: (key_id = keys.id)
 Planning Time: 0.532 ms
 Execution Time: 7.482 ms
(20 rows)

postgres=# 
postgres=# explain analyze
postgres-# select * from data
postgres-# where key_id = 1;
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=19.81..1200.77 rows=1650 width=16) (actual time=0.776..4.952 rows=9865 loops=1)
   ->  Bitmap Heap Scan on _hyper_1_2_chunk  (cost=19.81..1192.52 rows=1650 width=16) (actual time=0.775..4.411 rows=9865 loops=1)
         Recheck Cond: (key_id = 1)
         Heap Blocks: exact=1781
         ->  Bitmap Index Scan on _hyper_1_2_chunk_data_key_id_idx  (cost=0.00..19.40 rows=1650 width=0) (actual time=0.476..0.476 rows=9865 loops=1)
               Index Cond: (key_id = 1)
 Planning Time: 0.212 ms
 Execution Time: 5.253 ms
(8 rows)

postgres=# \d data
                        Table "public.data"
 Column |           Type           | Collation | Nullable | Default 
--------+--------------------------+-----------+----------+---------
 point  | timestamp with time zone |           | not null | 
 key_id | integer                  |           | not null | 
 value  | real                     |           | not null | 
Indexes:
    "data_key_id_idx" btree (key_id)
    "data_key_id_point_idx" btree (key_id, point DESC)
    "data_point_idx" btree (point DESC)
Triggers:
    ts_insert_blocker BEFORE INSERT ON data FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()
Number of child tables: 3 (Use \d+ to list them.)

postgres=# \d+ data
                                            Table "public.data"
 Column |           Type           | Collation | Nullable | Default | Storage | Stats target | Description 
--------+--------------------------+-----------+----------+---------+---------+--------------+-------------
 point  | timestamp with time zone |           | not null |         | plain   |              | 
 key_id | integer                  |           | not null |         | plain   |              | 
 value  | real                     |           | not null |         | plain   |              | 
Indexes:
    "data_key_id_idx" btree (key_id)
    "data_key_id_point_idx" btree (key_id, point DESC)
    "data_point_idx" btree (point DESC)
Triggers:
    ts_insert_blocker BEFORE INSERT ON data FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()
Child tables: _timescaledb_internal._hyper_1_1_chunk,
              _timescaledb_internal._hyper_1_2_chunk,
              _timescaledb_internal._hyper_1_3_chunk

postgres=# \d+ _timescaledb_internal._hyper_1_1_chunk
                              Table "_timescaledb_internal._hyper_1_1_chunk"
 Column |           Type           | Collation | Nullable | Default | Storage | Stats target | Description 
--------+--------------------------+-----------+----------+---------+---------+--------------+-------------
 point  | timestamp with time zone |           | not null |         | plain   |              | 
 key_id | integer                  |           | not null |         | plain   |              | 
 value  | real                     |           | not null |         | plain   |              | 
Indexes:
    "_hyper_1_1_chunk_data_key_id_idx" btree (key_id)
    "_hyper_1_1_chunk_data_key_id_point_idx" btree (key_id, point DESC)
    "_hyper_1_1_chunk_data_point_idx" btree (point DESC)
Check constraints:
    "constraint_1" CHECK (point >= '2020-02-13 00:00:00+00'::timestamp with time zone AND point < '2020-02-20 00:00:00+00'::timestamp with time zone)
    "constraint_2" CHECK (_timescaledb_internal.get_partition_hash(key_id) >= 715827882 AND _timescaledb_internal.get_partition_hash(key_id) < 1431655764)
Inherits: data