Fetching multiple rows in single round trip

On May 18, 2012, at 13:46, Jon Smark <jon.smark@yahoo.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.

Hi,

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

Thanks for the reply.  The number of widgets is variable, but should not be higher than about 20 in the worst case, with 10 being
a more average number.  Which solution should I opt for in these circumstances?

Cheers,
Jon

On May 18, 2012, at 17:06, Jon Smark <jon.smark@yahoo.com> wrote:

Hi,

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

Thanks for the reply. The number of widgets is variable, but should not be higher than about 20 in the worst case, with 10 being
a more average number. Which solution should I opt for in these circumstances?

Cheers,
Jon

You should just try both and see which one performs better in your specific case. I do not know generally which one is best in theory.

David J.

On 2012-05-18, Jon Smark <jon.smark@yahoo.com> wrote:

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"

my testing indicates that from 1 is significantly faster than from 2
postgres (8.4) cant factor "ARRAY[wid] <@" sufficiently to see how it
can use an index to help find the answer where as it can understand
"wid IN" and use an index on wid to find the rows it needs.

--
⚂⚃ 100% natural