Re: Keep specialized query pairs, or use single more general but more complex one

On Mon, Feb 24, 2025 at 5:39 PM Greg Sabino Mullane <htamfids(at)gmail(dot)com>
wrote:

> On Mon, Feb 24, 2025 at 4:46 AM Dominique Devienne <ddevienne(at)gmail(dot)com>
> 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