4. Isn't ANALYZE on a totally empty table really a special case? The presumption should be that the table will not remain empty. To optimize the performance assuming that there will be zero (or close to zero) rows seems somewhat pointless. However, there are valid reasons why a table might be empty at the moment in time when the ANALYZE is run. (In our case, we use "work" tables that get cleared at the end of an application process.) And, as mentioned above, it is easier to VACUUM ANALYZE an entire database than it is to list tables individually.
Well, for instance I have a few tables which contain just a few rows, for instance a list of states in which an object in another table may be, or a list of tax rates... for these kinds of tables with like, 10 rows, or just a few pages, you don't want index scans, so VACUUM and ANALYZE are doing their job.
If you were going to insert 5 rows in an empty table, you would also want this behaviour. The problems start when you make a large INSERT in an empty or almost empty table.
So, how to solve your problem without slowing the other requests (ie. selecting and inserting a few rows into a very small table) ?
Nobody responded to my suggestion that the planner take into account the number of rows to be inserted into the table in its plan... so I'll repost it :
- INSERT ... SELECT :
Planner has an estimate of how many rows the SELECT will yield. So it could plan the queries involving SELECTs on the target table (like, UNIQUE checks et al) using the number of rows in the table + number of rows to be inserted. This solves your problem.
Problems with this approach :
- This only gives a number of rows, not more precise statistics
It's the only information available so why not use it ? And it's enough to solve the OP's problem.
- Can get recursive
What if there is a self-join ? I guess, just fall back to the previous behaviour...
- Does not work for COPY
argument : COPY should act like it's going to insert many rows. Most of the time, that's how it's used.
- When the estimated number of rows to insert is imprecise
(for instance a SELECT with UNION's or DISTINCT or a huge join), the outcome would be incertain.
What do you think ?
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
