[postgis-users] Is my query planner failing me, or vice versa?

[Jessica M Salmon]

Hello all. Following this list for a couple months now, I have been privy
to many tidbits of wisdom. I have a trigger function in plpgsql that is
giving me strange behavior, in that its main query is normally very fast,
but sometimes very, very slow. I am trying identify these slow spots and
optimize them. I was doing an EXECUTE query_string, in hopes that the
planner could itself recognize the unique situation and tailor itself
accordingly. However, it is still using the same query plan each time. I
threw in a FOR debugvar IN EXECUTE 'EXPLAIN ANALYZE ' || query_string LOOP
to find out what's going on, and the results seem to suggest that the
planner is failing me dramatically. The chart below shows 121 runs that
group clearly into seven clusters. The problematic one is that which is
predicted at 26, but actually takes a full minute. Can anyone tell me or
make any guesses on why this is happening?

(Embedded image moved to file: pic22054.jpg)


My trigger function acts on updates to a table, and it is meant to join
different geometries that grow into one another, without being thrown off
by previous observations of the "same" geometry. The geometries represent
observations of burned areas from satellite. The code is below.

CREATE OR REPLACE FUNCTION join_nearby_fires() RETURNS TRIGGER AS '
  DECLARE
    near_fires text;
    near_fire RECORD;
    debugvar RECORD;
    debugvar2 text;
    the_firenum integer;
  BEGIN
    RAISE NOTICE ''                checking...'';
    near_fires := ''SELECT DISTINCT ON (firenum[1]) * from modis_obs_fires
where expand(geomfromewkt(''''SRID=102008;''
      || astext(NEW.surface ) || ''''''),  5000) && surface AND ('''''' ||
NEW.observed || '''''' - observed) <
      ''''10 days'''' AND NOT (firenum[1] = ANY (''''{'' ||
array_to_string(NEW.firenum, '','') || ''}''''))  AND
      distance(geomfromewkt(''''SRID=102008;''
      || astext(NEW.surface) || ''''''), surface) <= 5000 ORDER BY
firenum[1] DESC, observed DESC, source ASC'';
    FOR debugvar IN EXECUTE ''EXPLAIN ANALYZE '' || near_fires LOOP
      debugvar2 := debugvar;
      RAISE NOTICE ''%'', debugvar2;
    END LOOP;
    FOR near_fire IN EXECUTE near_fires LOOP
      RAISE NOTICE ''                    joining...'';
      the_firenum := near_fire.firenum[1];
      IF NOT (the_firenum = ANY (NEW.firenum)) THEN
        NEW.surface := MULTI(geomunion(SnapToGrid(NEW.surface, 0.001),
SnapToGrid(near_fire.surface, 0.001)));
        NEW.firenum := array_append(NEW.firenum, the_firenum);
        IF near_fire.observed = NEW.observed THEN
          IF near_fire.source = NEW.source THEN
            DELETE FROM modis_obs_fires WHERE
equals(surface,near_fire.surface) AND
              observed = near_fire.observed AND source = near_fire.source
AND
              firenum[1] = the_firenum and firenum != NEW.firenum;
          END IF;
        END IF;
      END IF;
      RAISE NOTICE ''                    joined...'';
    END LOOP;
    RAISE NOTICE ''                checked...'';
  RETURN NEW;
  END;
' Language plpgsql;

The query plan always looks about like this, with differing geometries,
costs, and actual times:

Unique  (cost=6.05..6.06 rows=1 width=12288) (actual time=0.576..0.576
rows=0 loops=1)")
  ->  Sort  (cost=6.05..6.06 rows=1 width=12288) (actual time=0.567..0.567
rows=0 loops=1)")
        Sort Key: firenum[1], observed, source")
        ->  Index Scan using perim_gix on modis_obs_fires  (cost=0.00..6.04
rows=1 width=12288) (actual time=0.541..0.541 rows=0 loops=1)")
              Index Cond: ('blah_blah_blah'::geometry && surface)")
              Filter: ((('2005-08-24 12:53:54-06'::timestamp with time zone
- observed) < '10 days'::interval) AND (NOT (firenum[1] = ANY
('{2598}'::integer[]))) AND (distance('blah_blah_blah'::geometry, surface)
<= 5000::double precision))")
 ("Total runtime: 0.696 ms")



Is what I'm seeing here normal? Am I being dumb?

-Meghan
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pic22054.jpg
Type: image/jpeg
Size: 27411 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20051213/0c97bdc5/attachment.jpg>