On 06/22/2005 04:39:00 PM, Tom Lane wrote:
David Mitchell <david.mitchell@xxxxxxxxxxx> 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@xxxxxxxx>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
