Questions about temporary tables and performance
От | Steven Rosenstein |
---|---|
Тема | Questions about temporary tables and performance |
Дата | |
Msg-id | OF01136A65.6C2D905D-ON85257040.007514DE-85257040.00773BFE@us.ibm.com обсуждение исходный текст |
Ответы |
Re: Questions about temporary tables and performance
|
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: