Seems this assumption is not right. Just created simple index on bigint column - situation with huge performance degradation repeated. Dropping this index solved COPY issues on the fly. So I'm still convinced - this bug relates to FreeBSD 64-bit + UFS + bigint column index (some of these may be superfluous, but I have no resources to check on different platforms with different filesystems). On Mon, Aug 1, 2011 at 12:15 PM, Robert Ayrapetyan <robert.ayrapetyan@xxxxxxxxxx> wrote: > Quite possible. > But anyway - I don't think performance degradation must be so huge in > case of using UNIQUE indexes. > > On Mon, Aug 1, 2011 at 12:06 PM, Vitalii Tymchyshyn <tivv00@xxxxxxxxx> wrote: >> 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 >> > > > > -- > Ayrapetyan Robert, > Comodo Anti-Malware Data Processing Analysis and Management System (CAMDPAMS) > http://repo-qa.camdpams.odessa.office.comodo.net/mediawiki/index.php > -- Ayrapetyan Robert, Comodo Anti-Malware Data Processing Analysis and Management System (CAMDPAMS) http://repo-qa.camdpams.odessa.office.comodo.net/mediawiki/index.php -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
