Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id C232B637777 for ; Tue, 24 Mar 2009 00:36:37 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 46210-04 for ; Tue, 24 Mar 2009 00:36:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 92A186323DF for ; Tue, 24 Mar 2009 00:35:13 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n2O3ZCGZ018582; Mon, 23 Mar 2009 23:35:13 -0400 (EDT) To: Sergey Burladyan cc: Oleg Bartunov , Teodor Sigaev , pgsql-performance@postgresql.org Subject: Re: Why creating GIN table index is so slow than inserting data into empty table with the same index? In-reply-to: <874oxjrb0p.fsf@seb.progtech.ru> References: <874oxjrb0p.fsf@seb.progtech.ru> Comments: In-reply-to Sergey Burladyan message dated "Tue, 24 Mar 2009 03:59:34 +0300" Date: Mon, 23 Mar 2009 23:35:12 -0400 Message-ID: <18581.1237865712@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL=0.009 X-Spam-Level: X-Archive-Number: 200903/342 X-Sequence-Number: 33264 Sergey Burladyan writes: > show maintenance_work_mem ; > maintenance_work_mem > ---------------------- > 128MB > create table a (i1 int, i2 int, i3 int, i4 int, i5 int, i6 int); > insert into a select n, n, n, n, n, n from generate_series(1, 100000) as n; > create index arr_gin on a using gin ( (array[i1, i2, i3, i4, i5, i6]) ); [ takes forever ] Seems the reason this is so awful is that the incoming data is exactly presorted, meaning that the tree structure that ginInsertEntry() is trying to build degenerates to a linear list (ie, every new element becomes the right child of the prior one). So the processing becomes O(N^2) up till you reach maintenance_work_mem and flush the tree. With a large setting for maintenance_work_mem it gets spectacularly slow. I think a reasonable solution for this might be to keep an eye on maxdepth and force a flush if that gets too large (more than a few hundred, perhaps?). Something like this: /* If we've maxed out our available memory, dump everything to the index */ + /* Also dump if the tree seems to be getting too unbalanced */ - if (buildstate->accum.allocatedMemory >= maintenance_work_mem * 1024L) + if (buildstate->accum.allocatedMemory >= maintenance_work_mem * 1024L || + buildstate->accum.maxdepth > DEPTH_LIMIT) { The new fast-insert code likely will need a similar defense. Comments? regards, tom lane