On Fri, Jan 11, 2013 at 5:17 PM, Claudio Freire <klaussfreire@xxxxxxxxx> wrote: > On Fri, Jan 11, 2013 at 8:55 PM, Horst Dehmer <horst.dehmer@xxxxxxxxx> 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. Will PG allow you to add a FK constraint where there is no usable index on the referenced side? I have failed to do so, but perhaps I am not being devious enough. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
