<div dir="ltr">I created the index on the geom column on both the tables..but the index is not being used as per execution plan. Analyze the table also.. still index not used.<div><br></div><div><div>here is the DDL for the table<br></div><div><br></div><div><div>CREATE TABLE test</div><div>(</div><div> idle_viol_id character varying(36),</div><div> country_cd character varying(3),</div><div> country character varying(3),</div><div> geom geometry,</div><div> CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),</div><div> CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL),</div><div> CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326)</div><div>)</div><div>WITH (</div><div> OIDS=FALSE</div><div>)</div><div>DISTRIBUTED BY (idle_viol_id);</div><div><br></div><div><br></div><div>-- Index: idol_viol_point_gix</div><div><br></div><div>-- DROP INDEX idol_viol_point_gix;</div><div><br></div><div>CREATE INDEX idol_viol_point_gix</div><div> ON test</div><div> USING gist</div><div> (geom);</div><div><br></div><div><br></div><div><br></div><div><div>CREATE TABLE usa_adm1</div><div>(</div><div> gid serial NOT NULL,</div><div> id_0 numeric(10,0),</div><div> iso character varying(3),</div><div> name_0 character varying(75),</div><div> id_1 numeric(10,0),</div><div> name_1 character varying(75),</div><div> hasc_1 character varying(15),</div><div> ccn_1 numeric(10,0),</div><div> cca_1 character varying(254),</div><div> type_1 character varying(50),</div><div> engtype_1 character varying(50),</div><div> nl_name_1 character varying(50),</div><div> varname_1 character varying(150),</div><div> geom geometry,</div><div> CONSTRAINT usa_adm1_pkey PRIMARY KEY (gid),</div><div> CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),</div><div> CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL),</div><div> CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326)</div><div>)</div><div>WITH (</div><div> OIDS=FALSE</div><div>)</div><div>DISTRIBUTED BY (gid);</div><div><br></div><div><br></div><div>-- Index: usa_adm1_gix</div><div><br></div><div><br></div><div>CREATE INDEX usa_adm1_gix</div><div> ON usa_adm1</div><div> USING gist</div><div> (geom);</div></div><div><br></div><div><br></div><div><div> update test a</div><div> set country = iso </div><div> from usa_adm2 b -- in (239,40,173,82,16,147)</div><div> where ST_Intersects(a.geom, b.geom)</div></div><div><br></div><div>explain plan</div><div><br></div><div><div>Update (cost=0.00..7504805.69 rows=4179111 width=1)</div><div> -> Result (cost=0.00..5872340.80 rows=8358221 width=50)</div><div> -> Assert (cost=0.00..5871922.89 rows=8358221 width=107)</div><div> Assert Cond: (geometrytype(public.test.<wbr>geom) = 'POINT'::text OR public.test.geom IS NULL) IS DISTINCT FROM false AND (st_ndims(public.test.geom) = 2) IS DISTINCT FROM false AND (st_srid(public.test.geom) = 4326) IS DISTINCT FROM false</div><div> -> Split (cost=0.00..5871028.56 rows=8358221 width=107)</div><div> -> Nested Loop (cost=0.00..5870134.23 rows=4179111 width=107)</div><div> Join Filter: public.test.geom && usa_adm1.geom AND _st_intersects(public.test.<wbr>geom, usa_adm1.geom)</div><div> -> Broadcast Motion 4:4 (slice1; segments: 4) (cost=0.00..624.34 rows=52 width=258822)</div><div> -> Table Scan on usa_adm1 (cost=0.00..432.85 rows=13 width=258822)</div><div> -> Table Scan on test (cost=0.00..450.11 rows=282542 width=103)</div><div>Settings: optimizer=on</div><div>Optimizer status: PQO version 1.650</div></div><div><br></div><div><br></div><div><p class="MsoNormal"><span style="color:rgb(31,73,125);font-family:calibri,sans-serif;font-size:11pt"><span> Thanks</span></span></p><p class="MsoNormal"><span style="color:rgb(31,73,125);font-family:calibri,sans-serif;font-size:11pt"><span>Suraj</span></span></p>
<p class="MsoNormal"><a name="m_978960013531591997__MailEndCompose"><span style="color:rgb(31,73,125);font-family:calibri,sans-serif;font-size:11pt"><span> </span></span></a></p>
<p class="MsoNormal"><a name="m_978960013531591997__MailOriginal"><b><span style="font-family:calibri,sans-serif;font-size:11pt">From:</span></b></a><span style="font-family:calibri,sans-serif;font-size:11pt"> postgis-users
[mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@<wbr>lists.osgeo.org</a>] <b>On Behalf Of </b>Darafei
"Kom?pa" Praliaskouski<br>
<b>Sent:</b> Tuesday, November 15, 2016 1:43 PM<br>
<b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<b>Subject:</b> Re: [postgis-users] PostGIS on Greenplum - Performance issue<span></span></span></p>
<p class="MsoNormal"><span> </span></p>
<p class="MsoNormal">Hey,<span></span></p>
<p class="MsoNormal"><br>
did you create a gist index on geometry? can you show the query plans? <br>
your e-mail also contains confidentiality notice and goes to a public list, fix
this into a right way.<span></span></p>
<p class="MsoNormal"><span> </span></p>
<p class="MsoNormal"><span> </span></p>
<p class="MsoNormal">вт, 15 нояб. 2016
г. в 21:55, Suraj Birla <<a href="mailto:sbirla@lytx.com" target="_blank">sbirla@lytx.com</a>>:<span></span></p>
<p class="MsoNormal"> <span></span></p>
<p class="MsoNormal">Hi,<span></span></p>
<p class="MsoNormal"> <span></span></p>
<p class="MsoNormal">We have greenplum database with PostGIS
extension.<span></span></p>
<p class="MsoNormal"> <span></span></p>
<p class="MsoNormal"><b><u>Greenplum version :</u></b> <span></span></p>
<p class="MsoNormal">PostgreSQL 8.2.15 (Greenplum Database
4.3.9.0 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2
compiled on Aug 8 2016 05:36:26<span></span></p>
<p class="MsoNormal"><b><u>PostGIS version</u></b><span></span></p>
<p class="MsoNormal">POSTGIS="2.0.3 r11128"
GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012"
LIBXML="2.7.8"<span></span></p>
<p class="MsoNormal"> <span></span></p>
<p class="MsoNormal">I’m working on a POC to find the
country/state/country of a transaction based on the GPS lon and lat. The
transaction table has 15-20 millions records.<span></span></p>
<p class="MsoNormal">I downloaded the polygon for all countries
in a table.<span></span></p>
<p class="MsoNormal">Added a geometry column to the transaction table
and derived the value using
ST_SetSRID(ST_MakePoint(idle_<wbr>viol_pos_long_deg::double
precision,idle_viol_pos_lat_<wbr>deg::double precision),4326).<span></span></p>
<p class="MsoNormal"> <span></span></p>
<p class="MsoNormal">Then started updating the transaction table
with country code using the below statement. <span></span></p>
<p class="MsoNormal"> <span></span></p>
<p class="MsoNormal"> update test a<span></span></p>
<p class="MsoNormal"> set country = su_a3 <span></span></p>
<p class="MsoNormal"> from (select su_a3 ,geom
from ne_10m_admin_0_countries) b <span></span></p>
<p class="MsoNormal"> where ST_Intersects(a.geom,
b.geom)<span></span></p>
<p class="MsoNormal"> and country is null<span></span></p>
<p class="MsoNormal"> <span></span></p>
<p class="MsoNormal">Updating 1 million records took around 1 hr.<span></span></p>
<p class="MsoNormal"> <span></span></p>
<p class="MsoNormal">If I try to update 20 millions , not sure
how long it will take.<span></span></p>
<p class="MsoNormal"> <span></span></p>
<p class="MsoNormal">I tried to reduce the entry in polygon table
to 5 as the transaction received is only from 5 countries.. Didn’t see any gain
in the performance.<span></span></p>
<p class="MsoNormal">Created index on geom column of the
transaction table and still no gain.<span></span></p>
<p class="MsoNormal"> <span></span></p>
<p class="MsoNormal">Question. Whether I’m on the right track? If
yes how to overcome the performance issue?<span></span></p>
<p class="MsoNormal">Any guidance would be appreciated.<span></span></p>
<p class="MsoNormal"> <span></span></p>
<p class="MsoNormal">Thanks<span></span></p>
<p class="MsoNormal">Suraj <span></span></p>
<p class="MsoNormal">______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><span></span></p></div></div></div></div>