[postgis-users] Query performanace against huge point table

[Gregory Williamson]

I forgot to note that if you try the method with the a new geometry column with the transformed data, you should create a GIST index on it and run analyze afterwards; otherwise the planner won't know anything about the new data.
GSW

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of Gregory Williamson
Sent: Tue 11/20/2007 2:43 AM
To: PostGIS Users Discussion; PostGIS Users Discussion
Subject: RE: [postgis-users] Query performanace against huge point table 
 
Santosh Gaikwad shaped the aether to ask us:

> I have a point table which contains 300 millions of records. I am using
> following query to fetch the records from the table within certain
> radius. 
> 
> Select *
> 
>    from table
>    where
>      st_dwithin(
>        transform(the_geom,2163),
>          transform(GeomFromText('POINT(-122.0527 37.323)',4326),2163),
>        1000
>     );
> 
> But it takes long time to get the result. I am seeking help.

Have you run "ANALYZE" on the table in question since the last load/update ?

I suspect that the transforming of the SRID is slowing things down. You might try creating a new column of point data in your table, and populate it with the 2163 SRID transform (precompute wherever possible!); a trigger on the table could allow this column to be updated automatically. Transforming the query point itself should be fast. It will increase the table size but not by a gross amount since point data is compact compared to multipolygons. 

I don't know SRID 2136 offhand but make sure that 1000 is a sensible measure; I've been bitten occasionally by this and ended up searching a far larger area than intended.

Finally, if you can post the results of:

EXPLAIN ANALYZE <your query here>;

that will tell us wiser people than me how the PostgreSQL planner itself is approaching the problem. Also include some information on your operating system, the version of PostgreSQL and of postGIS/GEOS, and perhaps show us some of the .config information on how much work_mem, shared buffers, and the like you have. There are a few parameters that can make a huge difference on performance.

HTH,

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)





-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 4585 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071120/dd1fac93/attachment.bin>