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

[strk]

I'm do not know much about join types, but try substituting

	setSRID( 'BOX3D(-121.586865184167 38.4277618656666,-121.336132412548 38.6954249341005)'::BOX3D,find_srid('','cpal_events','the_geom'))

With:

	'SRID=<CPALSRID>;BOX3D(-121.586865184167 38.4277618656666,-121.336132412548 38.6954249341005)'

You'll have to know <SPALSRID> in advance, but that should give the
estimator usable input. I think the estimator result is a default one,
because it won't get a constant element in the && operation.

--strk;

On Tue, Jun 15, 2004 at 03:36:45AM -0700, rm_postgis at cheapcomplexdevices.com wrote:
> On Tue, 15 Jun 2004, strk wrote:
> > 
> > Report explain analyze select ... results if you want more help.
> > Also try modifying the query. The selectivity estimator only works
> > in the presence of a constant geometry in one side of the && operator.
> 
> 
> I have explain analyze results here.  Table definitions "\d tablename" 
> follow the "explain analyze select" results". The line that makes me think 
> it's a bad optimizer guess is the "rows=" part of this line:
> "Nested Loop  (cost=0.00..218.01 rows=1 width=546) (actual 
> time=2555.501..202056.918 rows=6357 loops=1)"
> 
>   Ron
> 
> [explain analyze results follow]
> 
>   ==============================================================
> 
> ronmap=# set enable_nestloop = true;
> SET
> 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'));
> 
>                                                                     QUERY PLAN                                                                     
> ---------------------------------------------------------------------------------------------------------------------------------------------------
>  Nested Loop  (cost=0.00..218.01 rows=1 width=546) (actual time=2555.501..202056.918 rows=6357 loops=1)
>    Join Filter: ("outer".nextval = "inner".eventid)
>    ->  Index Scan using cpal_event_summary__gist on cpal_events  (cost=0.00..6.01 rows=1 width=503) (actual time=0.100..261.886 rows=9570 loops=1)
>          Index Cond: (the_geom && 'SRID=-1;BOX3D(-121.586865184167 38.4277618656666 0,-121.336132412548 38.6954249341005 0)'::geometry)
>    ->  Seq Scan on cpal_selected_events  (cost=0.00..132.50 rows=6360 width=43) (actual time=0.005..12.020 rows=6357 loops=9570)
>          Filter: ("owner" = 'user.23469878.20252'::text)
>  Total runtime: 202079.387 ms
> (7 rows)
> 
> ronmap=# set enable_nestloop = false;
> SET
> 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'));
>                                                                        QUERY PLAN                                                                       
> --------------------------------------------------------------------------------------------------------------------------------------------------------
>  Merge Join  (cost=540.31..572.13 rows=1 width=546) (actual time=161.258..266.926 rows=6357 loops=1)
>    Merge Cond: ("outer".nextval = "inner".eventid)
>    ->  Sort  (cost=6.02..6.03 rows=1 width=503) (actual time=129.153..137.399 rows=9570 loops=1)
>          Sort Key: cpal_events.nextval
>          ->  Index Scan using cpal_event_summary__gist on cpal_events  (cost=0.00..6.01 rows=1 width=503) (actual time=0.103..78.088 rows=9570 loops=1)
>                Index Cond: (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=32.047..37.490 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.074..15.649 rows=6357 loops=1)
>                Filter: ("owner" = 'user.23469878.20252'::text)
>  Total runtime: 281.134 ms
> (11 rows)
> 
> 
> 
> 
> 
> 
> ronmap=# \d cpal_events;
>             Table "public.cpal_events"
>       Column       |       Type       | Modifiers 
> -------------------+------------------+-----------
>  nextval           | bigint           | 
>  the_geom          | geometry         | 
>  [and lots of columns of data]
> Indexes:
>     "cpal_events__oid" unique, btree (oid)
>     "cpal_event_summary__gist" gist (the_geom)
>     "cpal_events__gist" gist (the_geom)
> Check constraints:
>     "$2" CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)
>     "$1" CHECK (srid(the_geom) = -1)
> 
> ronmap=# 
> ronmap=# \d cpal_selected_events;
>  Table "public.cpal_selected_events"
>     Column     |  Type   | Modifiers 
> ---------------+---------+-----------
>  name          | text    | 
>  owner         | text    | 
>  eventid       | integer |       
> 
> 
>