Postgresql Queries
The following doesn't take into account the current file based cache and focuses on current Postgresql queries using EXPLAIN command.
List sensors
BEGIN; EXPLAIN ANALYZE WITH stream_info AS (SELECT sensor_id, start_time, end_time, unnest(params) AS param FROM streams) SELECT row_to_json(t, true) FROM (SELECT gid As id, name, to_char(created AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSZ') AS created, 'Feature' As type, metadata As properties, ST_AsGeoJson(1, geog, 15, 0)::json As geometry, to_char(min(stream_info.start_time) AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSZ') AS min_start_time, to_char(max(stream_info.end_time) AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSZ') AS max_end_time, array_agg(distinct stream_info.param) as parameters FROM sensors LEFT OUTER JOIN stream_info ON stream_info.sensor_id = sensors.gid GROUP BY id ORDER BY name) As t; ROLLBACK;
"QUERY PLAN" "Subquery Scan on t (cost=709.31..710.74 rows=95 width=32) (actual time=4.224..5.173 rows=95 loops=1)" " CTE stream_info" " -> Seq Scan on streams (cost=0.00..57.26 rows=10100 width=52) (actual time=0.200..0.554 rows=159 loops=1)" " -> Sort (cost=652.06..652.29 rows=95 width=337) (actual time=4.187..4.195 rows=95 loops=1)" " Sort Key: sensors.name" " Sort Method: quicksort Memory: 76kB" " -> GroupAggregate (cost=586.35..648.93 rows=95 width=337) (actual time=1.620..3.544 rows=95 loops=1)" " Group Key: sensors.gid" " -> Sort (cost=586.35..598.34 rows=4798 width=241) (actual time=1.534..1.575 rows=218 loops=1)" " Sort Key: sensors.gid" " Sort Method: quicksort Memory: 94kB" " -> Hash Right Join (cost=5.14..292.99 rows=4798 width=241) (actual time=0.719..1.321 rows=218 loops=1)" " Hash Cond: (stream_info.sensor_id = sensors.gid)" " -> CTE Scan on stream_info (cost=0.00..202.00 rows=10100 width=52) (actual time=0.203..0.670 rows=159 loops=1)" " -> Hash (cost=3.95..3.95 rows=95 width=193) (actual time=0.500..0.500 rows=95 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 30kB" " -> Seq Scan on sensors (cost=0.00..3.95 rows=95 width=193) (actual time=0.195..0.426 rows=95 loops=1)" "Planning time: 0.414 ms" "Execution time: 5.333 ms"
Explore popup prefetch
BEGIN; EXPLAIN ANALYZE SELECT to_json(t) As datapoint FROM (SELECT datapoints.gid As id, to_char(datapoints.created AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSZ') AS created, to_char(datapoints.start_time AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSZ') AS start_time, to_char(datapoints.end_time AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSZ') AS end_time, data As properties, 'Feature' As type, ST_AsGeoJson(1, datapoints.geog, 15, 0)::json As geometry, stream_id::text, sensor_id::text, sensors.name as sensor_name FROM sensors, streams, datapoints WHERE sensors.gid = streams.sensor_id AND datapoints.stream_id = streams.gid AND sensor_id = 156 order by sensor_name, start_time asc) As t; ROLLBACK;
"QUERY PLAN" "Subquery Scan on t (cost=72192.83..72709.08 rows=34417 width=32) (actual time=1300.535..2110.546 rows=45919 loops=1)" " -> Sort (cost=72192.83..72278.87 rows=34417 width=270) (actual time=1300.476..1492.102 rows=45919 loops=1)" " Sort Key: sensors.name, (to_char((datapoints.start_time)::timestamp without time zone, 'YYYY-MM-DD""T""HH24:MI:SSZ'::text))" " Sort Method: external merge Disk: 16136kB" " -> Nested Loop (cost=0.43..65245.36 rows=34417 width=270) (actual time=0.103..408.056 rows=45919 loops=1)" " -> Nested Loop (cost=0.00..11.48 rows=3 width=18) (actual time=0.041..0.110 rows=3 loops=1)" " -> Seq Scan on sensors (cost=0.00..4.19 rows=1 width=14) (actual time=0.034..0.038 rows=1 loops=1)" " Filter: (gid = 156)" " Rows Removed by Filter: 94" " -> Seq Scan on streams (cost=0.00..7.26 rows=3 width=8) (actual time=0.005..0.067 rows=3 loops=1)" " Filter: (sensor_id = 156)" " Rows Removed by Filter: 98" " -> Index Scan using geoindex_stream_id on datapoints (cost=0.43..21181.93 rows=27589 width=104) (actual time=0.028..10.120 rows=15306 loops=3)" " Index Cond: (stream_id = streams.gid)" "Planning time: 1.166 ms" "Execution time: 2121.168 ms"
Detail switch binning resolution
BEGIN; EXPLAIN ANALYZE SELECT to_json(t) As datapoint FROM (SELECT datapoints.gid As id, to_char(datapoints.created AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSZ') AS created, to_char(datapoints.start_time AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSZ') AS start_time, to_char(datapoints.end_time AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSZ') AS end_time, data As properties, 'Feature' As type, ST_AsGeoJson(1, datapoints.geog, 15, 0)::json As geometry, stream_id::text, sensor_id::text, sensors.name as sensor_name FROM sensors, streams, datapoints WHERE sensors.gid = streams.sensor_id AND datapoints.stream_id = streams.gid AND sensor_id = 55 order by sensor_name, start_time asc) As t; ROLLBACK;
"QUERY PLAN" "Subquery Scan on t (cost=24006.51..24178.59 rows=11472 width=32) (actual time=732.882..951.117 rows=19504 loops=1)" " -> Sort (cost=24006.51..24035.19 rows=11472 width=270) (actual time=732.842..742.419 rows=19504 loops=1)" " Sort Key: sensors.name, (to_char((datapoints.start_time)::timestamp without time zone, 'YYYY-MM-DD""T""HH24:MI:SSZ'::text))" " Sort Method: external sort Disk: 3952kB" " -> Nested Loop (cost=0.43..23232.96 rows=11472 width=270) (actual time=0.096..150.605 rows=19504 loops=1)" " -> Nested Loop (cost=0.00..11.46 rows=1 width=18) (actual time=0.020..0.086 rows=1 loops=1)" " -> Seq Scan on sensors (cost=0.00..4.19 rows=1 width=14) (actual time=0.012..0.034 rows=1 loops=1)" " Filter: (gid = 55)" " Rows Removed by Filter: 94" " -> Seq Scan on streams (cost=0.00..7.26 rows=1 width=8) (actual time=0.007..0.049 rows=1 loops=1)" " Filter: (sensor_id = 55)" " Rows Removed by Filter: 100" " -> Index Scan using geoindex_stream_id on datapoints (cost=0.43..22658.81 rows=27589 width=104) (actual time=0.038..11.099 rows=19504 loops=1)" " Index Cond: (stream_id = streams.gid)" "Planning time: 0.821 ms" "Execution time: 958.907 ms"
Compare add sensor
BEGIN; EXPLAIN ANALYZE SELECT to_json(t) As datapoint FROM (SELECT datapoints.gid As id, to_char(datapoints.created AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSZ') AS created, to_char(datapoints.start_time AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSZ') AS start_time, to_char(datapoints.end_time AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SSZ') AS end_time, data As properties, 'Feature' As type, ST_AsGeoJson(1, datapoints.geog, 15, 0)::json As geometry, stream_id::text, sensor_id::text, sensors.name as sensor_name FROM sensors, streams, datapoints WHERE sensors.gid = streams.sensor_id AND datapoints.stream_id = streams.gid AND sensor_id = 244 order by sensor_name, start_time asc) As t; ROLLBACK;
"QUERY PLAN" "Subquery Scan on t (cost=230898.95..231729.37 rows=55361 width=32) (actual time=7.512..7.612 rows=9 loops=1)" " -> Sort (cost=230898.95..231037.36 rows=55361 width=270) (actual time=7.438..7.439 rows=9 loops=1)" " Sort Key: sensors.name, (to_char((datapoints.start_time)::timestamp without time zone, 'YYYY-MM-DD""T""HH24:MI:SSZ'::text))" " Sort Method: quicksort Memory: 29kB" " -> Nested Loop (cost=0.73..219533.95 rows=55361 width=270) (actual time=7.265..7.360 rows=9 loops=1)" " -> Nested Loop (cost=0.29..16.34 rows=1 width=18) (actual time=0.029..0.031 rows=1 loops=1)" " -> Index Scan using sensors_pkey on sensors (cost=0.14..8.16 rows=1 width=14) (actual time=0.014..0.015 rows=1 loops=1)" " Index Cond: (gid = 244)" " -> Index Scan using streams_sensor_id_idx on streams (cost=0.15..8.17 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)" " Index Cond: (sensor_id = 244)" " -> Index Scan using datapoints_stream_id_idx on datapoints (cost=0.43..217288.61 rows=84498 width=104) (actual time=0.017..0.035 rows=9 loops=1)" " Index Cond: (stream_id = streams.gid)" "Planning time: 3.237 ms" "Execution time: 7.706 ms"