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.
...
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
