Databases
StackOverflow2013
Postsdbo
POPostgres discrepency in trigger execution speed?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPostgres discrepency in trigger execution speed?
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USErwin Brandstetter
    1. USMark
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPostgres discrepency in trigger execution speed?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSo you are not using foreign keys, but emulating a fk with a trigger trigger? If so why?
      singulars
      1. POPostgres discrepency in trigger execution speed?
      1. USKuberchaun
    2. COFor good habit change your * to something like 'X'. We flag all *'s in code reviews and wouldn't let that pass review even if the performance isn't impacted.
      singulars
      1. POPostgres discrepency in trigger execution speed?
      1. USKuberchaun
    3. COCan we see your code with the FOR EACH STATEMENT and the explain it gives?
      singulars
      1. POPostgres discrepency in trigger execution speed?
      1. USKuberchaun
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload