Re: Insert performance for large transaction with multiple COPY FROM

On Fri, Jan 11, 2013 at 8:55 PM, Horst Dehmer <horst(dot)dehmer(at)gmail(dot)com> wrote:
> Except - and that's the wall I'm hitting - for one table which yielded just
> 75 records/second.
> The main 'problem' seem to be the FK constraints. Dropping just them
> restored insert performance for this table to 6k records/s. The table in
> question has a composite PK (3 columns), 3 foreign keys and a bunch of
> indexes (see table obj_item_loc at the end of the mail). Compared to the
> other 32 tables nothing unusual.
> I'd gladly supply more information if necessary.
...
> CREATE TABLE obj_item_loc
> (
> obj_item_id numeric(20,0) NOT NULL,
> loc_id numeric(20,0) NOT NULL,
> obj_item_loc_ix numeric(20,0) NOT NULL,

That sounds a lot like a missing index on the target relations (or
indices that are unusable).

Those numeric ids look really unusual. Why not bigint? It's close to
the same precision, but native, faster, more compact, and quite
unambiguous when indices are involved. If the types don't match on
both tables, it's quite likely indices won't be used when checking the
FK, and that spells trouble.