X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 23AC153CBC for ; Sun, 10 Apr 2005 05:37:23 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23837-07 for ; Sun, 10 Apr 2005 04:37:00 +0000 (GMT) Received: from zeus.gigaweb.cz (zeus.gigaweb.cz [81.0.236.19]) by svr1.postgresql.org (Postfix) with SMTP id 3ACA0539D3 for ; Sun, 10 Apr 2005 05:36:57 +0100 (BST) Received: (qmail 6148 invoked from network); 10 Apr 2005 04:36:56 -0000 Received: from unknown (HELO localhost) (81.0.236.19) by 0 with SMTP; 10 Apr 2005 04:36:56 -0000 Received: from pha-84-242-127-3.nat.karneval.cz (pha-84-242-127-3.nat.karneval.cz [84.242.127.3]) by email.gigaweb.cz (IMP) with HTTP for ; Sun, 10 Apr 2005 06:36:56 +0200 Message-ID: <1113107816.4258ad687c034@email.gigaweb.cz> Date: Sun, 10 Apr 2005 06:36:56 +0200 From: tv@fuzzy.cz To: pgsql-performance@postgresql.org Subject: performance - triggers, row existence etc. MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.2.3 X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200504/190 X-Sequence-Number: 11587 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.