[postgis-users] Possible index stats problem?

[Alexandre Florio]

Hi,

    I have upgraded from PgSQL/PostGIS/GEOS 7.4/0.82/1.0 to 8.0.0beta2/0.9/2.0.0.  These were the steps I took to upgrade:

1. dump database; drop database
2. remove geometry_columns create (table and index) statements (and also the whole spatial_ref_sys table) from the dump
3. create new database
4. create lang plpgsql and insert postgis.sql and spatial_ref_sys.sql
5. alter table geometry_columns, adding those 3 columns:  attrelid (int), varattnum (int), stats (text), so that I can import the dump without having syntax errors (old version's geometry_columns had those 3 attrs).
6. import the dump (no errors reported)
7. drop the 3 columns added in `5'
8. VACUUM ANALYZE

    I don't know if this migration approach is correct, so, if the steps above mess up with the indexes and stats, no need to read below...

    Assuming that the migration was done correctly.

    I've found the same problem described in this post (http://postgis.refractions.net/pipermail/postgis-users/2004-August/005547.html) in Postgis 0.9:

prototipo-gis=# SELECT version();
                                                               version                                                                                 
--------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.0.0beta2 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)
                                                                                
prototipo-gis=# SELECT postgis_version();
            postgis_version
---------------------------------------
 0.9 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)
                                                                                
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));
NOTICE:  postgis_gist_sel called
NOTICE:   search_box overlaps 49.000000 cells
NOTICE:   avg feat overlaps 2.388718 cells
NOTICE:   SUM(ov_histo_cells)=0.139461
NOTICE:   gain=0.418635
NOTICE:   selectivity=0.058383
NOTICE:   returning computed value: 0.058383
                                                                                                                                                                      QUERY PLAN                                                                                                                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=322.16..322.16 rows=1 width=0) (actual time=46143.496..46143.498 rows=1 loops=1)
   ->  Seq Scan on municipios  (cost=0.00..321.90 rows=103 width=0) (actual time=111.860..46136.221 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: 46143.655 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));
NOTICE:  postgis_gist_sel called
NOTICE:   search_box overlaps 49.000000 cells
NOTICE:   avg feat overlaps 2.388718 cells
NOTICE:   SUM(ov_histo_cells)=0.139461
NOTICE:   gain=0.418635
NOTICE:   selectivity=0.058383
NOTICE:   returning computed value: 0.058383
                                                                                     QUERY PLAN                                                                                 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=763.28..763.28 rows=1 width=0) (actual time=6853.987..6853.988 rows=1 loops=1)
   ->  Index Scan using municipios_gidx on municipios  (cost=0.00..763.02 rows=103 width=0) (actual time=172.699..6848.285 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)
 Total runtime: 6854.703 ms
(5 rows)
 
prototipo-gis=#

-- Alexandre