On May 18, 2012, at 13:46, Jon Smark <jon.smark@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
