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 219E753650 for ; Sun, 10 Apr 2005 14:10:04 +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 97467-04 for ; Sun, 10 Apr 2005 13:09:52 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 78D2C53657 for ; Sun, 10 Apr 2005 14:09:51 +0100 (BST) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j3AD9qGR016159; (envelope-from ) Sun, 10 Apr 2005 08:09:52 -0500 (CDT) Received: from [192.168.1.11] (12-215-118-172.client.mchsi.com [12.215.118.172]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j3AD9ots019699 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Sun, 10 Apr 2005 08:09:51 -0500 (CDT) Message-ID: <4259259A.7090504@arbash-meinel.com> Date: Sun, 10 Apr 2005 08:09:46 -0500 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: tv@fuzzy.cz Cc: pgsql-performance@postgresql.org Subject: Re: performance - triggers, row existence etc. References: <1113107816.4258ad687c034@email.gigaweb.cz> In-Reply-To: <1113107816.4258ad687c034@email.gigaweb.cz> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig1331E127074036E0F475E918" X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/193 X-Sequence-Number: 11590 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig1331E127074036E0F475E918 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 =:-> --------------enig1331E127074036E0F475E918 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.6 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCWSWeJdeBCYSNAAMRAsNgAKDP60wU78IHtYegCYeb8AQDASXsrgCghKUL H0+IvAyjtALOdBtPMWsLI7c= =a+Jj -----END PGP SIGNATURE----- --------------enig1331E127074036E0F475E918--