C9ph2Rcj

1create table (
2   start_date_time timestamp,
3   end_date_time timestamp, 
4   id_phi integer, 
5   primary key(start_date_time, end_date_time,id_phi);
6	
7select * from time_limits as t 
8where t.id_phi=0 
9and t.start_date_time <= timestamp'2010-08-08 00:00:00'
10and t.end_date_time   >= timestamp'2010-08-08 00:05:00';
11	
12create index idx_inversed on time_limits(id_phi, start_date_time, end_date_time);
13	
14Bitmap Heap Scan on time_limits  (cost=4730.38..22465.32 rows=62682 width=36) (actual time=44.446..44.446 rows=0 loops=1)
15   Recheck Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
16   ->  Bitmap Index Scan on idx_time_limits_phi_start_end  (cost=0.00..4714.71 rows=62682 width=0) (actual time=44.437..44.437 rows=0 loops=1)
17         Index Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone))
18 Total runtime: 44.507 ms
19	
20CREATE INDEX idx_time_limits_ts_inverse
21ON time_limits (id_phi, start_date_time, end_date_time DESC);
22	
23SELECT *
24FROM   time_limits
25WHERE  id_phi = 0
26AND    start_date_time <= '2010-08-08 00:00'
27AND    end_date_time   >= '2010-08-08 00:05';
28	
29ALTER TABLE time_limits ALTER start_date_time SET STATISTICS 1000;
30ALTER TABLE time_limits ALTER end_date_time   SET STATISTICS 1000;
31	
32ALTER TABLE time_limits CLUSTER ON idx_time_limits_inversed;
33	
34CREATE EXTENSION IF NOT EXISTS btree_gist;  -- only if not installed, yet.
35
36CREATE INDEX idx_time_limits_funky ON time_limits USING gist
37(id_phi, tsrange(start_date_time, end_date_time, '[]'));
38	
39SELECT *
40FROM   time_limits
41WHERE  id_phi = 0
42AND    tsrange(start_date_time, end_date_time, '[]')
43    @> tsrange('2010-08-08 00:00', '2010-08-08 00:05', '[]')
44	
45primary key(id_phi, start_date_time,end_date_time);
46	
47drop index if exists agg_search_a;
48CREATE INDEX agg_search_a
49ON agg (measurement_id, l, "$s");
50
51drop index if exists agg_search_b;
52CREATE INDEX agg_search_b
53ON agg (measurement_id, l, "$e");
54	
55select "$s", "$e", a, t, b, c from agg
56where 
57    measurement_id=0 
58    and l =  '30s'
59    and (
60        (
61            "$s" > '2013-05-01 02:05:05'
62            and "$s" < '2013-05-01 02:18:15'
63        )
64        or 
65        (
66             "$e" > '2013-05-01 02:00:05'
67            and "$e" < '2013-05-01 02:18:05'
68        )
69    )
70
71;
72	
73[
74  {
75    "Execution Time": 0.058,
76    "Planning Time": 0.112,
77    "Plan": {
78      "Startup Cost": 10.18,
79      "Rows Removed by Index Recheck": 0,
80      "Actual Rows": 37,
81      "Plans": [
82    {
83      "Startup Cost": 10.18,
84      "Actual Rows": 0,
85      "Plans": [
86        {
87          "Startup Cost": 0,
88          "Plan Width": 0,
89          "Actual Rows": 26,
90          "Node Type": "Bitmap Index Scan",
91          "Index Cond": "((measurement_id = 0) AND ((l)::text = '30s'::text) AND ("$s" > '2013-05-01 02:05:05'::timestamp without time zone) AND ("$s" < '2013-05-01 02:18:15'::timestamp without time zone))",
92          "Plan Rows": 29,
93          "Parallel Aware": false,
94          "Actual Total Time": 0.016,
95          "Parent Relationship": "Member",
96          "Actual Startup Time": 0.016,
97          "Total Cost": 5,
98          "Actual Loops": 1,
99          "Index Name": "agg_search_a"
100        },
101        {
102          "Startup Cost": 0,
103          "Plan Width": 0,
104          "Actual Rows": 36,
105          "Node Type": "Bitmap Index Scan",
106          "Index Cond": "((measurement_id = 0) AND ((l)::text = '30s'::text) AND ("$e" > '2013-05-01 02:00:05'::timestamp without time zone) AND ("$e" < '2013-05-01 02:18:05'::timestamp without time zone))",
107          "Plan Rows": 39,
108          "Parallel Aware": false,
109          "Actual Total Time": 0.011,
110          "Parent Relationship": "Member",
111          "Actual Startup Time": 0.011,
112          "Total Cost": 5.15,
113          "Actual Loops": 1,
114          "Index Name": "agg_search_b"
115        }
116      ],
117      "Node Type": "BitmapOr",
118      "Plan Rows": 68,
119      "Parallel Aware": false,
120      "Actual Total Time": 0.027,
121      "Parent Relationship": "Outer",
122      "Actual Startup Time": 0.027,
123      "Plan Width": 0,
124      "Actual Loops": 1,
125      "Total Cost": 10.18
126    }
127      ],
128      "Exact Heap Blocks": 1,
129      "Node Type": "Bitmap Heap Scan",
130      "Plan Rows": 68,
131      "Relation Name": "agg",
132      "Alias": "agg",
133      "Parallel Aware": false,
134      "Actual Total Time": 0.037,
135      "Recheck Cond": "(((measurement_id = 0) AND ((l)::text = '30s'::text) AND ("$s" > '2013-05-01 02:05:05'::timestamp without time zone) AND ("$s" < '2013-05-01 02:18:15'::timestamp without time zone)) OR ((measurement_id = 0) AND ((l)::text = '30s'::text) AND ("$e" > '2013-05-01 02:00:05'::timestamp without time zone) AND ("$e" < '2013-05-01 02:18:05'::timestamp without time zone)))",
136      "Lossy Heap Blocks": 0,
137      "Actual Startup Time": 0.033,
138      "Plan Width": 44,
139      "Actual Loops": 1,
140      "Total Cost": 280.95
141    },
142    "Triggers": []
143  }
144]