On Sun, Jul 31, 2011 at 2:47 PM, Robert <robert.ayrapet...@gmail.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
