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

2007-08-16 Thread Pavel Stehule 
2007/8/16, Oleg Bartunov <[EMAIL PROTECTED]>:
> 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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

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

2007-08-16 Thread Oleg Bartunov 

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

---(end of broadcast)---
TIP 6: explain analyze is your friend

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

2007-08-16 Thread Pavel Stehule 
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.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