Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group