Suggestion on index creation for TEXT data field
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.
On Thu, May 21, 2020 at 7:28 AM postgann2020 s <postgann2020@gmail.com>
wrote:
which is having an avg width of 149bytes.
The average is meaningless if your maximum value exceeds a limit.
2. What type of index is the best suited for this type of data?.
And what type of data exactly are we talking about. "TEXT" is not a useful
answer.
If the raw data is too large no index is going to be "best" - as the hint
suggests you either need to drop the idea of indexing the column altogether
or apply some function to the raw data and then index the result.
David J.
Hi David,
Thanks for your email.
And what type of data exactly are we talking about. ==> Column is stroing
GIS data.
Regards,
PostgAnn.
On Thu, May 21, 2020 at 8:06 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
On Thu, May 21, 2020 at 7:28 AM postgann2020 s <postgann2020@gmail.com>
wrote:which is having an avg width of 149bytes.
The average is meaningless if your maximum value exceeds a limit.
2. What type of index is the best suited for this type of data?.
And what type of data exactly are we talking about. "TEXT" is not a
useful answer.If the raw data is too large no index is going to be "best" - as the hint
suggests you either need to drop the idea of indexing the column altogether
or apply some function to the raw data and then index the result.David J.
On 5/21/20 7:27 AM, postgann2020 s wrote:
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.^^^^^^^^^^^^^^^^^^^^^^
Hint supplies answer to 1) and 2) below.
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.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Thu, May 21, 2020 at 7:45 AM postgann2020 s <postgann2020@gmail.com>
wrote:
And what type of data exactly are we talking about. ==> Column is
stroing GIS data.
GIS data isn't really TEXT and isn't a core datatype of PostgreSQL so this
is maybe better posted to the PostGIS community directly...
David J.
Hi David, Adrian,
Thanks for the information.
Sure, will post on PostGIS community.
Regards,
PostgAnn.
On Thu, May 21, 2020 at 8:21 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
On Thu, May 21, 2020 at 7:45 AM postgann2020 s <postgann2020@gmail.com>
wrote:And what type of data exactly are we talking about. ==> Column is
stroing GIS data.
GIS data isn't really TEXT and isn't a core datatype of PostgreSQL so this
is maybe better posted to the PostGIS community directly...David J.