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

[Paul Ramsey]

Prepared geometries (PostGIS 1.4 + GEOS 3.1) will reduce time
heartily, even with an unsimplified query geometry. If your table is
point geometries, then the latest 1.3 will also include much faster
results.

P.

On Wed, Jul 22, 2009 at 1:54 PM, Paul Austin<mail-lists at revolsys.com> wrote:
> 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
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>