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

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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83