I changed the query from (st_within or st_touches) to ST_intersects, that sped up the execution. Reference progress in Attachment please.
Thanks
________________________________________
From: Graeme B. Bell [grb@xxxxxxxxxxxxxxxxx]
Sent: Monday, September 29, 2014 7:08 AM
To: Burgess, Freddie
Cc: pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: Very slow postgreSQL 9.3.4 query
Hi,
Two things:
- Make sure you are creating a GIST index on your geometry column in postgis.
- Try using st_intersects rather than &&. I've noticed that && isn't using indices correctly in some situations e.g. function indices for st_transform'd geo columns.
Graeme
On 26 Sep 2014, at 18:17, Burgess, Freddie <FBurgess@xxxxxxxxxxxxxxx> wrote:
> Workflow description:
>
> 1.) User draws a polygon around an area of interest, via UI.
> 2.) UI responses with how many sensors reside within the area of the polygon.
> 3.) Hibernate generates the count query detailed in the attachment.
>
> Performance data is included in the attachment, via EXPLAIN PLAN, query takes approx 6 minutes to return count to UI.
> Amount of data processed is also included in the attachment, 185 million row partition.
>
> Hardware
>
> VM
> 80GB memory
> 8 CPU Xeon
> Linux 2.6.32-431.3.1.el6.x86-64
> 40TB disk, Database size: 8TB
> PostgreSQL 9.3.4 with POSTGIS 2.1.1, Red Hat 4.4.7-4, 64 bit
> streaming replication
>
> Postgresql.conf
>
> max_connection = 100
> shared_buffers = 32GB
> work_mem = 16MB
> maintenance_work_mem = 1GB
> seq_page_cost = 1.0
> random_page_cost = 2.0
> cpu_tuple_cost = 0.03
> effective_cache_size = 48GB
>
> ________________________________________
> From: Graeme B. Bell [grb@xxxxxxxxxxxxxxxxx]
> Sent: Friday, September 26, 2014 9:55 AM
> To: Burgess, Freddie
> Cc: pgsql-performance@xxxxxxxxxxxxxx
> Subject: Re: Very slow postgreSQL 9.3.4 query
>
> A good way to start would be to introduce the query - describe what it is meant to do, give some performance data (your measurements of time taken, amount of data being processed, hardware used etc).
>
> Graeme.
>
>
> On 26 Sep 2014, at 15:04, Burgess, Freddie <FBurgess@xxxxxxxxxxxxxxx> wrote:
>
>> Help, please can anyone offer suggestions on how to speed this query up.
>>
>> thanks
>>
>>
>> <Poor Pref query.txt>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
-- Graeme, the create Index statement was executed using the following syntax.
CREATE INDEX sidx_sensor_report_y2014m09 ON doti_sensor_report_y2014m09 USING GIST (sensor_location) TABLESPACE doti_data_y2014;
*** Leveraging the spatial index ***
-- 1.) Dropped index "idx_sensor_report_query_y2014m09" on the doti_sensor_report_y2014m09 and toggled the spatial index to a valid state to force planner to use it to execute query.
-- i.e. update pg_index set indisvalid = true where indexrelid = 'sidx_sensor_report_y2014m09'::regclass;
-- 2.) Executed query plan. This was our original configuration that was taking over 10 minutes to return results to the UI. Today the runtimes are slightly better than the compound index "idx_sensor_report_query_y2014m09"
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=12848201.03..12848201.06 rows=1 width=0) (actual time=282105.704..282105.705 rows=1 loops=1)
Buffers: shared hit=284476 read=204819
-> Append (cost=0.00..12848200.47 rows=225 width=0) (actual time=282105.700..282105.700 rows=0 loops=1)
Buffers: shared hit=284476 read=204819
-> Seq Scan on doti_sensor_report this_ (cost=0.00..0.00 rows=1 width=0) (actual time=0.001..0.001 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)))
-> Bitmap Heap Scan on doti_sensor_report_y2014m09 this__1 (cost=300474.83..12848125.67 rows=223 width=0) (actual time=282105.694..282105.694 rows=0 loops=1)
Recheck Cond: (sensor_location && '010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)
Rows Removed by Index Recheck: 2521326
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 (model_uid = 20164)
AND (_st_contains('010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry, sensor_location)
OR _st_touches(sensor_location, '010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)))
Rows Removed by Filter: 14867114
Buffers: shared hit=284476 read=204819
-> Bitmap Index Scan on sidx_sensor_report_y2014m09 on doti_sensor_report_y2014m09 (cost=0.00..300474.77 rows=14742696 width=0) (actual time=196575.084..196575.084 rows=14867114 loops=1)
Index Cond: (sensor_location && '010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)
Buffers: shared hit=16887 read=82607
-> Seq Scan on doti_sensor_report_overflow this__2 (cost=0.00..74.80 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: 282108.715 ms <- 4.7018 minutes - unacceptable to User community
(18 rows)
*** Updated table Statistics ***
marsdb=# select * from pg_stat_user_tables where relname = 'doti_sensor_report_y2014m09';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
---------+------------+-----------------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+-------------+---------------------------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
1820279 | public | doti_sensor_report_y2014m09 | 20 | 3226221906 | 22770 | 38741010306 | 210470837 | 0 | 1676570 | 0 | 230047224 | 1602995 | | 2014-09-22 10:25:17.095126+00 | 2014-09-26 21:06:17.817324+00 | 2014-09-26 04:37:52.687816+00 | 0 | 2 | 2 | 5
(1 row)
-- re-ran explain plan leveraging updated statistics
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=112542.35..112542.38 rows=1 width=0) (actual time=347664.232..347664.232 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=157035
-> Seq Scan on doti_sensor_report this_ (cost=0.00..0.00 rows=1 width=0) (actual time=0.001..0.001 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..112460.53 rows=2806 width=0) (actual time=150.115..347438.135 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=157035
-> Seq Scan on doti_sensor_report_overflow this__2 (cost=0.00..74.80 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 <-- last run: 5.81842 minutes - unacceptable to User community
Current runtime: 347644.312 ms <-- 5.79407 minutes - still slow - unacceptable to User community
(14 rows)
*** One very promising development ***
Graeme, mentioned using the St_Intersets clause. So I ran some tests using this re-factored query. Counts are returning in milliseconds using this method. Can there be descrepancies using this method instead.
Results are coming back with the identical row counts thus far in my testing?
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_Intersets(this_.sensor_location,'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F');
thanks
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
