MIME-Version: 1.0
From: Dominique Devienne <ddevienne@gmail.com>
Date: Mon, 24 Feb 2025 10:46:17 +0100
Message-ID: <CAFCRh-9SDUvcrF_RigOzXsbOm=8mHmzy2OSC1ejQNcjmfXXM_Q@mail.gmail.com>
Subject: Keep specialized query pairs, or use single more general but more
 complex one
To: pgsql-general@lists.postgresql.org
Cc: andrew@tao11.riddles.org.uk
Content-Type: multipart/alternative; boundary="0000000000002d5d8b062ee037eb"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-9SDUvcrF_RigOzXsbOm%3D8mHmzy2OSC1ejQNcjmfXXM_Q%40mail.gmail.com>
Precedence: bulk

--0000000000002d5d8b062ee037eb
Content-Type: text/plain; charset="UTF-8"

Hi,

We have a few dozen queries involving grandparent, parent, child table
triplets,
to implement a pre-existing API on top of PostgreSQL. That API is not
SQL-based
nor SQL "friendly" either, that's why we detect patterns in the APIs
inputs, to use
different SQL queries, as (important) optimizations, thus the "dozen of
queries" above.

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. That
requires a
different query, which is a bit more complex. Here's an example:

# existing "exact-name" query
select ...
from child c
join parent s on s.id = c.parent
join grantparent w on w.id = s.parent
where w.name = $1
and s.name = $2
and c.name = $3

# new "aliased name" query
select ...
from child c
join parent s on s.id = c.parent
join grantparent w on w.id = s.parent
join unnest($3::text[]) with ordinality as aliases(name, ord) on c.name =
aliases.name
where w.name = $1
and s.name = $2
order by aliases.ord
limit 1

Notice the limit 1, i.e. this is still a scalar query, since name or
(parent, name) are UNIQUE,
and the fact the alias query is ordered, the first match in alias order is
"preferred".

Given the above, it is obvious that if we stick the exact name in front of
its aliases, and use only the 2nd query, this is functionally equivalent
(if it isn't, please let us know!).

And from a maintenance perspective, not doubling our queries sounds like a
good thing.

But then, I do worry about performance. Will the second more complex query
be planned just as well of the 1st one?

We have two types of clients (apps) for that API:
* The first type never relies on fuzzy-find.
* While the second type relies extensively on it.
Thus I don't want to penalize the first type, over the second.

I'd appreciate an expert opinion on planning (Tom? Andrew?),
in the context of unnest+order by ordinal+limit 1 with UNIQUE constraints,
versus the simpler 3-way-join with equality constraints, to base my
decision on.

Thanks, --DD

PS: We are v16+ based.

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

<div dir=3D"ltr">Hi,<div><br></div><div>We have a few dozen queries involvi=
ng grandparent, parent, child table triplets,</div><div>to implement a pre-=
existing API on top of PostgreSQL. That API is not SQL-based</div><div>nor =
SQL &quot;friendly&quot; either, that&#39;s why we detect patterns in the A=
PIs inputs, to use</div><div>different SQL queries, as (important) optimiza=
tions, thus the &quot;dozen of queries&quot; above.</div><div><br></div><di=
v>But now we have a new requirement, for &quot;fuzzy find&quot;. I.e. the c=
lient can ask for names</div><div>which are not the exact in-DB names, but =
also aliases of those names. That requires a</div><div>different query, whi=
ch is a bit more complex. Here&#39;s an example:</div><div><br></div><div>#=
 existing &quot;exact-name&quot; query</div><div>select ...<br>from child c=
<br>join parent s on <a href=3D"http://s.id">s.id</a> =3D c.parent<br>join =
grantparent w on <a href=3D"http://w.id">w.id</a> =3D s.parent<br>where <a =
href=3D"http://w.name">w.name</a> =3D $1<br>and <a href=3D"http://s.name">s=
.name</a> =3D $2<br>and <a href=3D"http://c.name">c.name</a> =3D $3<br><br>=
</div><div># new &quot;aliased name&quot; query</div><div>select ...<br>fro=
m child c	=C2=A0	=C2=A0<br>join parent s on <a href=3D"http://s.id">s.id</a=
> =3D c.parent	=C2=A0	=C2=A0<br>join grantparent w on <a href=3D"http://w.i=
d">w.id</a> =3D s.parent	=C2=A0	=C2=A0<br>join unnest($3::text[]) with ordi=
nality as aliases(name, ord) on <a href=3D"http://c.name">c.name</a> =3D <a=
 href=3D"http://aliases.name">aliases.name</a>	=C2=A0	=C2=A0<br>where <a hr=
ef=3D"http://w.name">w.name</a> =3D $1	=C2=A0	=C2=A0<br>and <a href=3D"http=
://s.name">s.name</a> =3D $2	=C2=A0	=C2=A0<br>order by aliases.ord	=C2=A0	=
=C2=A0<br>limit 1</div><div><br></div><div>Notice the limit 1, i.e. this is=
 still a scalar query, since name or (parent, name) are UNIQUE,</div><div>a=
nd the fact the alias query is ordered, the first match in alias order is &=
quot;preferred&quot;.</div><div><br></div><div>Given the above, it is obvio=
us that if we stick the exact name in front of its aliases, and use only th=
e 2nd query, this is functionally equivalent (if it isn&#39;t, please let u=
s know!).</div><div><br></div><div>And from a maintenance perspective, not =
doubling our queries sounds like a good thing.</div><div><br></div><div>But=
 then, I do worry about performance. Will the second more complex query be =
planned just as well of the 1st one?</div><div><br></div><div>We have two t=
ypes of clients (apps) for that API:</div><div>* The first type never relie=
s on fuzzy-find.</div><div>* While the second type relies extensively on it=
.</div><div>Thus I don&#39;t want to penalize the first type, over the seco=
nd.</div><div><br></div><div>I&#39;d appreciate an expert opinion on planni=
ng (Tom? Andrew?),</div><div>in the context of unnest+order by ordinal+limi=
t 1 with UNIQUE constraints,</div><div>versus the simpler 3-way-join with e=
quality constraints, to base my decision on.</div><div><br></div><div>Thank=
s, --DD</div><div><br></div><div>PS: We are v16+ based.</div></div>

--0000000000002d5d8b062ee037eb--