[postgis-users] Possible index stats problem?
strk at refractions.net
strk at refractions.net
Mon Sep 13 11:54:02 PDT 2004
On Mon, Sep 13, 2004 at 03:16:20PM -0300, Alexandre Florio wrote:
> 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
Didn't you get lots of errors in this step ?
> 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...
Estimate is 103. Actual is 413. How many rows in the table ?
Try also to increment statistics target on the geometry column
of your table to see if you can get a better estimate.
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 ...).
Let me know.
--strk;
>
> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list