Fetching multiple rows in single round trip

From: Jon Smark <jon(dot)smark(at)yahoo(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Fetching multiple rows in single round trip
Date: 2012-05-18 17:46:28
Message-ID: 1337363188.90482.YahooMailNeo@web112806.mail.gq1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Aaron Burnett 2012-05-18 18:01:11 Re: odd intermittent query hanging issue
Previous Message John R Pierce 2012-05-18 17:18:16 Re: cascade replication and multiple primary_conninfo