On Fri, Nov 26, 2004 at 02:00:48PM +0100, Pierre-Frédéric Caillaud wrote:
>
> It seems this issue has been mentionned several times lately...
> I'd propose something to do to avoid it :
>
> * When TRUNCAT'ing a table :
> - the row-count is reset to 0 (of course !)
> - however, the column stats are kept, on the basis that the
> data which will be inserted later in the table will most likely have at
Currently, TRUNCATE doesn't affect the statistics, nor does it set the
row count to zero. Which means that when new data is inserted it will
plan as if the table were still full. So no change required here.
> * When INSERT'ing into a table :
> The OP's problem was that the query is planned as if the table was
> almost empty (hence seq scan), well it is in fact empty, but many rows are
> inserted. Of course, the query can't be replanned in the middle of its
> execution (although that'd be a nice feature to ad on the withlist for
> postgres version 2020)...
The size of the table you are inserting to is irrelevent to the
planner. All that matters is where the data is coming from. Think about
it, UPDATE, DELETE and INSERT are just fancy wrappers around SELECT to
do something special with the rows that are finally selected. Which you
use has very little effect on the plan finally used. I guess as a
special case, DELETE can optimise the fact that no data need be
returned, only a list of rows...
The original user's problem stemmed from the fact they were running
ANALYZE on an empty table, *that* was killing the statistics. Stop
doing that and the statistics will remain as if the table was full. The
VACUUM will set the rowcount back to zero, but that's it.
Seems PostgreSQL is already doing what you suggest anyway...
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.