Re: Fetching multiple rows in single round trip

On May 18, 2012, at 13:46, Jon Smark <jon(dot)smark(at)yahoo(dot)com> wrote:

> 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
>
>

...WHERE wid = ANY(string_to_array(?,';'))

where the ? is a parameter that you replace with a semi-colon delimited listing of widget IDs

Performance depends on specifics you have not provided, especially the expected number of widgets you are going to be filtering one.

David J.