Opened 8 years ago
Last modified 7 years ago
#3716 closed defect
Gist index irrational growth — at Version 2
Reported by: | mcinp | Owned by: | pramsey |
---|---|---|---|
Priority: | critical | Milestone: | PostGIS 2.2.7 |
Component: | postgis | Version: | 2.1.x |
Keywords: | Cc: |
Description (last modified by )
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