Also may I know if excessive use of temporary tables may cause locks?
Usually there are no problems with locks, but there is a problem with system tables bloating. Creating and dropping temp tables is expensive like creating or dropping normal tables.
Dropping a real table requires scanning all of shared_buffers, right? I wouldn't think dropping a temp table requires that kind of heavy operation because it should be stored within separate temp_buffers.
Against other databases, there can be used arrays instead temporary tables. This is significantly more effective.
Can you expand on this point? What do you mean? Like using a values statement rather than temp table? I find that I often need to create a temp table and analyze it to allow the planner to make wise decisions.
