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

List:       postgresql-general
Subject:    Re: Foreign table performance issue / PostgreSQK vs. ORACLE
From:       Jeremy Smith <jeremy () musicsmith ! net>
Date:       2021-01-30 11:28:06
Message-ID: CAM8SmLVioTeqR6GvhVpW3mtZ7eKUiNo+58qo9kbh7k0otdTNmQ () mail ! gmail ! com
[Download RAW message or body]

>
>
> Could there be some tuning option in PostgreSQL to make queries via
> foreign tables faster (e.g. I heard about option fetch_size)?
>

fetch_size can make a difference, but it won't change a query that takes
hours into a query that takes seconds.  The default is likely too low,
though.

Have you analyzed the foreign table or set use_remote_estimate?  With no
statistics, postgres may just be pulling the entire remote table.

As others have mentioned, it would be useful to see your query.  There may
be obvious issues, such as functions in the predicate, but without the
query, we can only guess.

>

[Attachment #3 (text/html)]

<div dir="auto"><div class="gmail_quote" dir="auto"><blockquote class="gmail_quote" \
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div \
lang="EN-GB" link="#0563C1" vlink="#954F72"><div \
class="m_-6771660398609724022WordSection1"><p class="MsoNormal"><br></p> <p \
class="MsoNormal"><span style="font-family:Verdana,sans-serif">Co</span><span \
style="font-family:Verdana,sans-serif">uld there be some tuning option  in PostgreSQL \
to make queries via foreign tables faster (e.g. I heard about option \
fetch_size)?</span></p></div></div></blockquote></div><div dir="auto"><br></div><div \
dir="auto">fetch_size can make a difference, but it won&#39;t change a query that \
takes hours into a query that takes seconds.   The default is likely too low, \
though.</div><div dir="auto"><br></div><div dir="auto">Have you analyzed the foreign \
table or set use_remote_estimate?   With no statistics, postgres may just be pulling \
the entire remote table.    </div><div dir="auto"><br></div><div dir="auto">As others \
have mentioned, it would be useful to see your query.   There may be obvious issues, \
such as functions in the predicate, but without the query, we can only \
guess.</div><div class="gmail_quote" dir="auto"><blockquote class="gmail_quote" \
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">

</blockquote></div></div>



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