Thread: Fetching multiple rows in single round trip
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
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 filteringone. 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 filteringone. Thanks for the reply. The number of widgets is variable, but should not be higher than about 20 in the worst case, with10 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 befiltering one. > > > Thanks for the reply. The number of widgets is variable, but should not be higher than about 20 in the worst case, with10 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 bestin 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