[postgis-users] PostGIS on Greenplum - Performance issue

[Suraj Birla]

Hi,

We have greenplum database with PostGIS extension.

Greenplum version :
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
PostGIS version
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"

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.
I downloaded the polygon for all countries in a table.
Added a geometry column to the transaction table and derived the value using ST_SetSRID(ST_MakePoint(idle_viol_pos_long_deg::double precision,idle_viol_pos_lat_deg::double precision),4326).

Then started updating the transaction table with country code using the below statement.

  update test a
  set country = su_a3
  from (select su_a3 ,geom  from ne_10m_admin_0_countries) b
  where ST_Intersects(a.geom, b.geom)
  and country is null

Updating 1 million records took around 1 hr.

If I try to update 20 millions , not sure how long it will take.

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.
Created index on geom column of the transaction table and still no gain.

Question. Whether I'm on the right track? If yes how to overcome the performance issue?
Any guidance would be appreciated.

Thanks
Suraj
=============== CONFIDENTIALITY NOTICE: This e-mail and any attachments contain information from Lytx, Inc. and/or its affiliates, and are intended solely for the use of the named recipient(s). This e-mail may contain confidential information of Lytx and its customers. Any dissemination of this e-mail by anyone other than an intended recipient is strictly prohibited. If you are not a named recipient, you are prohibited from any further viewing of the e-mail or any attachments or from making any use of the e-mail or attachments. If you believe you have received this e-mail in error, notify the sender immediately and permanently delete the e-mail, any attachments, and all copies thereof from any drives or storage media and destroy any printouts of the e-mail or attachments.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161115/477044c6/attachment.html>