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

Darafei "Komяpa" Praliaskouski me at komzpa.net
Thu Dec 29 04:24:51 PST 2016


Hello, can you please do

 \sf st_intersects (geometry, geometry)

and similar queries drilling down to each function, and confirm that they
are all installed as PARALLEL SAFE?


чт, 29 дек. 2016 г. в 4:39, Trang Nguyen <Trang.Nguyen at inrix.com>:

> 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
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161229/86e4edde/attachment.html>


More information about the postgis-users mailing list