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
