Re: performance - triggers, row existence etc.

...
>
> 2) Is there some (performance) difference between BEFORE and AFTER
> triggers? I believe there's no measurable difference.
>

BEFORE triggers might be faster, because you get a chance to reject the
record before it is inserted into table. Common practice is to put
validity checks into BEFORE triggers and updates of other tables into
AFTER triggers. See also
http://archives.postgresql.org/pgsql-sql/2005-04/msg00088.php.

> 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
> ....
> END IF;
>
> 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?
>

You could save one temporary variable by using PERFORM:

PERFORM 1 FROM ... JOIN ... WHERE ... LIMIT 1;
IF NOT FOUND THEN
...
END IF;

You might want to consider, if you need FOR UPDATE in those queries, so
that the referenced row maintains it's state until the end of
transaction. BTW, foreign keys weren't enough?

Tambet