[postgis-users] Optimizer guess makes query 1000x slower?

[rm_postgis at cheapcomplexdevices.com]

On Tue, 15 Jun 2004 rm_postgis at cheapcomplexdevices.com wrote:
> On Tue, 15 Jun 2004, strk wrote:
> > What postgresql version are you using ?
> > Postgis built against PostgreSQL <= 74 require you to run
> > update_geometry_stats() to build statistics used by the
> > selectivity estimator.
> 
> I'm using 7.4.2 with postgis-0.8.1 .

Oooh!  Seems you were on to something.

Even though I have 7.4.2, it seemed running it manually helped!

Perhaps the problem was that I had a broken "geometry_columns" table,
because I apparently dropped a table without dropping the geometry
index first.


After cleaning up my geometry_columns and running updata_geometry_stats()
the optimizer now chooses the right plan!   Updated "explain analyze" info
below for the curious...

Thank you very much!

   Ron



===============================

ronmap=# select update_geometry_stats();
ERROR:  relation \"canada_hwy\" does not exist
CONTEXT:  PL/pgSQL function \"find_extent\" line 8 at for over execute
statement
PL/pgSQL function \"update_geometry_stats\" line 3 at execute statement
ronmap=# delete from geometry_columns where f_table_name = 'canada_hwy';
DELETE 1
ronmap=# select update_geometry_stats();
 update_geometry_stats 
-----------------------
 done
(1 row)
ronmap=# 
ronmap=# explain analyze 
   SELECT *
     FROM cpal_events,cpal_selected_events
    WHERE cpal_events.nextval=cpal_selected_events.eventid
      and cpal_selected_events.owner='user.23469878.20252'
      and the_geom && setSRID( 'BOX3D(-121.586865184167 38.4277618656666,-121.336132412548 38.6954249341005)'::BOX3D,find_srid('','cpal_events','the_geom'));

ronmap-# ronmap-# ronmap-# ronmap-# ronmap-#
                                                                 QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=4816.87..4853.24 rows=188 width=546) (actual time=534.186..729.499 rows=6357 loops=1)
   Merge Cond: ("outer".nextval = "inner".eventid)
   ->  Sort  (cost=4282.58..4286.93 rows=1739 width=503) (actual time=479.695..487.807 rows=9570 loops=1)
         Sort Key: cpal_events.nextval
         ->  Seq Scan on cpal_events  (cost=0.00..4188.99 rows=1739 width=503) (actual time=119.626..396.981 rows=9570 loops=1)
               Filter: (the_geom && 'SRID=-1;BOX3D(-121.586865184167 38.4277618656666 0,-121.336132412548 38.6954249341005 0)'::geometry)
   ->  Sort  (cost=534.29..550.19 rows=6360 width=43) (actual time=54.435..66.506 rows=6357 loops=1)
         Sort Key: cpal_selected_events.eventid
         ->  Seq Scan on cpal_selected_events  (cost=0.00..132.50 rows=6360 width=43) (actual time=0.069..30.847 rows=6357 loops=1)
               Filter: ("owner" = 'user.23469878.20252'::text)
 Total runtime: 739.775 ms
(11 rows)