rtree/gist index taking enormous amount of space in 8.2.3

From: "Dolafi, Tom" <dolafit(at)janelia(dot)hhmi(dot)org>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: rtree/gist index taking enormous amount of space in 8.2.3
Date: 2007-06-27 15:17:48
Message-ID: AE9860225100F14D87B26D0D4D6766DB46F246@EXCHANGE03.janelia.priv
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Josh Berkus 2007-06-27 18:02:35 Re: PostgreSQL Configuration Tool for Dummies
Previous Message Greg Smith 2007-06-26 22:05:21 Re: PostgreSQL Configuration Tool for Dummies