Postgres Pro
Downloads
Home   >   mailing lists

Thread: building gist index on int[] field is terrible slow. Is it bug?

building gist index on int[] field is terrible slow. Is it bug?

From
"Pavel Stehule"
Date:
Hello

I tested contrib package intarray and gist support from this package.
I was surpirised. Index building needed lot of time.

10K fields .. 106sec
20K f         ..265 sec
30Kf          .. 450 sec
50Kf          .. 1283sec

building gin index for 50K fields needed 0.5sec

Regards
Pavel Stehule

sample of data:
pavel=# select * from test limit 10;          a

-----------------------{4209,4207,4197,2066}{4832,3004}{4629}{3243}{4816}{3726}{4834}{1459,3160,3984}{4569}{4164,1307,962,4482}
(10 rows)

pavel=# select version();                                               version
-------------------------------------------------------------------------------------------------------PostgreSQL
8.3develon i686-pc-linux-gnu, compiled by GCC gcc (GCC)
 
4.1.2 20070502 (Red Hat 4.1.2-12)
(1 row)

Time: 442,034 ms

pavel=# show maintenance_work_mem ;maintenance_work_mem
----------------------16MB
(1 row)

pavel=# set maintenance_work_mem to '300MB';
SET
Time: 0,230 ms
pavel=#
pavel=# CREATE index fooidx on test using gist(a gist__int_ops);
CREATE INDEX
Time: 1269276,866 ms

Re: building gist index on int[] field is terrible slow. Is it bug?

From
Oleg Bartunov
Date:
On Thu, 16 Aug 2007, Pavel Stehule wrote:

> Hello
>
> I tested contrib package intarray and gist support from this package.
> I was surpirised. Index building needed lot of time.
>
> 10K fields .. 106sec
> 20K f         ..265 sec
> 30Kf          .. 450 sec
> 50Kf          .. 1283sec
>
> building gin index for 50K fields needed 0.5sec

you probably need gist__intbig_ops

>
> Regards
> Pavel Stehule
>
> sample of data:
> pavel=# select * from test limit 10;
>           a
> -----------------------
> {4209,4207,4197,2066}
> {4832,3004}
> {4629}
> {3243}
> {4816}
> {3726}
> {4834}
> {1459,3160,3984}
> {4569}
> {4164,1307,962,4482}
> (10 rows)
>
> pavel=# select version();
>                                                version
> -------------------------------------------------------------------------------------------------------
> PostgreSQL 8.3devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
> 4.1.2 20070502 (Red Hat 4.1.2-12)
> (1 row)
>
> Time: 442,034 ms
>
> pavel=# show maintenance_work_mem ;
> maintenance_work_mem
> ----------------------
> 16MB
> (1 row)
>
> pavel=# set maintenance_work_mem to '300MB';
> SET
> Time: 0,230 ms
> pavel=#
> pavel=# CREATE index fooidx on test using gist(a gist__int_ops);
> CREATE INDEX
> Time: 1269276,866 ms
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Re: building gist index on int[] field is terrible slow. Is it bug?

From
"Pavel Stehule"
Date:
2007/8/16, Oleg Bartunov <oleg@sai.msu.su>:
> On Thu, 16 Aug 2007, Pavel Stehule wrote:
>
> > Hello
> >
> > I tested contrib package intarray and gist support from this package.
> > I was surpirised. Index building needed lot of time.
> >
> > 10K fields .. 106sec
> > 20K f         ..265 sec
> > 30Kf          .. 450 sec
> > 50Kf          .. 1283sec
> >
> > building gin index for 50K fields needed 0.5sec
>
> you probably need gist__intbig_ops
>

yes, it helps
thank you
Pavel Stehule