pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestions about temporary tables and performance
2+ messages / 2 participants
[nested] [flat]
* Questions about temporary tables and performance
@ 2005-07-16 21:42 Steven Rosenstein <srosenst@us.ibm.com>
0 siblings, 1 reply; 2+ messages in thread
From: Steven Rosenstein @ 2005-07-16 21:42 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Questions about temporary tables and performance
@ 2005-07-17 04:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Steven Rosenstein <srosenst@us.ibm.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-07-17 04:44 UTC (permalink / raw)
To: Steven Rosenstein <srosenst@us.ibm.com>; +Cc: pgsql-performance
Steven Rosenstein <srosenst@us.ibm.com> writes:
> Are there any performance issues or considerations associated with using a
> temporary table in this scenario?
It's probably worthwhile to ANALYZE the temp table after it's filled,
before you start joining to it.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-07-17 04:44 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-16 21:42 Questions about temporary tables and performance Steven Rosenstein <srosenst@us.ibm.com>
2005-07-17 04:44 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox