Postgres Version:
7.3.9 and 8.0.1 (different sites use different versions depending on when
they first installed Postgres)
Migration Plans:
All sites on 8.n within the next 6-9 months.
Scenario:
A temporary table is created via a "SELECT blah INTO TEMPORARY TABLE blah
FROM...". The SELECT query is composed of a number of joins on small
(thousands of rows) parameter tables. A view is not usable here because
the temporary table SELECT query is constructed on the fly in PHP with JOIN
parameters and WHERE filters that may change from main query set to main
query set.
After the table is created, the key main query JOIN parameter (device ID)
is indexed. The resulting temporary table is at most 3000-4000 small (128
byte) records.
The temporary table is then joined in a series of SELECT queries to other
data tables in the database that contain information associated with the
records in the temporary table. These secondary tables can have tens of
millions of records each. After the queries are executed, the DB
connection is closed and the temporary table and index automatically
deleted.
Are there any performance issues or considerations associated with using a
temporary table in this scenario? Is it worth my trying to develop a
solution that just incorporates all the logic used to create the temporary
table into each of the main queries? How expensive an operation is
temporary table creation and joining?
Thanks in advance for your advice,
--- Steve