Thread: 8.3 beta problems

Hello,
I'm testing 8.3beta and I think that there is a problem with gist/gin
indexes. The performance of 8.3 is very bad comparing to 8.2. I have a
table with an int[] column indexed using gin (or gist with intarray
module). Table contains about 1.5m rows, int[] length is from 2 to 6
elements. Now, on the 8.2 the simple query "select id from bundles where
path @> array[255,254]" runs about 0.5s returning 25k of rows. The same
query on 8.3 returns the same number of rows, but it requires about 40s.
Anybody has the same problems ?

Regards,
ML

Marek Lewczuk pisze:
> Hello,
> I'm testing 8.3beta and I think that there is a problem with gist/gin
> indexes. The performance of 8.3 is very bad comparing to 8.2. I have a
> table with an int[] column indexed using gin (or gist with intarray
> module). Table contains about 1.5m rows, int[] length is from 2 to 6
> elements. Now, on the 8.2 the simple query "select id from bundles where
> path @> array[255,254]" runs about 0.5s returning 25k of rows. The same
> query on 8.3 returns the same number of rows, but it requires about 40s.
> Anybody has the same problems ?
>
> Regards,
> ML
Not 40s, but 14s... Below the details:

query:
--------
select id from bundles where itempath @> array[255,254];

explain for 8.2:
------------------
Bitmap Heap Scan on bundles  (cost=83.43..4273.16
rows=1351 width=4) (actual time=59.100..225.889 rows=25524 loops=1)
   Recheck Cond: (itempath @> '{254,255}'::integer[])
   ->  Bitmap Index Scan on bundles__itempath__idx
  (cost=0.00..83.09 rows=1351 width=0) (actual time=52.843..52.843
rows=25524 loops=1)
         Index Cond: (itempath @> '{254,255}'::integer[])
Total runtime: 236.302 ms

explain for 8.3:
------------------
Bitmap Heap Scan on bundles  (cost=83.43..4180.91
rows=1351 width=4) (actual time=7698.497..16960.217 rows=25524 loops=1)
   Recheck Cond: (itempath @> '{254,255}'::integer[])
   ->  Bitmap Index Scan on bundles__itempath__idx
  (cost=0.00..83.09 rows=1351 width=0) (actual time=7677.748..7677.748
rows=25524 loops=1)
         Index Cond: (itempath @> '{254,255}'::integer[])
Total runtime: 16979.855 ms


table structure:
------------------
CREATE TABLE bundles
(
   id integer NOT NULL,
   idtable character varying NOT NULL,
   itempath integer[] NOT NULL,
   itemvalue character varying,
   CONSTRAINT bundles__pkey PRIMARY KEY (id, idtable, itempath)
)
WITH (OIDS=FALSE);


CREATE INDEX bundles__itempath__idx
   ON bundles
   USING gist
   (itempath);

2007/10/16, Marek Lewczuk <newsy@lewczuk.com>:
> Hello,
> I'm testing 8.3beta and I think that there is a problem with gist/gin
> indexes. The performance of 8.3 is very bad comparing to 8.2. I have a
> table with an int[] column indexed using gin (or gist with intarray
> module). Table contains about 1.5m rows, int[] length is from 2 to 6
> elements. Now, on the 8.2 the simple query "select id from bundles where
> path @> array[255,254]" runs about 0.5s returning 25k of rows. The same
> query on 8.3 returns the same number of rows, but it requires about 40s.
> Anybody has the same problems ?
>
> Regards,
> ML
>

attach execution plan, please.

Pavel

Marek Lewczuk <newsy@lewczuk.com> writes:
>> I'm testing 8.3beta and I think that there is a problem with gist/gin
>> indexes. The performance of 8.3 is very bad comparing to 8.2.

Comparing to 8.2.which-exactly?

I'm wondering if you have an example that doesn't work well with the
gist page-splitting heuristic changes that Teodor put in awhile back.
Those are all in 8.2.5 already, though you might have to REINDEX to
really notice a change if the index was first built with an older
8.2.x release.

Can you provide sample data illustrating the problem?

            regards, tom lane

Tom Lane pisze:
> I'm wondering if you have an example that doesn't work well with the
> gist page-splitting heuristic changes that Teodor put in awhile back.
> Those are all in 8.2.5 already, though you might have to REINDEX to
> really notice a change if the index was first built with an older
> 8.2.x release.
> Can you provide sample data illustrating the problem?

I've checked how 8.3beta will perform on windows machine and it seems
that there is no problem with gist index - it means that that my
problems are related only to linux machine, where 8.3beta is working. Is
it possible, that 8.3beta compilation went wrong or maybe there are
other issues that may have influence on the performance ? I will add,
that on the same machine there was 8.2.5 installed and everything was
working just fine.

Regards,
ML