Re: Return rows in input array's order?

On Wed, May 10, 2023 at 1:08 PM Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:

> >>>>> "Dominique" == Dominique Devienne <ddevienne(at)gmail(dot)com> writes:
> Dominique> I assume that if the PK is composite, and I pass the PK
> Dominique> tuples as separate same-cardinality "parallel" arrays, I can
> Dominique> "zip" those arrays back via a multi-join using their
> Dominique> ordinals before joining with the composite-PK table?
>
> You don't need to, because unnest can do it for you:
>

Wow, that's fantastic. Thanks!

Dominique> PS: I guess the ideal plan depends both on the table itself,
> Dominique> but also the cardinality of the array(s) passed in as bind
> Dominique> variable(s) at runtime to the prepared statement, right?
>
> Yes, in the sense that what matters is what proportion of the table is
> being fetched. Is it likely that you'll be passing in very long lists of
> ids relative to the table size?
>

I'm writing a new mid-tier implementation of an existing protocol / API,
so I don't decide "how much" the clients ask for. The API certainly allows
a small request to return a large amount data / rows from several tables.

Although the queries using list of IDs (SKs) as where-clauses are typically
internal implementation details, and not per-se client requests.

> Dominique> But from past posts, I got the impression the plan of a
> Dominique> prepared statement is "fixed", and does not depend on "bind
> Dominique> peeking" like it can in Oracle, to take those bound array's
> Dominique> cardinality into account at PQexecPrepared-time?
>
> It's a bit more complicated than that and it often depends on what the
> client library is doing; many clients don't do a protocol-level named
> prepare until after a client-side prepared statement has been used
> several times; and even after doing a named prepare, the planner won't
> try a generic plan until after several more uses.
>

I'm in C++ using my own thin wrapper on top of libpq directly.

And I do tend to PQprepare extensively, since my mid-tier implementation
is long lived and many queries will be used many many times. This used to
matter a lot with Oracle OCI, but maybe lack of bind-peeking to re-plan or
select among a choices of plan makes always preparing statements a bad
choice with PostgreSQL / LibPQ?

> We distinguish between "generic plan" and "custom plan"; a generic plan
> is one produced without knowledge of the parameter values and must work
> for any parameter values, while a custom plan only works for one
> specific set of parameter values and can't usually be re-used. Custom
> plans take the parameter values into account both for estimation and for
> constant-folding optimizations. Generic plans are used after about the
> 5th use of a statement if the cost of the generic plan isn't worse than
> the average costs of the custom plans from the previous uses, plus a
> fudge factor representing the CPU cost of custom planning.
>

Indeed it's more complicated than I thought... Interesting though.

> The planning hazard in cases like this is that when doing a generic
> plan, the planner has no idea at all what the array cardinalities will
> be; it doesn't try and cache information like that from the custom
> plans. So it will make a zeroth-order approximation (i.e. a constant)
> derived by the time-honoured method of rectal extraction, and this may
> make the generic plan look a lot cheaper than it should.
>

Funny colorful language :). Thanks again, you've been tremendously helpful.
--DD