Re: Performance die when COPYing to table with bigint PK

On Sun, Jul 31, 2011 at 2:51 PM, Robert Ayrapetyan
<robert(dot)ayrapetyan(at)comodo(dot)com> wrote:

> 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.
>
> In terms of IO picture is following: after copy started gstat shows
> 100% load on index partition (as I mentioned above - I've tried
> separate hdd to keep index tablespace), large queue (over 2k
> elements), and constant slow write on speed of ~2MB\s. Hdd becomes
> completely unresponsive, even ls on empty folder hangs for minute or
> so.
>
> To avoid thoughts like "your hdd is slow, you haven't tuned
> postgresql.conf etc" - all slowness dissapears with drop of bigint PK,
> same time other indexes on same table remain alive. And yes - I've
> tried drop PK \ recreate PK, vacuum full analyze and all other things
> - nothing helped, only drop helps.
>
> Is this known and expected behavior?

This is a duplicate post with one on BUGS, being discussed there.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services