<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal">Hi,<o:p></o:p></p>
<p class="MsoNormal"><br>
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<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Here are my DB configuration settings:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">"max_locks_per_transaction";"64"<o:p></o:p></p>
<p class="MsoNormal">"max_parallel_workers_per_gather";"4"<o:p></o:p></p>
<p class="MsoNormal">"max_pred_locks_per_transaction";"64"<o:p></o:p></p>
<p class="MsoNormal">"max_prepared_transactions";"0"<o:p></o:p></p>
<p class="MsoNormal">"max_replication_slots";"5"<o:p></o:p></p>
<p class="MsoNormal">"max_stack_depth";"6MB"<o:p></o:p></p>
<p class="MsoNormal">"max_standby_archive_delay";"30s"<o:p></o:p></p>
<p class="MsoNormal">"max_standby_streaming_delay";"30s"<o:p></o:p></p>
<p class="MsoNormal">"max_wal_senders";"10"<o:p></o:p></p>
<p class="MsoNormal">"max_wal_size";"2GB"<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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<o:p></o:p></p>
<p class="MsoNormal"><br>
Explain plan:<o:p></o:p></p>
<p class="MsoNormal">"Limit (cost=100.99..1266217.13 rows=1000 width=1680)"<o:p></o:p></p>
<p class="MsoNormal">" -> Nested Loop (cost=100.99..88014164.76 rows=69515 width=1680)"<o:p></o:p></p>
<p class="MsoNormal">" Join Filter: ((t.waypoints && zone.geom) AND _st_intersects(t.waypoints, zone.geom))"<o:p></o:p></p>
<p class="MsoNormal">" -> Index Scan using trip_v2_y2016m09w1_pkey on trip_v2_y2016m09w1 t (cost=0.57..32749625.17 rows=10427221 width=1680)"<o:p></o:p></p>
<p class="MsoNormal">" 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))"<o:p></o:p></p>
<p class="MsoNormal">" -> Materialize (cost=100.42..268.34 rows=20 width=4380)"<o:p></o:p></p>
<p class="MsoNormal">" -> Foreign Scan on remote_zone zone (cost=100.42..268.24 rows=20 width=4380)"<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Any help would be appreciated.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks,<br>
Trang<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p></o:p></p>
</div>
</body>
</html>