Re: BUG #1541: Unusually long INSERT times after fresh clean/CREATE TABLES

On 2005-03-14, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> The 8.0 planner is intentionally sensitive to the current actual
> physical sizes of tables. It sounds like you've managed to get it to
> plan something on the assumption that the tables are tiny and keep
> using that plan after they aren't tiny any more. The old planner had
> the same kind of issue but it was far easier to hit, so "revert that
> change" isn't an answer that I'm particularly interested in.

On the irc channel we were just investigating a very similar case to this
(which will show up as a bug report here in due course, though not from me).

It turns out that the scenario above is trivial to hit in 8.0 using
referential constraints; RI triggers cache their plans, and on 8.0 the RI
query is planned as a seqscan if the tables are freshly created. (On 7.4
the plan is an index scan, thanks to the default 1000 rows / 10 pages stats.)

What this basically means is that you can't do even a modest size insert
of new data into a fresh schema without having to either trick the planner
(e.g. enable_seqscan=false before the first insert) or defer the addition of
constraints until after the data load (which can be quite inconvenient in
some cases).

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services