public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jonathan S. Katz <[email protected]>
To: Tom Lane <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Large expressions in indexes can't be stored (non-TOASTable)
Date: Wed, 4 Sep 2024 15:20:33 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<ZtipsVMZAGjrO1p_@nathan>
<[email protected]>
On 9/4/24 3:08 PM, Tom Lane wrote:
> Nathan Bossart <[email protected]> writes:
>> Thanks to commit 96cdeae, only a few catalogs remain that are missing TOAST
>> tables: pg_attribute, pg_class, pg_index, pg_largeobject, and
>> pg_largeobject_metadata. I've attached a short patch to add one for
>> pg_index, which resolves the issue cited here. This passes "check-world"
>> and didn't fail for a few ad hoc tests (e.g., VACUUM FULL on pg_index). I
>> haven't spent too much time investigating possible circularity issues, but
>> I'll note that none of the system indexes presently use the indexprs and
>> indpred columns.
>
> Yeah, the possibility of circularity seems like the main hazard, but
> I agree it's unlikely that the entries for system indexes could ever
> need out-of-line storage. There are many other things that would have
> to be improved before a system index could use indexprs or indpred.
Agreed on the unlikeliness of that, certainly in the short-to-mid term.
The impetus driving this is dealing with a data type that can be quite
large, and it's unlikely system catalogs will be dealing with anything
of that nature, or requiring very long expressions that couldn't be
encapsulated in a different way.
Just to be fair, in the case I presented there's an argument that what
I'm trying to do is fairly inefficient for an expression, given I'm
passing around an additional several KB payload into the query. However,
we'd likely have to do that anyway for this problem space, and the
overall performance hit is negligible compared to the search relevancy
boost.
I'm working on a much more robust test, but using a known 10MM 768-dim
dataset and two C-based quantization functions (one using the
expression), I got a 3% relevancy boost with a 2% reduction in latency
and throughput. On some other known datasets, I was able to improve
relevancy 40% or more, though given they were initially returning with
0% relevancy in some cases, it's not fair to compare performance numbers.
There are other ways to solve the problem as well, but allowing for the
larger expression gives users more choices in how they can approach it.
Jonathan
Attachments:
[application/pgp-signature] OpenPGP_signature.asc (840B, ../[email protected]/2-OpenPGP_signature.asc)
download
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Large expressions in indexes can't be stored (non-TOASTable)
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox