Please ignore my post from earlier today. As strange as it sounds, changing "CREATE TEMP TABLE ... AS" to "CREATE TEMP TABLE ... LIKE" appeared to fix my performance problem because things errored out so quickly (and silently in my test program). After checking the pgsql logs, it became clear to me that you can't use LIKE on a view. Duh.
Moving forward, I have also discovered that our temp table did in fact have a varchar column (no specified limit on varchar). With this in mind, I could easily reproduce the problem on a temp table with one column. So...
Moving forward, I have also discovered that our temp table did in fact have a varchar column (no specified limit on varchar). With this in mind, I could easily reproduce the problem on a temp table with one column. So...
Issue #1:
(I'm assuming there's a reasonable explanation for this.) If I create a temp table with a single varchar column (or text column), do 100 inserts to that table, copy to a permanent table, truncate the temp table and repeat, the time required for the 100 inserts grows almost linearly. Maybe the data is treated as large objects.
Note that if I change the column type to varchar(SOME_LIMIT), integer, timestamptz, interval, etc., performance does not degrade. Also note that if I do not use a temp table (but do use a varchar column), inserts are slower (as expected) but do not degrade over time. So this seems to be specific to temp tables with varchar/text column(s).
Issue #2:
As I said earlier, the temp table is created via:
CREATE TEMP TABLE tmp AS SELECT <column-list> FROM perm LIMIT 0;
where perm is a view defined as follows:
View definition:
SELECT <column-list>
FROM view2
JOIN tbl USING (col1, col2)
WHERE <some-conditions>
UNION ALL
SELECT <column-list>
FROM view3
JOIN tbl USING (col1, col2)
WHERE <some-conditions>;
SELECT <column-list>
FROM view2
JOIN tbl USING (col1, col2)
WHERE <some-conditions>
UNION ALL
SELECT <column-list>
FROM view3
JOIN tbl USING (col1, col2)
WHERE <some-conditions>;
Now the varchar columns that end up in the perm view come from the tbl table, but in tbl, they are defined as varchar(40). Somehow the 40 limit is lost when constructing the view. After a little more testing, I found that this problem only occurs when you are creating a view (
i.e. CREATE TABLE ... AS does not observe this problem) and also that the UNION ALL clause must be present to observe this problem.
This looks like a bug. I know this is Postgres 7.4.6 and I haven't been able to verify with a later version of Postgres, but does this look familiar to anyone?
Steve
