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