public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dominique Devienne <[email protected]>
To: Greg Sabino Mullane <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: Keep specialized query pairs, or use single more general but more complex one
Date: Mon, 24 Feb 2025 17:50:24 +0100
Message-ID: <CAFCRh-_6tqs3CF-Wf=AytJ6-_8-wq=9Tz02E7jQSk-mnRRm--w@mail.gmail.com> (raw)
In-Reply-To: <CAKAnmmJeA-oXD-nOuqpi6EoSogmsgPAW-5puwN1scdc-snEC9g@mail.gmail.com>
References: <CAFCRh-9SDUvcrF_RigOzXsbOm=8mHmzy2OSC1ejQNcjmfXXM_Q@mail.gmail.com>
<CAKAnmmJeA-oXD-nOuqpi6EoSogmsgPAW-5puwN1scdc-snEC9g@mail.gmail.com>
On Mon, Feb 24, 2025 at 5:39 PM Greg Sabino Mullane <[email protected]>
wrote:
> On Mon, Feb 24, 2025 at 4:46 AM Dominique Devienne <[email protected]>
> wrote:
>
>> But now we have a new requirement, for "fuzzy find". I.e. the client can
>> ask for names
>> which are not the exact in-DB names, but also aliases of those names.
>>
> ...
>
>> join unnest($3::text[]) with ordinality as aliases(name, ord) on c.name
>> = aliases.name
>>
>
> I'm not seeing how this is supposed to work, if these aliases are not in
> the database somewhere.
>
Maybe an example? How does "Alli" get mapped to a c.name of "Allison"?
>
They may be stored in the DB somewhere one day, but aliases are
session-specific (not my design...),
and we're introducing them first managed in the client C++ code, instead of
in TEMP tables later perhaps.
Client requests child named "Allison". There's no such row. Current simple
query return no row.
We lookup whether there's a list of aliases for "Allison". If there are, we
send them in $3 as an array
of string (e.g. ['All', 'Alli', ...], and the first one matching (thanks to
order by ord limit 1) is returned, if any.
This works. We already have unit tests for that. That not the question.
The questions are about plan quality/performance of the complex query
compared to the simpler one.
If planning of unnest+order by ordinal+limit 1 recognized as a special case?
Does the join order matter with unnest?
These kind of things, which are above my pay grade I'm afraid... --DD
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: Keep specialized query pairs, or use single more general but more complex one
In-Reply-To: <CAFCRh-_6tqs3CF-Wf=AytJ6-_8-wq=9Tz02E7jQSk-mnRRm--w@mail.gmail.com>
* 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