Thread

1. Various Questions

   Evil Azrael <evilazrael@evilazrael.de> — 2003-12-01T13:07:50Z

   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
   
   
   

2. Re: Various Questions

   Shridhar Daithankar <shridhar_daithankar@myrealbox.com> — 2003-12-01T13:26:03Z

   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
   
   
   

3. Re: Various Questions

   Andrew Sullivan <andrew@libertyrms.info> — 2003-12-01T14:00:58Z

   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