On Thu, Sep 7, 2023 at 12:28 PM Sai Teja <saitejasaichintalapudi@xxxxxxxxx> wrote:
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
It doesn't support comparison methods of stuff like this because doing so tends to be pointless or very expensive, setting aside the fact that, as you probably are seeing here, records in an index must be small enough to fit on a physical page and large bodies of text typically don't.
If you truly want to perform equality checks on large bodies of text the typical solution is to hash said text and then perform a comparison against hashes.
Since you are producing an array you might be able to get something like a GIN or GIST index to work...I'm not all that familiar with them but they were designed for non-atomic data values.
David J.
