pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: tv@fuzzy.cz
To: pgsql-performance@postgresql.org
Subject: performance - triggers, row existence etc.
Date: Sun, 10 Apr 2005 06:36:56 +0200
Message-ID: <1113107816.4258ad687c034@email.gigaweb.cz> (raw)

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.



view thread (3+ messages)  latest in thread

Message-ID: <1113107816.4258ad687c034@email.gigaweb.cz>
Permalink:  ../1113107816.4258ad687c034@email.gigaweb.cz/
Also on:    postgresql.org/message-id/1113107816.4258ad687c034@email.gigaweb.cz

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tv@fuzzy.cz
  Subject: Re: performance - triggers, row existence etc.
  In-Reply-To: <1113107816.4258ad687c034@email.gigaweb.cz>
  References: <1113107816.4258ad687c034@email.gigaweb.cz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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