[postgis-tickets] [PostGIS] #3716: Gist index irrational growth

[PostGIS]

#3716: Gist index irrational growth
-------------------------------+---------------------
 Reporter:  Marcin Piotrowski  |      Owner:  pramsey
     Type:  defect             |     Status:  new
 Priority:  medium             |  Milestone:
Component:  postgis            |    Version:  2.1.x
 Keywords:                     |
-------------------------------+---------------------
 etry column).
 The size was not shrinked during autovacuum, only full vacuum was able to
 free the disk space.

 This is reproducable using following example that inserts 10000 rows and
 performs 1000 updates on the status column. After that index size is 321
 MB
 (321 MB of indexed nulls ! )

 {{{

 -- psql script to create the database

 CREATE DATABASE gist_weirdness WITH TEMPLATE = template0 ENCODING = 'UTF8'
 LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';
 \connect gist_weirdness
 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
 CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
 CREATE TABLE table_with_gist (
     id integer NOT NULL,
     status character varying(10),
     geompolygon geometry(Polygon,4326)
 );
 CREATE SEQUENCE table_with_gist_id_seq
     START WITH 1
     INCREMENT BY 1
     NO MINVALUE
     NO MAXVALUE
     CACHE 1;
 ALTER TABLE ONLY table_with_gist ALTER COLUMN id SET DEFAULT
 nextval('table_with_gist_id_seq'::regclass);

 CREATE INDEX incidents_geompolygon_idx ON table_with_gist USING gist
 (geompolygon);


 --  pgscript to insert data
 DECLARE @I;
 set @I = 0;
 while @I < 10000
 begin
 INSERT INTO table_with_gist(status)  VALUES (cast (@I as CHARACTER
 VARYING));
 set @I = @I + 1;
 end


 --pgscript to update data

 DECLARE @I;
 set @I = 0;
 while @I < 1000
 begin
 update table_with_gist set status=status::integer+7;
 set @I = @I + 1;
 end


 }}}

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3716>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.