[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Implementing foreign data wrappers and avoiding n+1 querying
From:       Brad White <b55white () gmail ! com>
Date:       2022-12-23 16:32:46
Message-ID: CAA_1=90KfVrBHWYeRSMHVbyTezwoF0M3HX7cqMWYV=MoT3rY+g () mail ! gmail ! com
[Download RAW message or body]

We had a similar situation in a completely different context.
Our eventual solution was to fire off a request as soon as one came in.
Then we batched further requests until the first returned. Whenever a
request returned, we sent any pending requests.
Any single request not sent immediately was slowed slightly, but overall
the system was faster because of the reduced traffic.

Brad

On Thu, Dec 22, 2022, 6:51 AM David Gilman <davidgilman1@gmail.com> wrote:

> I apologize that my post was not super clear, I am thinking about
> implementing a fdw from scratch, and the target database is one of those
> NoSQL databases where you have to send JSON over a HTTP connection for each
> query.
>
> I have reviewed the postgres fdw code to see how it works and to see
> what's possible. Although it probably wouldn't benefit as much from this
> sort of thing (yay to postgres' design!) It could possibly still benefit a
> bit, which makes me wonder if it can't be done with the current planner
> nodes it might be a worthy improvement to add support for this.
>
> On Wed, Dec 21, 2022, 10:57 PM David Rowley <dgrowleyml@gmail.com> wrote:
>
>> On Thu, 22 Dec 2022 at 13:31, David Gilman <davidgilman1@gmail.com>
>> wrote:
>> >
>> > When a fdw table participates in query planning and finds itself as
>> > part of a join it can output a parameterized path. If chosen, Postgres
>> > will dutifully call the fdw over and over via IterateForeignScan to
>> > fetch matching tuples. Many fdw extensions do network traffic, though,
>> > and it would be beneficial to reduce the total number of queries done
>> > or network connections established.
>>
>> Sounds like you might be looking for fdw_startup_cost [1].
>>
>> David
>>
>> [1] https://www.postgresql.org/docs/current/postgres-fdw.html
>>
>

[Attachment #3 (text/html)]

<div dir="auto">We had a similar situation in a completely different context.<div \
dir="auto">Our eventual solution was to fire off a request as soon as one came in. \
Then we batched further requests until the first returned. Whenever a request \
returned, we sent any pending requests.  </div><div dir="auto">Any single request not \
sent immediately was slowed slightly, but overall the system was faster because of \
the reduced traffic.  </div><div dir="auto"><br></div><div \
dir="auto">Brad</div></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Thu, Dec 22, 2022, 6:51 AM David Gilman &lt;<a \
href="mailto:davidgilman1@gmail.com">davidgilman1@gmail.com</a>&gt; \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="auto">I apologize that my \
post was not super clear, I am thinking about implementing a fdw from scratch, and \
the target database is one of those NoSQL databases where you have to send JSON over \
a HTTP connection for each query.<div dir="auto"><br></div><div dir="auto">I have \
reviewed the postgres fdw code to see how it works and to see what&#39;s possible. \
Although it probably wouldn&#39;t benefit as much from this sort of thing (yay to \
postgres&#39; design!) It could possibly still benefit a bit, which makes me wonder \
if it can&#39;t be done with the current planner nodes it might be a worthy \
improvement to add support for this.</div></div><br><div class="gmail_quote"><div \
dir="ltr" class="gmail_attr">On Wed, Dec 21, 2022, 10:57 PM David Rowley &lt;<a \
href="mailto:dgrowleyml@gmail.com" target="_blank" \
rel="noreferrer">dgrowleyml@gmail.com</a>&gt; wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">On Thu, 22 Dec 2022 at 13:31, David Gilman &lt;<a \
href="mailto:davidgilman1@gmail.com" rel="noreferrer noreferrer" \
target="_blank">davidgilman1@gmail.com</a>&gt; wrote:<br> &gt;<br>
&gt; When a fdw table participates in query planning and finds itself as<br>
&gt; part of a join it can output a parameterized path. If chosen, Postgres<br>
&gt; will dutifully call the fdw over and over via IterateForeignScan to<br>
&gt; fetch matching tuples. Many fdw extensions do network traffic, though,<br>
&gt; and it would be beneficial to reduce the total number of queries done<br>
&gt; or network connections established.<br>
<br>
Sounds like you might be looking for fdw_startup_cost [1].<br>
<br>
David<br>
<br>
[1] <a href="https://www.postgresql.org/docs/current/postgres-fdw.html" \
rel="noreferrer noreferrer noreferrer" \
target="_blank">https://www.postgresql.org/docs/current/postgres-fdw.html</a><br> \
</blockquote></div> </blockquote></div>



[prev in list] [next in list] [prev in thread] [next in thread]