Re: trigger SPI_exec count argument

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(at)sss(dot)pgh(dot)pa(dot)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
>