[postgis-users] Possible index stats problem?

[Alexandre Florio]

Em Mon, 13 Sep 2004 20:54:02 +0200
strk at refractions.net escreveu:

> > 4. create lang plpgsql and insert postgis.sql and spatial_ref_sys.sql
> Didn't you get lots of errors in this step ?
No.  Why should I get them?


> Estimate is 103. Actual is 413. How many rows in the table ?
5260 rows.  Each one's geometry attribute has about 514 points.


> Try also to increment statistics target on the geometry column
> of your table to see if you can get a better estimate.
This is what I've done:
ALTER TABLE municipios ALTER the_geom SET STATISTICS 1000;


> Note that while with PG<8.0 full dataset was inspected with new
> VACUUM ANALYZE integration statistics are gathered inspecting only
> a subset of it. Incrementing target statistics (check postgres manual)
> does increment both the returned subset and the frequency of cells.
> (search_box overlaps 49.000000 cells ...).
Does it explains why analyzing in 8.0 is much faster than in <8.0?


> Let me know.
Unfortunately, no changes:

prototipo-gis=# VACUUM ANALYZE ;
[...]
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 3721.000000 cells
NOTICE:   avg feat overlaps 49.697033 cells
NOTICE:   SUM(ov_histo_cells)=1.464153
NOTICE:   gain=0.020122
NOTICE:   selectivity=0.029462
NOTICE:   returning computed value: 0.029462
                                                                                                                                                                      QUERY PLAN                                                                                                                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=322.03..322.03 rows=1 width=0) (actual time=48125.695..48125.696 rows=1 loops=1)
   ->  Seq Scan on municipios  (cost=0.00..321.90 rows=52 width=0) (actual time=161.512..48120.603 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: 48125.845 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 3721.000000 cells
NOTICE:   avg feat overlaps 49.697033 cells
NOTICE:   SUM(ov_histo_cells)=1.464153
NOTICE:   gain=0.020122
NOTICE:   selectivity=0.029462
NOTICE:   returning computed value: 0.029462
                                                                                     QUERY PLAN                                                                                 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=475.69..475.69 rows=1 width=0) (actual time=6718.553..6718.554 rows=1 loops=1)
   ->  Index Scan using municipios_gidx on municipios  (cost=0.00..475.56 rows=52 width=0) (actual time=75.169..6712.530 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: 6718.763 ms
(5 rows)
 

-- Alexandre