Thread: Performance of the temporary table creation and use.
Hi, A suggestion: I made a test creating one database specific through pgAdminIII.. I created database test. I created the table tb1 into test. CREATE temporary TABLE tb1( campo1 int2 NOT NULL )WITH OIDS; Insert into tb1 values(1); The PostgreSQL creates a temporary schema pg_temp_(1-n). it physically places the table in this temporary schema and with a OID inside of the structure base/OID_do_database. Although to have changed they sort_mem/work_mem it for 1 MB, it did not use this area in available memory for the connection to make the creation of the temporary table. It has enough memory so that it was used. It would not be the case of beyond create index, create temporary table also to use this available resource? The performance would be another one in environments with much competition., not? This value of sort_mem/work_mem is configured and not used. Regards, Luiz Gonzaga da Mata
Luiz Gonzaga da Mata <gonzaga@pbh.gov.br> writes:
> Although to have changed they sort_mem/work_mem it for 1 MB, it did not
> use this area in available memory for the connection to make the
> creation of the temporary table.
Why would you expect it to, and why would you think there is any
advantage? A small, short-lived temp table will probably never actually
be spilled to disk by the kernel (we never fsync them) so the actual
performance improvement would be minimal.
regards, tom lane
If you store the temp tables in RAM, how will you preserve the ACID property on power failure (without using write-through which would negate the speed advantage)? The temp tables may be participating in a transaction. -----Original Message----- From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Luiz Gonzaga da Mata Sent: Tuesday, January 25, 2005 2:18 PM To: pgsql-hackers@postgresql.org Subject: [HACKERS] Performance of the temporary table creation and use. Hi, A suggestion: I made a test creating one database specific through pgAdminIII.. I created database test. I created the table tb1 into test. CREATE temporary TABLE tb1( campo1 int2 NOT NULL )WITH OIDS; Insert into tb1 values(1); The PostgreSQL creates a temporary schema pg_temp_(1-n). it physically places the table in this temporary schema and with a OID inside of the structure base/OID_do_database. Although to have changed they sort_mem/work_mem it for 1 MB, it did not use this area in available memory for the connection to make the creation of the temporary table. It has enough memory so that it was used. It would not be the case of beyond create index, create temporary table also to use this available resource? The performance would be another one in environments with much competition., not? This value of sort_mem/work_mem is configured and not used. Regards, Luiz Gonzaga da Mata ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
On Tue, Jan 25, 2005 at 03:29:55PM -0800, Dann Corbit wrote: > If you store the temp tables in RAM, how will you preserve the ACID > property on power failure (without using write-through which would > negate the speed advantage)? > > The temp tables may be participating in a transaction. Temp tables are only visible inside a single session. They are inherently not durable. -Mike Adler
Tom Lane escreveu: >Luiz Gonzaga da Mata <gonzaga@pbh.gov.br> writes: > > >> Although to have changed they sort_mem/work_mem it for 1 MB, it did not >>use this area in available memory for the connection to make the >>creation of the temporary table. >> >> > >Why would you expect it to, and why would you think there is any >advantage? A small, short-lived temp table will probably never actually >be spilled to disk by the kernel (we never fsync them) so the actual >performance improvement would be minimal. > > > It can be that kernel not writing physically in disk, but can be also that write. If the use to order by, distinct, creating index and other temporary resources is to greater then it sort_mem/work_mem per processes, the creating resource in disk can be used. If to create a concept of work_mem_pool internally, we could optimize the resources of SO X SGDB and and the use most rational of resource.. For the administrator of the operational system and the administrator of the SGDB that is very important. Work_mem_pool = (number of connections->postgresql.conf) X (work_mem->postgresql.conf). If *memory real used* for all the user processes will be minor who work_mem_pool, would use more resources in memory of that simply the value of work_mem individual. The general behavior of the PostgreSQL would be very better. regards, Luiz Gonzaga da Mata