Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 16331637A4E for ; Mon, 23 Mar 2009 21:59:57 -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 90581-01 for ; Mon, 23 Mar 2009 21:59:40 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.152]) by mail.postgresql.org (Postfix) with ESMTP id DCCBC6379E3 for ; Mon, 23 Mar 2009 21:59:38 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id l26so405150fgb.0 for ; Mon, 23 Mar 2009 17:59:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:to:subject:from:date :message-id:user-agent:mime-version:content-type :content-transfer-encoding; bh=+rsU7YNu/rh2J7d0RBWZ5knS6hvxyI/PrungvBnGMos=; b=rlgE6wtJmLRFIjG09P3cABKXtpFAS2mlFcWHFENgpDGm7kAymq1Czq4SnqRX/HTmX/ Z46C2rrv8IYJk9EBNk16XtDkX5GinN4KQF3ITxMQCqJcBbT2kcpkazbVVltvH1PiqHLw e36s7JU83F1xiEGgpSKiq1c3H1uA6m8g+wXrg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=to:subject:from:date:message-id:user-agent:mime-version :content-type:content-transfer-encoding; b=qQqInxRhL7KI8NKqAkQZ4Jn7WCC9eKG6tixz/aoaKsXLQhEcgHLrcko85qqrRj1wLI 9FMaqiAZAyFnBrYvOotgnNc+50xn4789Tqkhjkdou72X7xChJRxm819vket7DLsNun07 Bv6pvpipfHVVU2kA/rZVZ3T3HAvADIIwnyiZs= Received: by 10.86.3.4 with SMTP id 4mr4139895fgc.41.1237856376926; Mon, 23 Mar 2009 17:59:36 -0700 (PDT) Received: from seb.progtech.ru (os.progtech.ru [80.252.153.41]) by mx.google.com with ESMTPS id e11sm7964578fga.20.2009.03.23.17.59.36 (version=TLSv1/SSLv3 cipher=RC4-MD5); Mon, 23 Mar 2009 17:59:36 -0700 (PDT) To: pgsql-performance@postgresql.org Subject: Why creating GIN table index is so slow than inserting data into empty table with the same index? From: Sergey Burladyan Date: Tue, 24 Mar 2009 03:59:34 +0300 Message-ID: <874oxjrb0p.fsf@seb.progtech.ru> User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200903/341 X-Sequence-Number: 33263 example: select version(); version ---------------------------------------------------------------------------= ----------------- PostgreSQL 8.3.6 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debia= n 4.3.3-3) 4.3.3 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; INSERT 0 100000 =D0=92=D1=80=D0=B5=D0=BC=D1=8F: 570,110 =D0=BC=D1=81 create index arr_gin on a using gin ( (array[i1, i2, i3, i4, i5, i6]) ); CREATE INDEX =D0=92=D1=80=D0=B5=D0=BC=D1=8F: 203068,314 =D0=BC=D1=81 truncate a; drop index arr_gin ; create index arr_gin on a using gin ( (array[i1, i2, i3, i4, i5, i6]) ); CREATE INDEX =D0=92=D1=80=D0=B5=D0=BC=D1=8F: 3,246 =D0=BC=D1=81 insert into a select n, n, n, n, n, n from generate_series(1, 100000) as n; INSERT 0 100000 =D0=92=D1=80=D0=B5=D0=BC=D1=8F: 2405,481 =D0=BC=D1=81 select pg_size_pretty(pg_total_relation_size('a')) as total, pg_size_pretty(pg_relation_size('a')) as table; total | table ---------+--------- 9792 kB | 5096 kB 203068.314 ms VS 2405.481 ms, is this behaviour normal ? Thanks ! --=20 Sergey Burladyan