[postgis-users] Indexes (ENABLE_SEQSCAN)

[Alexandre Florio]

Hi Paul,

    Thanks for your answer.
    I already did this, and also VACUUM ANALYZE, before testing:


prototipo-gis=# select postgis_version();
postgis_version
---------------------------------------
0.8 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

prototipo-gis=# vacuum analyze;
VACUUM

prototipo-gis=# explain analyze SELECT COUNT(*) as cnt FROM "municipios" WHERE (intersects("the_geom", GeometryFromText('POLYGON ((-44.874111 -24.12874, -44.874111 -19.832116, -40.580777 -19.832116, -40.580777 -24.12874, -44.874111 -24.12874))', 4291)) AND "the_geom" && GeometryFromText('POLYGON ((-44.874111 -24.12874, -44.874111 -19.832116, -40.580777 -19.832116, -40.580777 -24.12874, -44.874111 -24.12874))', 4291));
--------------------
 Aggregate  (cost=321.15..321.15 rows=1 width=0) (actual time=65366.716..65366.718 rows=1 loops=1)
   ->  Seq Scan on municipios  (cost=0.00..320.90 rows=98 width=0) (actual time=143.508..65361.887 rows=413 loops=1)
         Filter: (intersects(the_geom, 'SRID=4291;POLYGON((-44.874111 -24.12874,-44.874111 -19.832116,-40.580777 -19.832116,-40.580777 -24.12874,-44.874111 -24.12874))'::geometry) AND (the_geom && 'SRID=4291;POLYGON((-44.874111 -24.12874,-44.874111 -19.832116,-40.580777 -19.832116,-40.580777 -24.12874,-44.874111 -24.12874))'::geometry))
 Total runtime: 65367.649 ms
(4 rows)
                                                                                
prototipo-gis=# set enable_seqscan=off;
SET

prototipo-gis=# explain analyze SELECT COUNT(*) as cnt FROM "municipios" WHERE (intersects("the_geom", GeometryFromText('POLYGON ((-44.874111 -24.12874, -44.874111 -19.832116, -40.580777 -19.832116, -40.580777 -24.12874, -44.874111 -24.12874))', 4291)) AND "the_geom" && GeometryFromText('POLYGON ((-44.874111 -24.12874, -44.874111 -19.832116, -40.580777 -19.832116, -40.580777 -24.12874, -44.874111 -24.12874))', 4291));
--------------------
 Aggregate  (cost=740.00..740.00 rows=1 width=0) (actual time=5333.920..5333.922 rows=1 loops=1)
   ->  Index Scan using municipios_gidx on municipios  (cost=0.00..739.76 rows=98 width=0) (actual time=68.281..5329.132 rows=413 loops=1)
         Index Cond: (the_geom && 'SRID=4291;POLYGON((-44.874111 -24.12874,-44.874111 -19.832116,-40.580777 -19.832116,-40.580777 -24.12874,-44.874111 -24.12874))'::geometry)
         Filter: (intersects(the_geom, 'SRID=4291;POLYGON((-44.874111 -24.12874,-44.874111 -19.832116,-40.580777 -19.832116,-40.580777 -24.12874,-44.874111 -24.12874))'::geometry) AND (the_geom && 'SRID=4291;POLYGON((-44.874111 -24.12874,-44.874111 -19.832116,-40.580777 -19.832116,-40.580777 -24.12874,-44.874111 -24.12874))'::geometry))
 Total runtime: 5334.204 ms
(5 rows)
 
prototipo-gis=# select version();
version
-------
 PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 20040412 (Gentoo Linux 3.3.3-r6, ssp-3.3.2-2, pie-8.7.6)
(1 row)


-- Alexandre


Em Mon, 30 Aug 2004 21:42:29 -0700
Paul Ramsey <pramsey at refractions.net> escreveu:

> Run "select update_geometry_stats();" and see if the planner does not 
> get smarter about whether or not to use the index.
> 
> P.
> 
> Alexandre Florio wrote:
> > Analyzing the archives, this seems to be an exhaustively discussed
> > problem which was apparently fixed in version 0.6.
> > 
> > The documentation says that I do not need, anymore, to use "SET
> > ENABLE_SEQSCAN=OFF" to force the planner to use the spatial index.
> > Nevertheless, I have a database in which a query do execute much
> > faster when setting ENABLE_SEQSCAN to OFF.
> > 
> > Before dumping the table scheme, queries and outputs I would like to
> > confirm that this should not happen in the current version of PostGIS
> > (0.8.2).
> > 
> > Thanks, Alexandre Florio
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>