· 6 years ago · Jun 13, 2019, 11:15 PM
1CREATE TABLE front.probes (
2 bucket_start timestamp NOT NULL DEFAULT now(),
3 -- Extra fields here ...
4) PARTITION BY RANGE (bucket_start);
5
6CREATE TABLE IF NOT EXISTS front.probes_20190611
7PARTITION OF front.probes
8FOR VALUES FROM ('2019-06-11 00:00:00') TO ('2019-06-12 00:00:00');
9
10select min(bucket_start), max(bucket_start)
11from front.probes
12where bucket_start < '2019-06-11 09:06:47'::timestamp - interval '30 minutes'
13and bucket_start >= '2019-06-11 09:06:47'::timestamp - interval '70 minutes';
14
15min |max |
16-------------------|-------------------|
172019-06-11 08:00:00|2019-06-11 08:35:00|
18
19(0.171s)
20
21select min(bucket_start), max(bucket_start)
22from front.probes
23where bucket_start < now() at time zone 'utc' - interval '30 minutes'
24and bucket_start >= now() at time zone 'utc' - interval '70 minutes';
25
26min |max |
27-------------------|-------------------|
282019-06-11 08:00:00|2019-06-11 08:35:00|
29
30(11.140s)
31
32QUERY PLAN |
33--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
34Finalize Aggregate (cost=27177.24..27177.25 rows=1 width=16) |
35 -> Gather (cost=27177.02..27177.23 rows=2 width=16) |
36 Workers Planned: 2 |
37 -> Partial Aggregate (cost=26177.02..26177.03 rows=1 width=16) |
38 -> Append (cost=0.00..24873.83 rows=260639 width=8) |
39 -> Parallel Seq Scan on probes_20190611 (cost=0.00..24873.83 rows=260639 width=8) |
40 Filter: ((bucket_start < '2019-06-11 08:36:47'::timestamp without time zone) AND (bucket_start >= '2019-06-11 07:56:47'::timestamp without time zone))|
41
42QUERY PLAN |
43--------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
44Aggregate (cost=2119587.51..2119587.52 rows=1 width=16) |
45 -> Append (cost=0.00..2116094.94 rows=698514 width=8) |
46 -> Seq Scan on probes_20190605 (cost=0.00..713298.11 rows=1 width=8) |
47 Filter: ((bucket_start < (timezone('utc'::text, now()) - '00:30:00'::interval)) AND (bucket_start >= (timezone('utc'::text, now()) - '01:10:00'::interval)))|
48 (... full partition list, omitted for clarity ...)
49 -> Seq Scan on probes_20190617 (cost=0.00..21.40 rows=2 width=8) |
50 Filter: ((bucket_start < (timezone('utc'::text, now()) - '00:30:00'::interval)) AND (bucket_start >= (timezone('utc'::text, now()) - '01:10:00'::interval)))|