AgfF0qQP

1DROP TABLE IF EXISTS sandbox_nopart;
2CREATE TABLE sandbox_nopart (
3    ts timestamp not null,
4    key int not null,
5    value double precision not null
6);
7
8SELECT create_hypertable(
9    'sandbox_nopart',
10    'ts',
11    chunk_time_interval => interval '7 day',
12    if_not_exists => TRUE
13);
14
15insert into sandbox_nopart (ts, key, value) select ts, key, 1 from (
16    SELECT *
17    FROM generate_series('2019-01-01'::TIMESTAMP, '2020-01-01'::TIMESTAMP, interval '1 minutes') as "ts"
18    CROSS JOIN generate_series(1, 5) AS "key"
19) a;
20
21DROP TABLE IF EXISTS sandbox_withpart;
22CREATE TABLE sandbox_withpart (
23    ts timestamp not null,
24    key int not null,
25    value double precision not null
26);
27
28SELECT create_hypertable(
29    'sandbox_withpart',
30    'ts',
31    partitioning_column => 'key',
32    number_partitions => 5,
33    chunk_time_interval => interval '7 day',
34    if_not_exists => TRUE
35);
36
37insert into sandbox_withpart (ts, key, value) select ts, key, 1 from (
38    SELECT *
39    FROM generate_series('2019-01-01'::TIMESTAMP, '2020-01-01'::TIMESTAMP, interval '1 minutes') as "ts"
40    CROSS JOIN generate_series(1, 5) AS "key"
41) a;
42
43
44explain (analyze, buffers) with machines as (
45    select key, '2019-01-01'::TIMESTAMP + make_interval(days => key) as ts from generate_series(1, 10) as "key"
46), events as (
47    select *
48    from sandbox_nopart
49    inner join machines on sandbox_nopart.key = machines.key and sandbox_nopart.ts >= machines.ts and sandbox_nopart.ts < machines.ts + interval '1 day'
50)
51select count(*) from events;
52
53
54explain (analyze, buffers) with machines as (
55    select key, '2019-01-01'::TIMESTAMP + make_interval(days => key) as ts from generate_series(1, 10) as "key"
56), events as (
57    select *
58    from sandbox_withpart
59    inner join machines on sandbox_withpart.key = machines.key and sandbox_withpart.ts >= machines.ts and sandbox_withpart.ts < machines.ts + interval '1 day'
60)
61select count(*) from events;
62
63explain (analyze, buffers) select count(*) from sandbox_nopart where ts between '2019-06-01' and '2019-06-02';
64explain (analyze, buffers) select count(*) from sandbox_withpart where ts between '2019-06-01' and '2019-06-02';
65
66explain (analyze, buffers) select count(*) from sandbox_nopart where key = 4;
67explain (analyze, buffers) select count(*) from sandbox_withpart where key = 4;
68
69explain (analyze, buffers) select count(*) from sandbox_nopart where key = 4 and ts between '2019-06-01' and '2019-06-15';
70explain (analyze, buffers) select count(*) from sandbox_withpart where key = 4 and ts between '2019-06-01' and '2019-06-15';
71
72explain (analyze, buffers) select count(*) from sandbox_nopart where
73    (key = 1 and ts between '2019-01-01' and '2019-01-02') or
74    -- (key = 2 and ts between '2019-02-01' and '2019-02-02') or 
75    -- (key = 3 and ts between '2019-03-01' and '2019-03-02') or 
76    -- (key = 4 and ts between '2019-04-01' and '2019-04-02') or 
77    (key = 5 and ts between '2019-05-01' and '2019-05-02')
78;
79explain (analyze, buffers) select count(*) from sandbox_withpart where
80    (key = 1 and ts between '2019-01-01' and '2019-01-02') or
81    -- (key = 2 and ts between '2019-02-01' and '2019-02-02') or 
82    -- (key = 3 and ts between '2019-03-01' and '2019-03-02') or 
83    -- (key = 4 and ts between '2019-04-01' and '2019-04-02') or 
84    (key = 5 and ts between '2019-05-01' and '2019-05-02')
85;