Re: BUG #6136: Perfomance dies when using PK on 64-bit field

On Sun, Jul 31, 2011 at 2:47 PM, Robert <robert(dot)ayrapetyan(at)gmail(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.

Sounds weird. Looking at this now.

Could be that its storing the wrong kind of plan on the RI trigger for PK.

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