Re: How to write a function that manipulates a set of results

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Ashley Moran wrote:
>> Also I'm unsure how the use of temporary tables will affect the
>> running of the query. This function will be called up to 400 times
>> by a wrapping function that needs to complete quickly (relatively -
>> the calculations need to return in a few seconds). I haven't had
>> chance to read much about PG temp tables yet, so I don't know how the
>> implementation works. Are small tables stored in RAM, or does
>> creating a temporary table always force a disk write?

> Temp tables stay in RAM until they are bigger than temp_buffers. If you
> need them to be big and quick, maybe it would be appropriate to use
> indexes (note these count towards temp_buffers), ANALYZE, etc.

You do need to realize that creation of a temp table involves making
entries in the system catalogs. If you can set it up so that you reuse
the same temp table(s) for the life of a connection, you'll save a lot
of thrashing and need for catalog vacuuming (the ON COMMIT DELETE ROWS
option for temp tables might help here). Other than that gotcha, they
should be pretty efficient.

regards, tom lane