Phil Endecott <[EMAIL PROTECTED]> writes:
Does this make sense? I imagine that the temporary table is being added to these tables and then removed again.
Yes, a temp table has the same catalog infrastructure as a regular table, so creation and deletion of a temp table will cause some activity in those catalogs. I thought you were concerned about the data within the temp table, though.
I'm concerned about unnecessary disk activity, whatever its cause. I guessed that it was the temp table contents.
I do have quite a large number of tables in the database; I have one schema per user and of the order of 20 tables per user and 200 users. I can imagine that in a system with fewer tables this would be insignificant, yet in my case it seems to be writing of the order of a megabyte in each 5-second update.
That seems like a lot. How often do you create/delete temp tables?
Only once or twice per 5-sec update period. I agree that it sounds like a lot which makes me think this could all be a red herring; I suspect that there is something else going on as well as this temp table stuff (possibly nothing to do with postgresql). But FYI this is treefic.com, a family tree website. Have a look at, for example, http://treefic.com/treefic/royal92?a=tree_page&root_id=10286&direction=up
The first step in building that diagram is to find the ancestors of the root individual. I have a pl_pgsql function that itteratively finds all of the ancestors, progressively adding them to a temporary table. So it will create, populate, read and then drop one table for each page that it generates. This is reasonably fast; overall speed is not limited by postgres.
What would happen if I were to rollback at the end of the transaction, rather than committing (having made no changes)? Would that eliminate some or all of the catalog writes?
Many thanks for helping me understand this.
Regards,
Phil.
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
