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);
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
