Re: Vacuum advice

On 06/22/2005 04:39:00 PM, Tom Lane wrote:
> David Mitchell <david(dot)mitchell(at)telogis(dot)com> writes:
> > However, occasionally we need to import data, and this involves
> > inserting several million rows into a table, but this just
> *cripples*
> > postgres. After the import has been running for a while, simple
> selects
> > take a long time, and strangely, the query planner starts using a
> > sequential scan on a table with millions of items in it. Surely
> inserts
> > shouldn't be doing this? Should I be running a vacuum during the
> import?
>
> PG 8.0 and up should handle this a bit more nicely. Before 8.0 the
> planner looked only at the table size recorded in pg_catalog (the
> relpages and reltuples fields) and so of course it wouldn't react to a
> still-in-progress mass insert. Now it looks at the current actual
> physical table size, and so it should be willing to switch to
> indexscan
> if needed before the import completes.

I've made a habit of disconnecting, analyzing and re-connecting
every so often on mass data loading because any stored
procedures I'm using for the load, including triggers, will not have
their query plans updated to account for the new data.
It's a pain, so I figured I'd ask whether I'm missing
something and this is somehow unnecessary.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein