Thread: Various Questions
Hi! I have 4 question which probably someone can answer. 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? 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? 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? 4) Temporary tables will always be rather slow as they can´t gain from ANALYZE runs, correct? Thanx in advance for any answer Christoph Nelles -- Mit freundlichen Grüssen Evil Azrael mailto:evilazrael@evilazrael.de
On Monday 01 December 2003 18:37, 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 should not matter. Both commit and rollback should take same amount of time.. > 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? I thought GEQO was triggered by numebr of join clauses. That is what GEQO cost indicates. It is not triggered by number of tuples in any table etc. But correct me if I am wrong. > 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? Yes. It does make a lot of difference. If the table is very seldom queried, you can probably create the index before querying and drop it later. However even this will cost a seq. scan of table and can be heavy on performance.. Take your pick Shridhar
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