Suggestion on index creation for TEXT data field

From: postgann2020 s <postgann2020(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Subject: Suggestion on index creation for TEXT data field
Date: 2020-05-21 14:27:44
Message-ID: CANynezMUb3xF4Tuj5a4CH+BYNkPxfAQR3qg5ExRTuuWc8veRJg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general pgsql-performance

Hi Team,
Thanks for your support.
Could you please suggest on below query.

Environment

PostgreSQL: 9.5.15
Postgis: 2.2.7
Mostly table contains GIS data and we are trying to creating an index on
the column which is having an avg width of 149bytes.

CREATE INDEX index_idx
ON SCHEMA.TABLE
USING btree
(column);

ERROR: index row size 2976 exceeds maximum 2712 for index "index_idx"
HINT: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full-text
indexing.

Could you please suggest on below queries.
1. How to solve the issue?.
2. What type of index is the best suited for this type of data?.

Thanks for your support.

Regards,
PostgAnn.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2020-05-21 14:36:39 Re: Suggestion on index creation for TEXT data field
Previous Message postgann2020 s 2020-05-21 14:18:03 Suggestion on table analyze

Browse pgsql-general by date

  From Date Subject
Next Message Amit Kapila 2020-05-21 14:30:50 Re: Behaviour of failed Primary
Previous Message postgann2020 s 2020-05-21 14:18:03 Suggestion on table analyze

Browse pgsql-performance by date

  From Date Subject
Next Message David G. Johnston 2020-05-21 14:36:39 Re: Suggestion on index creation for TEXT data field
Previous Message postgann2020 s 2020-05-21 14:18:03 Suggestion on table analyze