PostGIS Error : size of index too small


  • QA Engineer

    PostgreSQL 13.5, PostGIS 3.2, Windows Server 2012.

    My main goal is to create an unique index on a column containing geometries.

    Here is the table :

    CREATE TABLE IF NOT EXISTS geometrie.uniteobservation_geom
    (
    gid bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    gid_unobs bigint,
    geometrie character varying COLLATE pg_catalog."default",
    CONSTRAINT gid_pk PRIMARY KEY (gid)
    )
    

    Here is the query to fill the table (following some threads seen here) :

    insert into geometrie.uniteobservation_geom (gid_unobs, geometrie) 
    select gid, md5(st_asBinary(geom)) from 
    (select 
     u.gid,
     u.geom
    FROM activite.uniteobservation u
     JOIN ara.departements d ON st_intersects(u.geom, d.geom)) as ssreq;
    

    Then I can't create an index on geometrie column :

    create unique index if not exists idx_geom_unobs on geometrie.uniteobservation_geom (geometrie)
    

    returns the row index requires 10848 octets, maximum size is 8191

    I have tried to shorten the values with left(md5(st_asBinary(geom)), 10), but it failed. And since there are already thousand rows and there will tens of thousand rows, I don't want to shorten the values much !

    What solutions can I try to create an index that fixs my goal ?



  • Your query shows only MD5 values inserted into the column, which wouldn't be a problem. But somewhere there must be a much bigger value in the table.

    You can find the ten largest values with a query like

    SELECT gid, length(geometrie)
    FROM geometrie.uniteobservation_geom
    ORDER BY length(geometrie)
    LIMIT 10;
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2