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: [EMAIL PROTECTED] 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.)
<<winmail.dat>>
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
