From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Sai Teja <saitejasaichintalapudi(at)gmail(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL |
Date: | 2023-09-07 19:56:52 |
Message-ID: | CAKFQuwZDXRq0cbMH501e1OMe8kZV=uoT7PiN8+7D6OrJBjLPJg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Sep 7, 2023 at 12:28 PM Sai Teja <saitejasaichintalapudi(at)gmail(dot)com>
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2023-09-07 20:21:43 | Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL |
Previous Message | Erik Wienhold | 2023-09-07 19:37:50 | Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL |