Re: Various Questions

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