Skip site navigation (1) Skip section navigation (2)

Why creating GIN table index is so slow than inserting data into empty table with the same index?

From: Sergey Burladyan <eshkinkot(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Why creating GIN table index is so slow than inserting data into empty table with the same index?
Date: 2009-03-24 00:59:34
Message-ID: 874oxjrb0p.fsf@seb.progtech.ru (view raw or flat)
Thread:
Lists: pgsql-performance
example:

select version();
                                          version
--------------------------------------------------------------------------------------------
 PostgreSQL 8.3.6 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 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
Время: 570,110 мс

create index arr_gin on a using gin ( (array[i1, i2, i3, i4, i5, i6]) );
CREATE INDEX
Время: 203068,314 мс

truncate a;
drop index arr_gin ;

create index arr_gin on a using gin ( (array[i1, i2, i3, i4, i5, i6]) );
CREATE INDEX
Время: 3,246 мс

insert into a select n, n, n, n, n, n from generate_series(1, 100000) as n;
INSERT 0 100000
Время: 2405,481 мс

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 !

-- 
Sergey Burladyan

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-03-24 03:35:12
Subject: Re: Why creating GIN table index is so slow than inserting data into empty table with the same index?
Previous:From: Tom LaneDate: 2009-03-23 23:02:52
Subject: Re: multiple threads inserting into the same table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group