Re: [GENERAL] pg temp tables
On 06/03/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Anton Melser" <[EMAIL PROTECTED]> writes: > Thanks for your reply. I am managing a db that has some export scripts > that don't do a drop/create, but rather a delete from at the start of > the proc (6 or 7 tables used for this, and only this). Now given that > there is no vacuuming at all going on - this is clearly suboptimal but > in the general case is this better/worse than using temporary tables? Delete all rows, you mean? Have you considered TRUNCATE? Hi, ... I have considered lots of things - but I didn't write the scripts! Now that you mention it, I do remember that truncate is much better than delete from mytable; That is not what they wrote but hey. But even then, what are the advantages/disadvantages of temp tables? Is there a document somewhere I can consult which will give me the lowdown on permanent (but temporary) versus temporary tables in pg? Cheers Anton ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg temp tables
"Anton Melser" <[EMAIL PROTECTED]> writes: > Thanks for your reply. I am managing a db that has some export scripts > that don't do a drop/create, but rather a delete from at the start of > the proc (6 or 7 tables used for this, and only this). Now given that > there is no vacuuming at all going on - this is clearly suboptimal but > in the general case is this better/worse than using temporary tables? Delete all rows, you mean? Have you considered TRUNCATE? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg temp tables
On 06/03/07, Robert Treat <[EMAIL PROTECTED]> wrote: On Saturday 03 March 2007 10:33, Anton Melser wrote: > Hi, > I have been going around telling everyone that there is no point using > physical tables in postgres for temporary storage within a procedure. > Why bother bothering the system with something which is only used in > one procedure I said to myself... I have just learnt that with MS Sql > Server, this is not the case, and that there are locks on some system > table and temp tables eat up memory and lots of other unfortunate > things. Can someone give me a 101 on temp table considerations? Or > rather give me "the good link"? The main issue against using temp tables involve bloat of some of the system catalogs, but it's no worse than doing create/drop cycles with standard tables, and better because they don't suffer as much i/o load. Thanks for your reply. I am managing a db that has some export scripts that don't do a drop/create, but rather a delete from at the start of the proc (6 or 7 tables used for this, and only this). Now given that there is no vacuuming at all going on - this is clearly suboptimal but in the general case is this better/worse than using temporary tables? Thanks again, Anton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pg temp tables
On Saturday 03 March 2007 10:33, Anton Melser wrote:
> Hi,
> I have been going around telling everyone that there is no point using
> physical tables in postgres for temporary storage within a procedure.
> Why bother bothering the system with something which is only used in
> one procedure I said to myself... I have just learnt that with MS Sql
> Server, this is not the case, and that there are locks on some system
> table and temp tables eat up memory and lots of other unfortunate
> things. Can someone give me a 101 on temp table considerations? Or
> rather give me "the good link"?
The main issue against using temp tables involve bloat of some of the system
catalogs, but it's no worse than doing create/drop cycles with standard
tables, and better because they don't suffer as much i/o load.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: explain analyze is your friend
