Re: Slow Inserts on 1 table?

>
> 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
>
It seems like the query planner goes to great lengths to avoid using
indexes because it might take 5 ms longer to execute an index lookup on
a table with one row.

But then, when the table has 1 million rows, and a full scan takes 3
minutes, and the index scan takes 3 seconds, it has no problem picking
the 3 minute route.
I'll gladly give up the 5 ms in turn for not having to wait 3 minutes,
which is why I disabled the sequential scans.  If I have a small table,
where indexes won't speed things up, I wont build an index on it.

The other factor, is that most of my tables have at least thousands, and
usually millions of rows.  Sequential scans will never be faster for the
queries that I am doing - like I said, that is why I created the indexes.

My loading is done programatically, from another format, so COPY is not
an option.  Neither is removing foreign keys, as they are required to
guarantee valid data.  I don't really have a problem with the insert
speed when it is working properly - it is on par with other DBs that I
have on the same hardware.  The problem is when it stops using the
indexes, for no good reason.

Example, last night, I kicked off a load process - this morning, it had
only managed to make it through about 600,000 rows (split across several
tables).  After restarting it this morning, it made it through the same
data in 30 minutes.

If thats not bad and buggy behavior, I don't know what is....

Dan

--
****************************
Daniel Armbrust
Biomedical Informatics
Mayo Clinic Rochester
daniel.armbrust(at)mayo.edu
http://informatics.mayo.edu/