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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
