Re: 8.3 beta problems

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);