[postgis-users] Postgis query not making using of parallel query execution

[Trang Nguyen]

Hi,

I'm running queries against postgres/postgis in an RDS environment. Recently, I've updated postgis to 2.3.0 but am not seeing that my queries are getting executed using multiple workers

Here are my DB configuration settings:

"max_locks_per_transaction";"64"
"max_parallel_workers_per_gather";"4"
"max_pred_locks_per_transaction";"64"
"max_prepared_transactions";"0"
"max_replication_slots";"5"
"max_stack_depth";"6MB"
"max_standby_archive_delay";"30s"
"max_standby_streaming_delay";"30s"
"max_wal_senders";"10"
"max_wal_size";"2GB"

select pkey, trip_id, startts, endts, startloc, endloc, probe_id, provider_id, movement_type, mode, trip_dist_m, trip_mean_speed_metersph, trip_max_speed_metersph, is_start_home, is_end_home, waypoints from od1.trip_v2_y2016m09w1 t, od1.v_zone z where startts>=TIMESTAMP '2016-09-01T23:49:59.997Z' and startts<TIMESTAMP '2016-09-02T23:49:59.997Z' and endts<TIMESTAMP '2016-09-02T23:49:59.997Z' and uuid in ('8c344107-0dd9-49c0-985a-408fe3c583e3','53c75340-faca-4aef-987f-4a30d98078a6','ed2dc985-9035-45ea-a026-14f0f34e2e5c','95222e3a-14dd-4677-969d-86c05bfd2cff','47192cfa-441f-4ef9-b5a4-5dd8608621db','dc94e748-8814-4f46-842b-54597e3c88de','d3bac4cc-ebd7-480b-a530-2e9e3eea2437','b0257117-a66d-4a53-8371-de13cc6b7f3c','cd2dc6c5-94cb-4586-a5a1-1e494da4437a','e7d9aeca-6117-413d-abe5-b90fb52b38f3','c86f12bd-de6a-4e03-b72e-8fb9be30099a','37e8ce9f-fd50-4152-8e2e-10b67ad49142','086480df-81c8-4309-a99f-d4c502fc8463','bf23ac00-eb6a-4a6a-af66-c9e7dfd8da12','3c56bc1a-4abd-4912-8591-76d6ec6c036c','92ee1ea0-9241-4274-8797-53ad95277b0b','6dce995f-4ba9-49d4-838d-6ec0c2ac191f','c4d93769-0008-46e7-88bf-95cbc758ff3c','b976b529-292c-48a2-87b1-e39863579c51','8fc74fd7-1952-46d9-969b-27994a100aab') and mode in (1) and st_intersects(waypoints, geom) order by pkey limit 1000

Explain plan:
"Limit  (cost=100.99..1266217.13 rows=1000 width=1680)"
"  ->  Nested Loop  (cost=100.99..88014164.76 rows=69515 width=1680)"
"        Join Filter: ((t.waypoints && zone.geom) AND _st_intersects(t.waypoints, zone.geom))"
"        ->  Index Scan using trip_v2_y2016m09w1_pkey on trip_v2_y2016m09w1 t  (cost=0.57..32749625.17 rows=10427221 width=1680)"
"              Filter: ((startts >= '2016-09-01 23:49:59.997'::timestamp without time zone) AND (startts < '2016-09-02 23:49:59.997'::timestamp without time zone) AND (endts < '2016-09-02 23:49:59.997'::timestamp without time zone) AND (mode = 1))"
"        ->  Materialize  (cost=100.42..268.34 rows=20 width=4380)"
"              ->  Foreign Scan on remote_zone zone  (cost=100.42..268.24 rows=20 width=4380)"

Also, this is a scaled-down version of the query, for testing purposed. The real query uses a partitioned table. Does parallel query also work on inherited tables?

Any help would be appreciated.

Thanks,
Trang

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161229/5536400f/attachment.html>