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

[rm_postgis at cheapcomplexdevices.com]

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 |