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 <[EMAIL PROTECTED]> 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.
pgptXWeppH5rj.pgp
Description: PGP signature
