pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance - triggers, row existence etc.
3+ messages / 3 participants
[nested] [flat]
* performance - triggers, row existence etc.
@ 2005-04-10 04:36 tv@fuzzy.cz
2005-04-10 13:09 ` Re: performance - triggers, row existence etc. John A Meinel <john@arbash-meinel.com>
0 siblings, 1 reply; 3+ messages in thread
From: tv@fuzzy.cz @ 2005-04-10 04:36 UTC (permalink / raw)
To: pgsql-performance
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.
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
....
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?
Thanks
t.v.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: performance - triggers, row existence etc.
2005-04-10 04:36 performance - triggers, row existence etc. tv@fuzzy.cz
@ 2005-04-10 13:09 ` John A Meinel <john@arbash-meinel.com>
0 siblings, 0 replies; 3+ messages in thread
From: John A Meinel @ 2005-04-10 13:09 UTC (permalink / raw)
To: tv@fuzzy.cz; +Cc: pgsql-performance
tv@fuzzy.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
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: performance - triggers, row existence etc.
@ 2005-04-11 12:59 Tambet Matiisen <t.matiisen@aprote.ee>
0 siblings, 0 replies; 3+ messages in thread
From: Tambet Matiisen @ 2005-04-11 12:59 UTC (permalink / raw)
To: tv@fuzzy.cz; pgsql-performance
...
>
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-04-11 12:59 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-10 04:36 performance - triggers, row existence etc. tv@fuzzy.cz
2005-04-10 13:09 ` John A Meinel <john@arbash-meinel.com>
2005-04-11 12:59 Re: performance - triggers, row existence etc. Tambet Matiisen <t.matiisen@aprote.ee>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox