Dear postgresql-general,
What would be the best way to fetch in a single round trip a set of table rows?
To clarify, suppose I have a 'widgets' table with columns 'wid' and 'data',
and I wish to retrieve all rows that belong to the client side array $targets.
Obviously one solution would be to loop on the client-side, with each iteration
fetching one row. This however entails many round trips in the client <->
postmaster communication, which is undesirable for performance reasons.
Therefore, I would rather tell the PostgreSQL server to give me all rows
whose wid belongs in a given set.
I can think of two solutions:
1) "SELECT wid, data FROM widgets WHERE wid IN $targets"
2) "SELECT wid, data FROM widgets WHERE ARRAY [wid] <@ $targets"
Is there another (better) approach I'm missing? Also, is there any significant
performance difference for PostgreSQL between solutions 1 and 2? (Solution
1 seems more efficient, though solution 2 is actually a better fit for the
client-side bindings I'm using).
Thanks in advance!
Jon