Re: Slow Inserts on 1 table?

Dan Armbrust wrote:
> Dan Armbrust wrote:
>
>> I have one particular insert query that is running orders of magnitude
>> slower than other insert queries, and I cannot understand why.
>> For example, Inserts into "conceptProperty" (detailed below) are at
>> least 5 times faster than inserts into "conceptPropertyMultiAttributes".

> Well, I now have a further hunch on why the inserts are so slow on 1
> table. Most of the time, when I am doing bulk inserts, I am starting
> with an empty database. My insertion program creates the tables,
> indexes and foreign keys.
> The problem seems to be the foreign key - PostgreSQL is apparently being
> to stupid to use the indexes while loading and checking the foreign key
> between two large tables - my guess is because analyze has not been run
> yet, so it thinks all of the tables are size 0.

If you haven't analysed them since creation, it should think size=1000,
which is a safety measure to reduce this sort of problem.

> If I let it run for a
> while, then kill the load process, run Analyze, empty the tables, and
> then restart, things perform fine. But that is kind of a ridiculous
> sequence to have to use to load a database.
> Why can't postgres compile some rough statistics on tables without
> running analyze? Seems that it would be pretty easy to keep track of
> the number of inserts/deletions that have taken place since the last
> Analyze execution... It may not be the exact right number, but it would
> certainly be smarter than continuing to assume that the tables are size
> 0, even though it has been doing constant inserts on the tables in
> question....

Yep, but it would have to do it all the time. That's overhead on every
query.

> I have already had to disable sequential scans, since the planner is
> almost _always_ wrong in deciding whether or not to use an index.

Then either your stats are badly out, or your other configuration
settings are.

> I put
> the indexes on the columns I choose for a reason - it is because I KNOW
> the index read will ALWAYS be faster since I designed the indexes for
> the queries I am running. But it still must be doing a sequential scan
> on these inserts...

What, ALWAYS faster, even for the first FK check when there's only one
row in the target table and that's cached?

If you're really in a hurry doing your bulk loads:
1. Use COPY.
2. Drop/restore the foreign-key constraints before/after.
That will be hugely faster than INSERTs, although it's not always an
applicable solution.
--
Richard Huxton
Archonet Ltd