X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 45B17D1B43E for ; Mon, 1 Dec 2003 14:01:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 03769-09 for ; Mon, 1 Dec 2003 10:00:58 -0400 (AST) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by svr1.postgresql.org (Postfix) with ESMTP id 046E8D1C941 for ; Mon, 1 Dec 2003 10:00:55 -0400 (AST) Received: from [10.1.2.130] (helo=dba2) by mail.libertyrms.com with esmtp (Exim 3.22 #3 (Debian)) id 1AQobi-0005yQ-00 for ; Mon, 01 Dec 2003 09:00:58 -0500 Received: by dba2 (Postfix, from userid 1019) id 667C8CC8A; Mon, 1 Dec 2003 09:00:58 -0500 (EST) Date: Mon, 1 Dec 2003 09:00:58 -0500 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: Various Questions Message-ID: <20031201140058.GA4107@libertyrms.info> Mail-Followup-To: Andrew Sullivan , pgsql-performance@postgresql.org References: <924548300.20031201140750@evilazrael.de> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <924548300.20031201140750@evilazrael.de> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/5 X-Sequence-Number: 4865 On Mon, Dec 01, 2003 at 02:07:50PM +0100, Evil Azrael wrote: > 1) I have a transaction during which no data was modified, does it > make a difference whether i send COMMIT or ROLLBACK? The effect is the > same, but whatīs about the speed? It makes no difference. > 2) Is there any general rule when the GEQO will start using an index? > Does he consider the number of tuples in the table or the number of > data pages? Or is it even more complex even if you donīt tweak the > cost setting for the GEQO? GEQO is not what causes indexscans. You're thinking of the planner/optimiser. Generally, the optimiser decides what the optimum plan is to deliver a query. This involves a complicated set of rules. The real important question is, "Am I really getting the fastest plan?" You can find out that with EXPLAIN ANALYSE. If you want to know more about what makes a good plan, I'd start by reading the docs, and then by reading the comments in the source code. > 3) Makes it sense to add a index to a table used for logging? I mean > the table can grow rather large due to many INSERTs, but is also > seldom queried. Does the index slowdown noticable INSERTs? It does, but you might find that it's worth it. If it is seldom queried, but you really need the results and the result set is a small % of the table, then you're probably wise to pay the cost of the index at insert, update, and VACUUM because doing a seqscan on a large table to get one or two rows will destroy all your buffers. > 4) Temporary tables will always be rather slow as they canīt gain from > ANALYZE runs, correct? No, you can ANALYSE them yourself. Of course, you'll need an index unless you plan to read the whole table. Note that, if you use temp tables a lot, you need to be sure to vacuum at least pg_class and pg_attribute more frequently than you might have thought. A -- ---- Andrew Sullivan 204-4141 Yonge Street Afilias Canada Toronto, Ontario Canada M2P 2A8 +1 416 646 3304 x110