public inbox for [email protected]help / color / mirror / Atom feed
Keep specialized query pairs, or use single more general but more complex one 3+ messages / 2 participants [nested] [flat]
* Keep specialized query pairs, or use single more general but more complex one @ 2025-02-24 09:46 Dominique Devienne <[email protected]> 2025-02-24 16:39 ` Re: Keep specialized query pairs, or use single more general but more complex one Greg Sabino Mullane <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Dominique Devienne @ 2025-02-24 09:46 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Keep specialized query pairs, or use single more general but more complex one 2025-02-24 09:46 Keep specialized query pairs, or use single more general but more complex one Dominique Devienne <[email protected]> @ 2025-02-24 16:39 ` Greg Sabino Mullane <[email protected]> 2025-02-24 16:50 ` Re: Keep specialized query pairs, or use single more general but more complex one Dominique Devienne <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Greg Sabino Mullane @ 2025-02-24 16:39 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: [email protected]; [email protected] On Mon, Feb 24, 2025 at 4:46 AM Dominique Devienne <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Keep specialized query pairs, or use single more general but more complex one 2025-02-24 09:46 Keep specialized query pairs, or use single more general but more complex one Dominique Devienne <[email protected]> 2025-02-24 16:39 ` Re: Keep specialized query pairs, or use single more general but more complex one Greg Sabino Mullane <[email protected]> @ 2025-02-24 16:50 ` Dominique Devienne <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Dominique Devienne @ 2025-02-24 16:50 UTC (permalink / raw) To: Greg Sabino Mullane <[email protected]>; +Cc: [email protected]; [email protected] On Mon, Feb 24, 2025 at 5:39 PM Greg Sabino Mullane <[email protected]> wrote: > On Mon, Feb 24, 2025 at 4:46 AM Dominique Devienne <[email protected]> > 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-02-24 16:50 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-02-24 09:46 Keep specialized query pairs, or use single more general but more complex one Dominique Devienne <[email protected]> 2025-02-24 16:39 ` Greg Sabino Mullane <[email protected]> 2025-02-24 16:50 ` Dominique Devienne <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox