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
[GENERAL] pg temp tables
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"? Cheers Anton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
