[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Postgis query not making using of parallel query execution
From: Darafei_"Komяpa"_Praliaskouski <me () komzpa ! net>
Date: 2016-12-29 12:24:51
Message-ID: CAC8Q8tK1gUPzJqAUZCJCBqhedFW2JdjMXd3FCV9mgiCZT0ZU7g () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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@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','ed2d \
> c985-9035-45ea-a026-14f0f34e2e5c','95222e3a-14dd-4677-969d-86c05bfd2cff','47192cfa-4 \
> 41f-4ef9-b5a4-5dd8608621db','dc94e748-8814-4f46-842b-54597e3c88de','d3bac4cc-ebd7-48 \
> 0b-a530-2e9e3eea2437','b0257117-a66d-4a53-8371-de13cc6b7f3c','cd2dc6c5-94cb-4586-a5a \
> 1-1e494da4437a','e7d9aeca-6117-413d-abe5-b90fb52b38f3','c86f12bd-de6a-4e03-b72e-8fb9 \
> be30099a','37e8ce9f-fd50-4152-8e2e-10b67ad49142','086480df-81c8-4309-a99f-d4c502fc84 \
> 63','bf23ac00-eb6a-4a6a-af66-c9e7dfd8da12','3c56bc1a-4abd-4912-8591-76d6ec6c036c','9 \
> 2ee1ea0-9241-4274-8797-53ad95277b0b','6dce995f-4ba9-49d4-838d-6ec0c2ac191f','c4d9376 \
> 9-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@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
[Attachment #5 (text/html)]
<div dir="ltr">Hello, can you please do <br><br> <span \
style="font-family:monospace">\sf st_intersects (geometry, geometry)<br><br>and \
similar queries drilling down to each function, and confirm that they are all \
installed as PARALLEL SAFE?<br> <br></span></div><br><div class="gmail_quote"><div \
dir="ltr">чт, 29 дек. 2016 г. в 4:39, Trang Nguyen <<a \
href="mailto:Trang.Nguyen@inrix.com">Trang.Nguyen@inrix.com</a>>:<br></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">
<div lang="EN-US" link="#0563C1" vlink="#954F72" class="gmail_msg">
<div class="m_-7995323744171728001WordSection1 gmail_msg">
<p class="MsoNormal gmail_msg">Hi,<u class="gmail_msg"></u><u \
class="gmail_msg"></u></p> <p class="MsoNormal gmail_msg"><br class="gmail_msg">
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<u class="gmail_msg"></u><u class="gmail_msg"></u></p> <p \
class="MsoNormal gmail_msg"><u class="gmail_msg"></u> <u class="gmail_msg"></u></p> \
<p class="MsoNormal gmail_msg">Here are my DB configuration settings:<u \
class="gmail_msg"></u><u class="gmail_msg"></u></p> <p class="MsoNormal gmail_msg"><u \
class="gmail_msg"></u> <u class="gmail_msg"></u></p> <p class="MsoNormal \
gmail_msg">"max_locks_per_transaction";"64"<u \
class="gmail_msg"></u><u class="gmail_msg"></u></p> <p class="MsoNormal \
gmail_msg">"max_parallel_workers_per_gather";"4"<u \
class="gmail_msg"></u><u class="gmail_msg"></u></p> <p class="MsoNormal \
gmail_msg">"max_pred_locks_per_transaction";"64"<u \
class="gmail_msg"></u><u class="gmail_msg"></u></p> <p class="MsoNormal \
gmail_msg">"max_prepared_transactions";"0"<u \
class="gmail_msg"></u><u class="gmail_msg"></u></p> <p class="MsoNormal \
gmail_msg">"max_replication_slots";"5"<u class="gmail_msg"></u><u \
class="gmail_msg"></u></p> <p class="MsoNormal \
gmail_msg">"max_stack_depth";"6MB"<u class="gmail_msg"></u><u \
class="gmail_msg"></u></p> <p class="MsoNormal \
gmail_msg">"max_standby_archive_delay";"30s"<u \
class="gmail_msg"></u><u class="gmail_msg"></u></p> <p class="MsoNormal \
gmail_msg">"max_standby_streaming_delay";"30s"<u \
class="gmail_msg"></u><u class="gmail_msg"></u></p> <p class="MsoNormal \
gmail_msg">"max_wal_senders";"10"<u class="gmail_msg"></u><u \
class="gmail_msg"></u></p> <p class="MsoNormal \
gmail_msg">"max_wal_size";"2GB"<u class="gmail_msg"></u><u \
class="gmail_msg"></u></p> <p class="MsoNormal gmail_msg"><u class="gmail_msg"></u> \
<u class="gmail_msg"></u></p> <p class="MsoNormal gmail_msg">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-4a30d9807 \
8a6','ed2dc985-9035-45ea-a026-14f0f34e2e5c','95222e3a-14dd-4677-969d-8 \
6c05bfd2cff','47192cfa-441f-4ef9-b5a4-5dd8608621db','dc94e748-8814-4f4 \
6-842b-54597e3c88de','d3bac4cc-ebd7-480b-a530-2e9e3eea2437','b0257117- \
a66d-4a53-8371-de13cc6b7f3c','cd2dc6c5-94cb-4586-a5a1-1e494da4437a','e \
7d9aeca-6117-413d-abe5-b90fb52b38f3','c86f12bd-de6a-4e03-b72e-8fb9be30099a' \
;,'37e8ce9f-fd50-4152-8e2e-10b67ad49142','086480df-81c8-4309-a99f-d4c502fc \
8463','bf23ac00-eb6a-4a6a-af66-c9e7dfd8da12','3c56bc1a-4abd-4912-8591- \
76d6ec6c036c','92ee1ea0-9241-4274-8797-53ad95277b0b','6dce995f-4ba9-49 \
d4-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<u \
class="gmail_msg"></u><u class="gmail_msg"></u></p> <p class="MsoNormal \
gmail_msg"><br class="gmail_msg"> Explain plan:<u class="gmail_msg"></u><u \
class="gmail_msg"></u></p> <p class="MsoNormal gmail_msg">"Limit \
(cost=100.99..1266217.13 rows=1000 width=1680)"<u class="gmail_msg"></u><u \
class="gmail_msg"></u></p> <p class="MsoNormal gmail_msg">" -> Nested \
Loop (cost=100.99..88014164.76 rows=69515 width=1680)"<u \
class="gmail_msg"></u><u class="gmail_msg"></u></p> <p class="MsoNormal \
gmail_msg">" Join Filter: ((t.waypoints && zone.geom) AND \
_st_intersects(t.waypoints, zone.geom))"<u class="gmail_msg"></u><u \
class="gmail_msg"></u></p> <p class="MsoNormal gmail_msg">" -> \
Index Scan using trip_v2_y2016m09w1_pkey on trip_v2_y2016m09w1 t \
(cost=0.57..32749625.17 rows=10427221 width=1680)"<u class="gmail_msg"></u><u \
class="gmail_msg"></u></p> <p class="MsoNormal gmail_msg">" \
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))"<u class="gmail_msg"></u><u class="gmail_msg"></u></p>
<p class="MsoNormal gmail_msg">" -> Materialize \
(cost=100.42..268.34 rows=20 width=4380)"<u class="gmail_msg"></u><u \
class="gmail_msg"></u></p> <p class="MsoNormal gmail_msg">" \
-> Foreign Scan on remote_zone zone (cost=100.42..268.24 rows=20 \
width=4380)"<u class="gmail_msg"></u><u class="gmail_msg"></u></p> <p \
class="MsoNormal gmail_msg"><u class="gmail_msg"></u> <u class="gmail_msg"></u></p> \
<p class="MsoNormal gmail_msg">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?<u class="gmail_msg"></u><u class="gmail_msg"></u></p> <p \
class="MsoNormal gmail_msg"><u class="gmail_msg"></u> <u class="gmail_msg"></u></p> \
<p class="MsoNormal gmail_msg">Any help would be appreciated.<u \
class="gmail_msg"></u><u class="gmail_msg"></u></p> <p class="MsoNormal gmail_msg"><u \
class="gmail_msg"></u> <u class="gmail_msg"></u></p> <p class="MsoNormal \
gmail_msg">Thanks,<br class="gmail_msg"> Trang<u class="gmail_msg"></u><u \
class="gmail_msg"></u></p> <p class="MsoNormal gmail_msg"><u class="gmail_msg"></u> \
<u class="gmail_msg"></u></p> <p class="MsoNormal gmail_msg"><u \
class="gmail_msg"></u><u class="gmail_msg"></u></p> </div>
</div>
_______________________________________________<br class="gmail_msg">
postgis-users mailing list<br class="gmail_msg">
<a href="mailto:postgis-users@lists.osgeo.org" class="gmail_msg" \
target="_blank">postgis-users@lists.osgeo.org</a><br class="gmail_msg"> <a \
href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" \
class="gmail_msg" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic