So, a related question, since we have dozens of temp tables and a lot of code, is there a way to look up what temp tables are being created by the current session, so I can do a VACUUM or ANALYZE on all of them in bulk? I know I can inspect pg_temp_* schema, but how to figure out which one is from the current session?
Any table in pg_temp schema would belong to the current session. Technically the schema has some number appended on the end, but to the creating connection of those temp tables, it is just pg_temp. Probably you should be analyzing the temp tables immediately after insert/update/delete, OR just before first use. As others have said, you have no stats of most common values or historgrams or any other info unless you analyze. If your temp tables have many columns which are only selected and not used for joins or ordering, then you may not need stats on those columns and could save some cycles by specifying the columns, eg. analyze pg_temp.temp_table_name( join_field1, join_field2, order_by_field );
