This is from within SPI_exec:
Nested Loop (cost=0.00..115947.18 rows=1952242 width=25) (actual
time=0.095..6425.291 rows=1952202 loops=1)
-> Index Scan using othertable_level_pkey on othertable
(cost=0.00..9.34 rows=1 width=25) (actual time=0.063..0.067 rows=1
loops=1)
Index Cond: ((level = 0) AND (pkey = '123456'::text))
-> Seq Scan on sometable target (cost=0.00..96415.42 rows=1952242
width=0) (actual time=0.025..2333.743 rows=1952202 loops=1)
Total runtime: 9613.094 ms
Now, there seems to be a stray FROM inside the trigger code, as in:
UPDATE %s SET key = '%s', val = '%s' FROM %s target WHERE ....
thanks to incremental change :-) removing the stray FROM has given:
Index Scan using othertable_level_pri on othertable (cost=0.00..9.34
rows=1 width=25) (actual time=0.109..0.110 rows=1 loops=1) Index Cond: ((level = 0) AND (pkey = '123456'::text))
Total runtime: 0.162 ms
which is exactly what I got from the psql version, which did not have
the stray FROM part.
so, it turns out the sql was not identical inside the trigger -- my mistake...
The interesting thing is SQL_exec(sql,1) resulted in a different
execution plan while the stray FROM was still in place.
regards, Michael