Horton
Menu

Thread

  1. Keep specialized query pairs, or use single more general but more complex one

    Dominique Devienne <ddevienne@gmail.com> — 2025-02-24T09:46:17Z

    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.

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

    Rob Sargent <robjsargent@gmail.com> — 2025-02-24T14:51:21Z

    API:
> * The first type never relies on fuzzy-find.

Then have that app type use the simple, fast query. After all each app/code is making an explicit choice which yesterday was the same and tomorrow will be different.

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

    Dominique Devienne <ddevienne@gmail.com> — 2025-02-24T15:27:12Z

    On Mon, Feb 24, 2025 at 3:51 PM Rob Sargent <robjsargent@gmail.com> wrote:

> API:
> > * The first type never relies on fuzzy-find.
>
> Then have that app type use the simple, fast query.
>

I'm sorry, but that's neither helpful, nor relevant. --DD

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

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-02-24T16:39:15Z

    On Mon, Feb 24, 2025 at 4:46 AM Dominique Devienne <ddevienne@gmail.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"?


Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

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

    Dominique Devienne <ddevienne@gmail.com> — 2025-02-24T16:50:24Z

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

> On Mon, Feb 24, 2025 at 4:46 AM Dominique Devienne <ddevienne@gmail.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

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

    Greg Sabino Mullane <htamfids@gmail.com> — 2025-02-24T17:36:10Z

    On Mon, Feb 24, 2025 at 11:50 AM Dominique Devienne <ddevienne@gmail.com>
wrote:

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

Thanks, I understand it now. While the unnest will create a different plan,
it should fundamentally be the same. The join order will not matter. The
only consideration is if the unnest list grows very, very large, which
seems unlikely given your situation. And yes, it should be fine to set the
first name as the leading item in the array and only run a single query for
both cases.

As always, it's best to test on your data and your exact queries, but from
here it seems sane.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support