From: Vitalii Tymchyshyn <tivv00@gmail.com>
To: Robert Ayrapetyan <robert.ayrapetyan@comodo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance die when COPYing to table with bigint PK
Date: Mon, 01 Aug 2011 12:06:36 +0300
Message-ID: <4E366C9C.4040904@gmail.com> (raw)
In-Reply-To: <CAAboi9sk0EnbS23-tr86X9i7LdHTfdbTP4aa7r2q7NpWKaHWPA@mail.gmail.com>
References: <CAAboi9sk0EnbS23-tr86X9i7LdHTfdbTP4aa7r2q7NpWKaHWPA@mail.gmail.com>
31.07.11 16:51, Robert Ayrapetyan написав(ла):
> Hello.
>
> I've found strange behavior of my pg installation (tested both 8.4 and
> 9.0 - they behave same) on FreeBSD platform.
> In short - when some table have PK on bigint field - COPY to that
> table from file becomes slower and slower as table grows. When table
> reaches ~5GB - COPY of 100k records may take up to 20 mins. I've
> experimented with all params in configs, moved indexes to separate hdd
> etc - nothing made any improvement. However, once I'm dropping 64 bit
> PK - COPY of 100k records passes in seconds. Interesting thing - same
> table has other indexes, including composite ones, but none of them
> include bigint fields, that's why I reached decision that bug
> connected with indexes on bigint fields only.
I did see this behavior, but as for me it occurs for UNIQUE indexes only
(including PK), not dependent on field type.
You can check this by dropping PK and creating it as a regular
non-unique index.
Best regards, Vitalii Tymchyshyn
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: tivv00@gmail.com, robert.ayrapetyan@comodo.com
Subject: Re: Performance die when COPYing to table with bigint PK
In-Reply-To: <4E366C9C.4040904@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