From: Andrew Sullivan <andrew@libertyrms.info>
To: pgsql-performance@postgresql.org
Subject: Re: Various Questions
Date: Mon, 1 Dec 2003 09:00:58 -0500
Message-ID: <20031201140058.GA4107@libertyrms.info> (raw)
In-Reply-To: <924548300.20031201140750@evilazrael.de>
References: <924548300.20031201140750@evilazrael.de>
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
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
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: andrew@libertyrms.info
Subject: Re: Various Questions
In-Reply-To: <20031201140058.GA4107@libertyrms.info>
References: <924548300.20031201140750@evilazrael.de> <20031201140058.GA4107@libertyrms.info>
* 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