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

[Paul Austin]

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/f0d01689/attachment.html>