Hi,
In version 8.1.5, I have an rtree index on a 1.5 GB table. The size of this index is 500 MB. After migrating to 8.2.3, the size of this index has increased to 35GB. I’ve dropped are recreated the index and got the same result. In 8.2.3 the index type is gist, does this have something to do with it? At 35GB we have seen a decrease in performance. Any help or hints are appreciated.
CREATE INDEX binloc_boxrange
ON featureloc
USING rtree
(boxrange(fmin, fmax));
CREATE TABLE featureloc
(
featureloc_id serial NOT NULL,
feature_id integer NOT NULL,
srcfeature_id integer,
fmin integer,
is_fmin_partial boolean NOT NULL DEFAULT false,
fmax integer,
is_fmax_partial boolean NOT NULL DEFAULT false,
strand smallint,
phase integer,
residue_info text,
locgroup integer NOT NULL DEFAULT 0,
rank integer NOT NULL DEFAULT 0,
….
CREATE OR REPLACE FUNCTION boxrange(integer, integer)
RETURNS box AS
'SELECT box (create_point(0, $1), create_point($2,500000000))'
LANGUAGE 'sql' IMMUTABLE;
ALTER FUNCTION boxrange(integer, integer) OWNER TO cjm;
CREATE OR REPLACE FUNCTION create_point(integer, integer)
RETURNS point AS
'SELECT point ($1, $2)'
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION create_point(integer, integer) OWNER TO cjm;
Thanks,
Tom
