Thread

1. Re: drop tempoary table VERY slow

   Sam Liddicott <sam.liddicott@ananova.com> — 2002-06-05T13:54:46Z

   
   > -----Original Message-----
   > From: Andrew McMillan [mailto:andrew@catalyst.net.nz]
   > Sent: 05 June 2002 12:58
   > To: Sam Liddicott
   > Cc: pgsql-bugs@postgresql.org
   > Subject: RE: [BUGS] drop tempoary table VERY slow
   > 
   > Interesting.  Those are pretty long times to take for a 
   > vacuum on those
   > tables - if you are using 7.2.x have you tried more frequent vacuum? 
   > Perhaps with a vacuum full each night?
   
   Hmmm.
   
   > I think that the aborting transaction approach, since it 
   > works, is most
   > likely to be your best bet in general, however.
   > 
   > It would be interesting to see the 'vacuum full analyze' 
   > results for the
   > system tables in that DB, although perhaps less interesting while you
   > are running your current solution - maybe a comparison would be
   > worthwhile.
   
   Alas we won't be able to downgrade as it affected the service seriously.
   In doing a full vacuum I notice such errors as:
   
   NOTICE:  Index pg_index_indrelid_index NUMBER OF INDEX' TUPLES (92) IS NOT
   THE SAME AS HEAP' (86). Recreate the index
   
   Hmm.  It's not my index (of course) I'm not sure how to go about re-creating
   it.
   
   Sam
   
   
   
   
   

2. Re: drop tempoary table VERY slow

   Andrew McMillan <andrew@catalyst.net.nz> — 2002-06-06T10:52:06Z

   On Thu, 2002-06-06 at 01:54, Sam Liddicott wrote:
   
   > > 
   > > It would be interesting to see the 'vacuum full analyze' 
   > > results for the
   > > system tables in that DB, although perhaps less interesting while you
   > > are running your current solution - maybe a comparison would be
   > > worthwhile.
   > 
   > Alas we won't be able to downgrade as it affected the service seriously.
   > In doing a full vacuum I notice such errors as:
   > 
   > NOTICE:  Index pg_index_indrelid_index NUMBER OF INDEX' TUPLES (92) IS NOT
   > THE SAME AS HEAP' (86). Recreate the index
   > 
   > Hmm.  It's not my index (of course) I'm not sure how to go about re-creating
   > it.
   
   To reindex the system tables you need to shut down your database, then
   run a standalone postgres backend:
   
   postgres -O -P <dbname>
   
   Inside that you then:
   
   REINDEX DATABASE <dbname>;
   
   Once it is done, quit and restart your database.
   
   Regards,
   					Andrew.
   -- 
   --------------------------------------------------------------------
   Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
   WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
   DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
          Are you enrolled at http://schoolreunions.co.nz/ yet?