[postgis-users] new estimation functions in PostGIS

[David Blasby]

I've commited the new changes to postgis.  You shouldnt notice anything
different (ie. if you're risk adverse, dont run the "select
UPDATE_GEOMETRY_STATS();" command and everything should run as normal).

To actually use it -

1. create a new database and load the postgis.sql file
2. load your data (make sure your geometry_columns table is up-to-date)
3. do a:
        select UPDATE_GEOMETRY_STATS();
    This will populate the 'stats' column of the geometry_columns table
4. you should notice that that your queries are better
    use "EXPLAIN ANALYSE <query>"
    with the commands  "set enable_seqscan =<on/off>" and "set
enable_indexscan=<on/off>".


If you're using this, please tell me how the row estimates and time estimates
are for your data and machine.

dave2=# explain  analyse select  * from test_data where the_geom &&
'BOX3D(468759 1019897 0,867111 1508599 0)'::box3d;
NOTICE:  QUERY PLAN:

Index Scan using g_idx on test_data (cost=0.00..764.54 rows=2908 width=36)
(actual time=0.37..20.00 rows=2600 loops=1)
Total runtime: 21.56 msec

EXPLAIN


The important parts are near the end -"(cost=0.00..764.54 rows=2908 width=36)"
and "(actual time=0.37..20.00 rows=2600 loops=1)".  The first is the estimate,
the second is the actual.

In this case, it estimates 2908 rows, but actually gets 2600.  The cost/time
are more difficult - you should only use these to compare different query
plans.

dave2=# set enable_indexscan =off;
SET VARIABLE
dave2=# explain  analyse select  * from test_data where the_geom &&
'BOX3D(468759 1019897 0,867111 1508599 0)'::box3d;
NOTICE:  QUERY PLAN:

Seq Scan on test_data  (cost=0.00..9078.50 rows=2908 width=36) (actual
time=0.32..526.18 rows=2600 loops=1)
Total runtime: 527.26 msec



Here, we have the sequence scan running very much slower, and its cost is very
much higher!



Remember, this is still experimental!

dave

THIS DOESNT DO ANYTHING UNDER POSTGRESQL 7.1