Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

Hi All,

We have one table which is storing XML data with 30k records and stores
huge amount of data.

We are trying to create the index for this column in the table. But, we’re
getting “Huge input Lookup error” during creation of Index.

Please check the below command which is used to create the index

CREATE INDEX xml_index on xml_table using BTREE (CAST (XPATH
(‘directory/access/mode/@Identifier’, content) as text[]))

Here we’re using Xpath expression to create the index since postgreSQL
directly does not support comparison methods. So, we decided to use Xpath
expression. But while creating the index as I mentioned above we’re facing
the issue with Huge Input lookup

I can able to create the index when the table have no records. But now we
have huge amount of data stored in the table. So, creating the index facing
the issue with Huge input lookup

The same error we have faced earlier when trying to retrieve the particular
rows from the table So we have changed the XML option from content to
document and then it got worked and we can able to retrieve the files now.

But, now while creating the index, we tried to change the XML Option from
content to document again. But this didn’t worked.

PS, we are using postgreSQL 14.8 version which is hosted in azure.

I would request to please suggest any ideas to resolve this issue. This
would help us a lot and appreciated