[postgis-users] Indexes on table with 200,000 rows

[Paul Austin]

Using ST_Simplify reduces the time down to 3 minutes for the same query.

Paul


On Wed, Jul 22, 2009 at 1:38 PM, Paul Austin <mail-lists at revolsys.com>wrote:

> Paul,
>
> Using a similar data set with 1.4 million rows (mfar might mean something
> to you)
>
> 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.
>
> 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.
>
> I'm going to try reducing the precision to 1m accuracy and running a
> simplification on the query geometry to compare the speeds.
>
> Have you done any comparisons using DWithin(geom1, geom2, 1) compared to
> ST_intersects and when one works better than the other.
>
> 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!
>
> Paul
>
>
>
> On Fri, Jul 17, 2009 at 11:50 AM, Paul Ramsey <pramsey at opengeo.org> wrote:
>
>> You either have a small table (which 200k rows is not necessarily) or
>> your bounding box is a large % of your data (which it might be, that
>> looks like a big range).
>>
>> P.
>>
>> On Fri, Jul 17, 2009 at 9:58 AM, Paul Austin<mail-lists at revolsys.com>
>> wrote:
>> > I have a table with a geometry column in BC Albers (3005) for which I
>> have
>> > created a GIST index. I'm using postgresql 8.3.7 with Postgis 1.3.5-1.
>> >
>> > CREATE INDEX my_table_geom_gist
>> >   ON my_table
>> >   USING gist
>> >   (geom);
>> >
>> > I ran the VACUUM ANALYZE on the table.
>> >
>> > When I run the following it shows a sequential scan rather than an index
>> > scan.
>> > explain SELECT  geom FROM my_table WHERE geom && 'BOX3D(250982 1754957,
>> > 1369941 482671)'::box3d
>> >
>> > Ultimately I want to be able to query using the ST_Intersects function
>> but
>> > if the index is not being used with the && operator then it's unlikely
>> to
>> > using the index with ST_Intersects.
>> >
>> > In some forum posts there was a gist_geometry_ops option when creating
>> > indexes but that doesn't seem to work with the version I have.
>> >
>> > What are the real secrets to get postgis to use spatial indexes? Or do I
>> > just not have enough data in my tables?
>> >
>> > Paul
>> >
>> > _______________________________________________
>> > postgis-users mailing list
>> > postgis-users at postgis.refractions.net
>> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >
>> >
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090722/6f9f3e31/attachment.html>