Using ST_Simplify reduces the time down to 3 minutes for the same query.<br><br>Paul<br>
<br><br><div class="gmail_quote">On Wed, Jul 22, 2009 at 1:38 PM, Paul Austin <span dir="ltr"><<a href="mailto:mail-lists@revolsys.com">mail-lists@revolsys.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Paul,<br><br>Using a similar data set with 1.4 million rows (mfar might mean something to you) <br><br>Using a smaller polygon (the other was the whole of BC) I have been able to force it to use the index. This for most cases is resulting in about 1 minute to query and extract the data for a given region to a shape file, this includes the JDBC time to get the data and write it to disk.<br>
<br>However if the source polygon has a larger number of coordinates the query is very slow. Taking 15 minutes to execute, 13.5 min for the raw query. This is probably a lot slower than it would take to download the whole table and perform the intersects using JTS in memory to filter out the data.<br>
<br>I'm going to try reducing the precision to 1m accuracy and running a simplification on the query geometry to compare the speeds.<br><br>Have you done any comparisons using DWithin(geom1, geom2, 1) compared to ST_intersects and when one works better than the other.<br>
<br>On a side not if using the postgresql JDBC driver connection.setAutoCommit(false) and statement.setFetchSize(10) (any value > 0). This will cause the JDBC driver to stream the results in fetch size increments rather than load the whole result set into memory!<br>
<font color="#888888">
<br>Paul</font><div><div></div><div class="h5"><br>
<br><br><div class="gmail_quote">On Fri, Jul 17, 2009 at 11:50 AM, Paul Ramsey <span dir="ltr"><<a href="mailto:pramsey@opengeo.org" target="_blank">pramsey@opengeo.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
You either have a small table (which 200k rows is not necessarily) or<br>
your bounding box is a large % of your data (which it might be, that<br>
looks like a big range).<br>
<br>
P.<br>
<div><div></div><div><br>
On Fri, Jul 17, 2009 at 9:58 AM, Paul Austin<<a href="mailto:mail-lists@revolsys.com" target="_blank">mail-lists@revolsys.com</a>> wrote:<br>
> I have a table with a geometry column in BC Albers (3005) for which I have<br>
> created a GIST index. I'm using postgresql 8.3.7 with Postgis 1.3.5-1.<br>
><br>
> CREATE INDEX my_table_geom_gist<br>
> ON my_table<br>
> USING gist<br>
> (geom);<br>
><br>
> I ran the VACUUM ANALYZE on the table.<br>
><br>
> When I run the following it shows a sequential scan rather than an index<br>
> scan.<br>
> explain SELECT geom FROM my_table WHERE geom && 'BOX3D(250982 1754957,<br>
> 1369941 482671)'::box3d<br>
><br>
> Ultimately I want to be able to query using the ST_Intersects function but<br>
> if the index is not being used with the && operator then it's unlikely to<br>
> using the index with ST_Intersects.<br>
><br>
> In some forum posts there was a gist_geometry_ops option when creating<br>
> indexes but that doesn't seem to work with the version I have.<br>
><br>
> What are the real secrets to get postgis to use spatial indexes? Or do I<br>
> just not have enough data in my tables?<br>
><br>
> Paul<br>
><br>
</div></div>> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
><br>
><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</blockquote></div><br>
</div></div></blockquote></div><br>