Hi Tom,
the only thing I can tell from EXPLAIN ANALYZE is how long the trigger took
Index Scan using some_pkey on sometable (cost=0.00..8.58 rows=1 width=253) (actual time=0.046..0.050 rows=1 loops=1)
Index Cond: (pkey = 123456)
Trigger so_and_so_on_change: time=62.309 calls=1
running an equivalent query in psql, I get:
Index Scan using other_level_pri on othertable (cost=0.00..8.99 rows=1 width=24) (actual time=0.076..0.085 rows=1 loops=1)
Index Cond: ((level = 0) AND (pkey = '123456'::text))
now, the 62ms trigger execution of the fist statement used to be ~2s
The trigger updates a helper table every time a record is inserted/updated/deleted in the original table. So, SPI_exec calls an INSERT/UPDATE/DELETE operation, affecting exactly one record in the second table. I don't retrieve the results of the query, I just use the return code to raise errors if something goes wrong.
The trigger code is part of a data diffing toolkit I am hoping to release soon.
regards, Michael
2009/9/2 Tom Lane
<tgl@sss.pgh.pa.us>With no details that's an unanswerable question.
SPI_exec doesn't appear to consider the count while forming the query
plan, which was my first thought. But I have seen queries in which
the first row is returned quickly but searching for additional rows
takes a long time, even if there aren't any additional rows. It's
not clear though why that wouldn't apply to hand execution. Have
you tried comparing EXPLAIN ANALYZE outputs?
regards, tom lane