Обсуждение: [BUGS] BUG #14537: Gist index irrational growth

The following bug has been logged on the website:

Bug reference:      14537
Logged by:          Marcin Piotrowski
Email address:      mcinp@o2.pl
PostgreSQL version: 9.6.1
Operating system:   Amazon Linux, Ubuntu
Description:

If you create a GiST index on a geometry(Polygon,4267) column and do updates
on the table, the index size grows enormously even if the indexed column
contains only nulls. This is a big problem  that caused index to grow to
tens of gigabytes for a table with just about 10000 rows, where indexed
column contained only nulls, but was updated on a minutely basis (but not on
the geometry 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




--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

mcinp@o2.pl writes:
> If you create a GiST index on a geometry(Polygon,4267) column and do updates
> on the table, the index size grows enormously even if the indexed column
> contains only nulls.

I don't see any comparable misbehavior when testing a GIST index on a
plain polygon column full of nulls, so I would say this is specific to the
geometry opclass.  Which means you need to tell the PostGIS folk about it,
not us.

            regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs