rtree/gist index taking enormous amount of space in 8.2.3

Поиск
Список
Период
Сортировка
От Dolafi, Tom
Тема rtree/gist index taking enormous amount of space in 8.2.3
Дата
Msg-id AE9860225100F14D87B26D0D4D6766DB46F246@EXCHANGE03.janelia.priv
обсуждение исходный текст
Ответы Re: rtree/gist index taking enormous amount of space in 8.2.3  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Greg Smith
Дата:
Сообщение: Re: PostgreSQL Configuration Tool for Dummies
Следующее
От: Josh Berkus
Дата:
Сообщение: Re: PostgreSQL Configuration Tool for Dummies