MIME-Version: 1.0
References: <CAFCRh-9SDUvcrF_RigOzXsbOm=8mHmzy2OSC1ejQNcjmfXXM_Q@mail.gmail.com>
 <CAKAnmmJeA-oXD-nOuqpi6EoSogmsgPAW-5puwN1scdc-snEC9g@mail.gmail.com>
In-Reply-To: <CAKAnmmJeA-oXD-nOuqpi6EoSogmsgPAW-5puwN1scdc-snEC9g@mail.gmail.com>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Mon, 24 Feb 2025 17:50:24 +0100
Message-ID: <CAFCRh-_6tqs3CF-Wf=AytJ6-_8-wq=9Tz02E7jQSk-mnRRm--w@mail.gmail.com>
Subject: Re: Keep specialized query pairs, or use single more general but more
 complex one
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-general@lists.postgresql.org, andrew@tao11.riddles.org.uk
Content-Type: multipart/alternative; boundary="000000000000e7660f062ee62346"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-_6tqs3CF-Wf%3DAytJ6-_8-wq%3D9Tz02E7jQSk-mnRRm--w%40mail.gmail.com>
Precedence: bulk

--000000000000e7660f062ee62346
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Feb 24, 2025 at 5:39=E2=80=AFPM Greg Sabino Mullane <htamfids@gmail=
.com>
wrote:

> On Mon, Feb 24, 2025 at 4:46=E2=80=AFAM Dominique Devienne <ddevienne@gma=
il.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
>> =3D 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

--000000000000e7660f062ee62346
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Feb 24, 2025 at 5:39=E2=80=AFPM G=
reg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmail=
.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=
=3D"ltr">On Mon, Feb 24, 2025 at 4:46=E2=80=AFAM Dominique Devienne &lt;<a =
href=3D"mailto:ddevienne@gmail.com" target=3D"_blank">ddevienne@gmail.com</=
a>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex"><div dir=3D"ltr"><div>But now we have a new requiremen=
t, for &quot;fuzzy find&quot;. I.e. the client can ask for names</div><div>=
which are not the exact in-DB names, but also aliases of those names.</div>=
</div></blockquote><div>...=C2=A0</div><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex"><div dir=3D"ltr"><div>join unnest($3::text[]) with ordinality=
 as aliases(name, ord) on <a href=3D"http://c.name" target=3D"_blank">c.nam=
e</a> =3D <a href=3D"http://aliases.name" target=3D"_blank">aliases.name</a=
></div></div></blockquote><div><br></div><div>I&#39;m not seeing how this i=
s supposed to work, if these aliases are not in the database somewhere.</di=
v></div></div></blockquote><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
"><div dir=3D"ltr"><div class=3D"gmail_quote"><div>Maybe an example? How do=
es &quot;Alli&quot; get mapped to a=C2=A0<a href=3D"http://c.name" target=
=3D"_blank">c.name</a> of &quot;Allison&quot;?</div></div></div></blockquot=
e><div><br></div><div>They may be stored in the DB somewhere one day, but a=
liases are session-specific (not my design...),</div><div>and we&#39;re int=
roducing them first managed in the client C++ code, instead of in TEMP tabl=
es later perhaps.</div><div><br></div><div>Client requests child named &quo=
t;Allison&quot;. There&#39;s no such row. Current simple query return no ro=
w.</div><div>We lookup whether there&#39;s a list of aliases for &quot;Alli=
son&quot;. If there are, we send them in $3 as an array</div><div>of string=
 (e.g. [&#39;All&#39;, &#39;Alli&#39;, ...], and the first one matching (th=
anks to order by ord limit 1) is returned, if any.</div><div><br></div><div=
>This works. We already have unit tests for that. That not the question.</d=
iv><div>The questions are about plan quality/performance of the complex que=
ry compared to the simpler one.</div><div>If planning of unnest+order by or=
dinal+limit 1 recognized as a special case?</div><div>Does the join order m=
atter with unnest?</div><div>These kind of things, which are above my pay g=
rade I&#39;m afraid... --DD</div><div><br></div></div></div>

--000000000000e7660f062ee62346--