title: Postgres discrepency in trigger execution speed?
body: <p>I have a trigger that executes a function on table insert or update.  It looks like this:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION func_fk_location_area()
  RETURNS "trigger" AS $$
  BEGIN
      IF EXISTS (
          -- there was a row valid in area when location started
          SELECT * FROM location
          WHERE NOT EXISTS (
              SELECT * FROM area
               WHERE area.key=location.key
                 AND area.id=location.area_id
                 AND (  (area.tr_from&lt;=location.tr_from AND area.tr_until&gt;location.tr_from) OR
                        (area.tr_from=location.tr_from AND area.tr_until=location.tr_from)))
      ) OR EXISTS (
          -- there was a row valid in area when location ended
          SELECT * FROM location
          WHERE NOT EXISTS (
              SELECT * FROM area
               WHERE area.key=location.key
                 AND area.id=location.area_id
                 AND (  (area.tr_from&lt;location.tr_until AND area.tr_until&gt;=location.tr_until) OR
                        (area.tr_from=location.tr_until AND area.tr_until=location.tr_until)))
      )
      THEN
          RAISE EXCEPTION 'FK location_area integrity violation.';
      END IF;
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  CREATE TRIGGER trigger_fk_area_location AFTER DELETE OR UPDATE ON area
      FOR EACH ROW EXECUTE PROCEDURE func_fk_location_area();
  CREATE TRIGGER trigger_fk_location_area AFTER INSERT OR UPDATE ON location
      FOR EACH ROW EXECUTE PROCEDURE func_fk_location_area();
  </code></pre>
  
  <p>When I insert a row, it seems to run very slowly.  Using explain analyze I determined that this trigger was taking nearly 400ms to complete.</p>
  
  <pre><code> Result  (cost=0.00..0.03 rows=1 width=0) (actual time=0.026..0.029 rows=1 loops=1)
   Trigger for constraint location_fkey_tr_by: time=0.063 calls=1
   Trigger trigger_fk_location_area: time=361.878 calls=1
   Trigger trigger_update_objects_location: time=355.033 calls=1
   Total runtime: 717.229 ms
  (5 rows)
  </code></pre>
  
  <p>However, if I run the two lots of SQL in the function, they each only take 3 or 4ms to run!</p>
  
  <p>FIRST PART:</p>
  
  <pre><code>mydb=# explain analyze
  mydb-#             SELECT * FROM location
  mydb-#             WHERE NOT EXISTS (
  mydb(#                 SELECT * FROM area
  mydb(#                  WHERE area.key=location.key
  mydb(#                    AND area.id=location.area_id
  mydb(#                    AND (  (area.tr_from&lt;location.tr_until AND area.tr_until&gt;=location.tr_until) OR
  mydb(#                           (area.tr_from=location.tr_until AND area.tr_until=location.tr_until)));
  
   Hash Anti Join  (cost=14.68..146.84 rows=1754 width=126) (actual time=5.512..5.512 rows=0 loops=1)
     Hash Cond: ((location.key = area.key) AND (location.area_id = area.id))
     Join Filter: (((area.tr_from &lt; location.tr_until) AND (area.tr_until &gt;= location.tr_until)) OR ((area.tr_from = location.tr_until) AND (area.tr_until = locat
  ion.tr_until)))
     -&gt;  Seq Scan on location  (cost=0.00..79.91 rows=2391 width=126) (actual time=0.005..1.016 rows=2393 loops=1)
     -&gt;  Hash  (cost=8.87..8.87 rows=387 width=37) (actual time=0.497..0.497 rows=387 loops=1)
           -&gt;  Seq Scan on area  (cost=0.00..8.87 rows=387 width=37) (actual time=0.004..0.250 rows=387 loops=1)
   Total runtime: 5.562 ms
  (7 rows)
  </code></pre>
  
  <p>SECOND PART:</p>
  
  <pre><code>mydb=# explain analyze
  mydb-#             SELECT * FROM location
  mydb-#             WHERE NOT EXISTS (
  mydb(#                 SELECT * FROM area
  mydb(#                  WHERE area.key=location.key
  mydb(#                    AND area.id=location.area_id
  mydb(#                    AND (  (area.tr_from&lt;location.tr_until AND area.tr_until&gt;=location.tr_until) OR
  mydb(#                           (area.tr_from=location.tr_until AND area.tr_until=location.tr_until)));
  
   Hash Anti Join  (cost=14.68..146.84 rows=1754 width=126) (actual time=5.666..5.666 rows=0 loops=1)
     Hash Cond: ((location.key = area.key) AND (location.area_id = area.id))
     Join Filter: (((area.tr_from &lt; location.tr_until) AND (area.tr_until &gt;= location.tr_until)) OR ((area.tr_from = location.tr_until) AND (area.tr_until = locat
  ion.tr_until)))
     -&gt;  Seq Scan on location  (cost=0.00..79.91 rows=2391 width=126) (actual time=0.005..1.072 rows=2393 loops=1)
     -&gt;  Hash  (cost=8.87..8.87 rows=387 width=37) (actual time=0.509..0.509 rows=387 loops=1)
           -&gt;  Seq Scan on area  (cost=0.00..8.87 rows=387 width=37) (actual time=0.007..0.239 rows=387 loops=1)
   Total runtime: 5.725 ms
  (7 rows)
  </code></pre>
  
  <p>This makes no sense to me.</p>
  
  <p>Any thoughts?</p>
  
  <p>Thanks.</p>