Re: Return rows in input array's order? - Mailing list pgsql-general

From Dominique Devienne
Subject Re: Return rows in input array's order?
Date
Msg-id CAFCRh-9Y+fJQuGE8K6LVq=p633aoCJUhRQMGw+tJud7VFBmsnQ@mail.gmail.com
Whole thread Raw
In response to Re: Return rows in input array's order?  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
List pgsql-general
On Wed, May 10, 2023 at 1:08 PM Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>> "Dominique" == Dominique Devienne <ddevienne@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

pgsql-general by date:

Previous
From: Inzamam Shafiq
Date:
Subject: Patroni Issue
Next
From: Evgeny Morozov
Date:
Subject: Re: "PANIC: could not open critical system index 2662" - twice