[postgis-users] PostGIS on Greenplum - Performance issue

[Darafei "Komяpa" Praliaskouski]

Hey,

did you create a gist index on geometry? can you show the query plans?
your e-mail also contains confidentiality notice and goes to a public list,
fix this into a right way.


вт, 15 нояб. 2016 г. в 21:55, Suraj Birla <sbirla at lytx.com>:

>
>
> 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.
> _______________________________________________
> 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/20161115/0802cd6f/attachment.html>