Re: Performance with temporary table

Well instead of creating a temp table everytime i just created a
permanant table and insert the data into it everytime and truncate it.
I created indexes on this permanent table too. This did improve the
performance to some extent.

Does using permanant tables also bloat the catalog or hinder the performance?

Thanks
Samantha

On 4/8/08, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> samantha mahindrakar escribió:
>
> > So the program necessarily creates a temporary table evrytime it has
> > to correct a record. However this table is dropeed after each record
> > is corrected.
>
> Perhaps it would be better to truncate the temp table instead.
>
> > Iam not sure if i can use a cursor to replicate the functionality of
> > the temp table. Is the performance bad because of the creation and
> > deletion of the temp table?
>
> Yes -- if you create/drop thousands of temp tables (or create/drop the
> same temp table thousands of time), the resulting catalog bloat is
> likely to hinder performance. Perhaps autovacuum should be at work here
> (and if not you can solve the issue with manual vacuums to the system
> catalogs), but even then it is at best unnecessary.
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>