I'm just in the middle of performance tunning of our database running
on PostgreSQL, and I've several questions (I've searched the online
docs, but without success).
1) When I first use the EXPLAIN ANALYZE command, the time is much
larger than in case of subsequent invocations of EXPLAIN ANALYZE.
I suppose the plan prepared during the first invocation is cached
somewhere, but I'm not sure where and for how long.
I suppose the execution plans are connection specific, but
I'm not sure whether this holds for the sql queries inside the
triggers too. I've done some testing but the things are somehow
more difficult thanks to persistent links (the commands will
be executed from PHP).
2) Is there some (performance) difference between BEFORE and AFTER
triggers? I believe there's no measurable difference.
3) Vast majority of SQL commands inside the trigger checks whether there
exists a row that suits some conditions (same IP, visitor ID etc.)
Currently I do this by
SELECT INTO tmp id FROM ... JOIN ... WHERE ... LIMIT 1
IF NOT FOUND THEN
and so on. I believe this is fast and low-cost solution (compared
to the COUNT(*) way I've used before), but is there some even better
(faster) way to check row existence?