Re: performance - triggers, row existence etc.

tv(at)fuzzy(dot)cz wrote:

>Hello,
>
>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.
>
>
>
This is actually true for any command. If you just use \timing and not
explain analyze, you will see that the first time is usually
significantly longer than the rest.

It's because the tables you are using are being cached in RAM (by the OS
& by postgres).
It's not a planning difference, it's a bulk data cache difference.

When and how long is dependent on how much RAM you have, and how much of
the database you are using.

> 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).
>
>
Connection specific????
If you were doing PREPARE myquery AS SELECT ...; Then myquery would only
exist for that connection. And cursors & temp tables are only for the
given connection.
But otherwise I don't think the connection matters.

>2) Is there some (performance) difference between BEFORE and AFTER
> triggers? I believe there's no measurable difference.
>
>
I don't know that there is a performance difference, but there is a
semantic one. If you are trying to (potentially) prevent the row from
being inserted you must do that BEFORE, since the row doesn't exist yet.
If you are trying to update a foreign key reference to the new object,
you must do that AFTER, so that the row exists to reference.

>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?
>
>
>
SELECT ... WHERE EXISTS ...;
I'm not sure what you are trying to do, but this makes a good joined
command.

SELECT what_I_want FROM table WHERE EXISTS (SELECT what_I_need FROM
othertable);

In general, though, SELECT WHERE LIMIT 1 is about as fast as you can get.

>Thanks
>t.v.
>
>
John
=:->