Help, please can anyone offer suggestions on how to speed this query up.
thanks
dotidb=# select count(*) from doti_sensor_report_y2014m09;
count
----------
184,888,345
(1 row)
dotidb=# \d+ doti_sensor_report_y2014m09 <-- Partition table of parent table
public.doti_sensor_report
Table "public.doti_sensor_report_y2014m09"
Column | Type | Modifiers
-----------------------+-----------------------------+-----------
sensor_report_uid | bigint | not null
report_type | character varying(255) | not null
sensor_report_uuid | uuid |
model_uid | bigint | not null
sensor_location | geometry | not null
node_date_time | timestamp without time zone |
Indexes:
"doti_sensor_report_y2014m09_pkey" PRIMARY KEY, btree (sensor_report_uid),
tablespace "doti_data_y2014"
"idx_sensor_report_query_y2014m09" btree (model_uid, node_date_time),
tablespace "doti_data_y2014"
"sidx_sensor_report_y2014m09" gist (sensor_location) INVALID, tablespace
"doti_data_y2014" <-- set invalid to force planner to use compound index,
when toggled spatial index runs in eccess of 8 minutes
Check constraints:
"doti_sensor_report_y2014m09_node_date_time_check" CHECK (node_date_time >=
'2014-09-01'::date AND node_date_time < '2014-10-01'::date)
Inherits: doti_sensor_report
Tablespace: "doti_data_y2014"
explain (analyze,buffers)
select count(*) as y0_ from DOTI_SENSOR_REPORT this_
where this_.node_date_time between '2014-09-20 23:40:56.245'::timestamp
without time zone and '2014-09-21 07:36:47.388'::timestamp without time zone
and this_.model_uid=20164
and (ST_within
(this_.sensor_location,'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F')
or ST_touches
(this_.sensor_location,'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'))
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=137582.68..137582.71 rows=1 width=0) (actual time=349105.178
rows=1 loops=1)
Buffers: shared hit=157038
-> Append (cost=0.00..137572.97 rows=3885 width=0) (actual
time=168.419..348986.845 rows=443542 loops=1)
Buffers: shared hit=157038
-> Seq Scan on doti_sensor_report this_ (cost=0.00..0.00 rows=1
width=0) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((node_date_time >= '2014-09-20 23:40:56.245'::timestamp
without time zone) AND (node_date_time <= '2014-09-21 07:36:47.388'::timestamp
without time zone)
AND (sensor_location &&
'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)
AND (model_uid = 20164)
AND
(_st_contains('010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry,
sensor_location)
OR _st_touches(sensor_location,
'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)))
-> Index Scan using idx_sensor_report_query_y2014m09 on
doti_sensor_report_y2014m09 this__1 (cost=0.57..137498.17 rows=3883 width=0)
(actual time=168.416..348873.308 rows=443542 loops=1)
Index Cond: ((model_uid = 20164) AND (node_date_time >=
'2014-09-20 23:40:56.245'::timestamp without time zone) AND (node_date_time <=
'2014-09-21 07:36:47.388'::timestamp without time zone))
Filter: ((sensor_location &&
'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)
AND
(_st_contains('010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry,
sensor_location)
OR _st_touches(sensor_location,
'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)))
Rows Removed by Filter: 3310409
Buffers: shared hit=157038
-> Seq Scan on doti_sensor_report_overflow this__2 (cost=0.00..7480
rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((node_date_time >= '2014-09-20 23:40:56.245'::timestamp
without time zone) AND (node_date_time <= '2014-09-21 07:36:47.388'::timestamp
without time zone)
AND (sensor_location &&
'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)
AND (model_uid = 20164) AND
(_st_contains('010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry,
sensor_location)
OR _st_touches(sensor_location,
'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)))
Total runtime: 349105.317 ms <-- 5.81842 minutes unacceptable
(14 rows)
dotidb=#
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
