On Wed, May 10, 2023 at 1:08 PM Andrew Gierth <and...@tao11.riddles.org.uk> wrote:
> >>>>> "Dominique" == Dominique Devienne <ddevie...@gmail.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
