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 <t...@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 >
