[postgis-users] Speed up query

[Gheorghiu, Mihai]

It is slow.
EXPLAIN VERBOSE:
"Nested Loop  (cost=0.00..99912244399.51 rows=520868 width=37)"
"  Output: a.point_id, b.polygon_id"
"  Join Filter: ((st_setsrid(st_geomfromwkb(st_asbinary(b.geog)), 4326) && st_setsrid(st_geomfromwkb(st_asbinary(a.geog)), 4326)) AND _st_contains(st_setsrid(st_geomfromwkb(st_asbinary(b.geog)), 4326), st_setsrid(st_geomfromwkb(st_asbinary(a.geog)), 4326)))"
"  ->  Seq Scan on points b  (cost=0.00..22058.03 rows=288003 width=2135)"
"        Output: b.polygon_id, b.geog"
"  ->  Seq Scan on polygons a  (cost=0.00..29513.30 rows=1085130 width=138)"
"        Output: a.point_id, a.geog"


From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Szymon Guz
Sent: Wednesday, October 19, 2011 12:28 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Speed up query


On 19 October 2011 18:21, Gheorghiu, Mihai <mgheorghiu at chpmail.com<mailto:mgheorghiu at chpmail.com>> wrote:
I have a query that places points in polygons:
insert into results (
                select a.point_id, b.polygon_id
                from  points a, polygons b
                where st_contains(ST_GeomFromWKB(ST_AsBinary(b.geog),4326),ST_GeomFromWKB(ST_AsBinary(a.geog),4326)) = 't'
);
There are ~1M points and ~300k polygons. There are geo indexes on the geog columns of both tables.
What changes would make the query faster?

Thank you very much.

Michael


... and the problem is?

Especially I'd like to know is the time, show us explain analyze of the query and show table structures.

regards
Szymon


--
http://simononsoftware.com/


The information contained in this transmission is to be considered CONFIDENTIAL and PROPRIETARY to Consortium Health Plans, Inc. and intended for the use of the Individual or Entity named above.  If the reader of this message is not the Intended Recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is Strictly Prohibited.  If you have received this transmission in error, please notify us immediately by telephone at 410-772-2900 or return email to sender immediately.  Thank You.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111019/8cd2dab3/attachment.html>