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

[strk]

On Tue, Jun 15, 2004 at 03:53:46AM -0700, rm_postgis at cheapcomplexdevices.com wrote:
> 
> 
> 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

update_geometry_stats() needs always be run manually.
automatic statistics gathering for custom operators is available
from postgresql 75 only.

A curiosity, how many geometry records are there ?

--strk;

> 
> 
> 
> ===============================
> 
> 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)
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users