Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM
Yes, the ids is something I don't like either. They carry additional semantics, which I cannot make go away. How are chances char(20) is more time efficient than numeric(20)? Disk space is no problem here. On 12.01.2013, at 02:17, Claudio Freire wrote: > On Fri, Jan 11, 2013 at 8:55 PM, Horst Dehmer 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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM
On Fri, Jan 11, 2013 at 5:17 PM, Claudio Freire wrote: > On Fri, Jan 11, 2013 at 8:55 PM, Horst Dehmer 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM
On Sat, Jan 12, 2013 at 5:16 PM, Horst Dehmer wrote: > Yes, the ids is something I don't like either. > They carry additional semantics, which I cannot make go away. > How are chances char(20) is more time efficient than numeric(20)? > Disk space is no problem here. What are the other tables like then? The exact data types involved are at issue here, so it matters. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM
Jeff Janes writes: > Will PG allow you to add a FK constraint where there is no usable > index on the referenced side? It will not, because the referenced side must have a unique constraint, ie an index. The standard performance gotcha here is not having an index on the referencing side. But that only hurts when doing UPDATEs/DELETEs of referenced-side keys, which as far as I gathered was not the OP's scenario. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM
The types referenced by the foreign keys are the same Numeric(20). Since the complete schema (of about 300 tables) is generated, I will just try char(20) instead of numeric(20) in the next days to see if it makes any difference. Which I somehow doubt. But first I'm following the lead of the tables/indexes iostats given by Jeff. obj_item_loc references the following three tables and there should be no surprises. CREATE UNLOGGED TABLE loc ( loc_id numeric(20,0) NOT NULL, ... CONSTRAINT loc_pkey PRIMARY KEY (loc_id), … ) CREATE UNLOGGED TABLE obj_item ( obj_item_id numeric(20,0) NOT NULL, ... CONSTRAINT obj_item_pkey PRIMARY KEY (obj_item_id), … ) CREATE UNLOGGED TABLE rptd ( rptd_id numeric(20,0) NOT NULL, ... CONSTRAINT rptd_pkey PRIMARY KEY (rptd_id), … ) On 12.01.2013, at 23:18, Claudio Freire wrote: > On Sat, Jan 12, 2013 at 5:16 PM, Horst Dehmer wrote: >> Yes, the ids is something I don't like either. >> They carry additional semantics, which I cannot make go away. >> How are chances char(20) is more time efficient than numeric(20)? >> Disk space is no problem here. > > What are the other tables like then? > > The exact data types involved are at issue here, so it matters. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM
On Sat, Jan 12, 2013 at 7:41 PM, Horst Dehmer wrote: > Since the complete schema (of about 300 tables) is generated, I will just try > char(20) instead of numeric(20) in the next days to see if it makes any > difference. Which I somehow doubt. I think that might just make it worse. Well, maybe the others were right, and it's just that you're hitting the disk on that particular table. That, or it's all those CHECK constraints. Have you tried removing the CHECK constraints (they're a heapload of function calls) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
