From: Horst Dehmer <horst.dehmer@gmail.com>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Insert performance for large transaction with multiple COPY FROM
Date: Sat, 12 Jan 2013 21:16:02 +0100
Message-ID: <9F5056DC-8089-40A5-8D41-508EADC63A09@gmail.com> (raw)
In-Reply-To: <CAGTBQpavK111WhRZYk1pfRsv3ixn-VnQKD1EZbbD=Yg-y4pZ+Q@mail.gmail.com>
References: <78A65A52-C49D-4CA4-9A63-1F5F120BC7E7@gmail.com>
<CAGTBQpavK111WhRZYk1pfRsv3ixn-VnQKD1EZbbD=Yg-y4pZ+Q@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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 <klaussfreire@gmail.com> wrote:
> On Fri, Jan 11, 2013 at 8:55 PM, Horst Dehmer <horst.dehmer@gmail.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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: horst.dehmer@gmail.com, klaussfreire@gmail.com
Subject: Re: Insert performance for large transaction with multiple COPY FROM
In-Reply-To: <9F5056DC-8089-40A5-8D41-508EADC63A09@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox