Re: Insert performance for large transaction with multiple COPY FROM

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux