Re: Return rows in input array's order?

>>>>> "Dominique" == Dominique Devienne <ddevienne(at)gmail(dot)com> writes:

Dominique> Is it possible to maintain $1's order directly in SQL?

>> This is the correct way:
>>
>> SELECT ... FROM unnest($1) WITH ORDINALITY AS u(id,ord)
>> JOIN yourtable t ON t.id=u.id
>> ORDER BY u.ord;

Dominique> Thanks Andrew, for spelling it out for me. Appreciated.
Dominique> Also thanks to others who chimed in.

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:

SELECT ... FROM unnest($1,$2,$3) WITH ORDINALITY AS u(id1,id2,id3,ord)
JOIN yourtable t ON t.id1=u.id1
AND t.id2=u.id2
AND t.id3=u.id3
ORDER BY u.ord;

(I did actually consider using a join on the ordinal column to implement
multi-arg unnest internally, but the overhead was too much. So instead
the executor knows how to do the zipping itself.)

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?

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.

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.

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.

--
Andrew (irc:RhodiumToad)