Re: An issue in remote query optimization

Sorry for the confusion.
ANALYZE works for the foreign table 'foreign_numbers'.
test=# analyze foreign_numbers;
ANALYZE
test=#

On Tue, Jan 31, 2017 at 5:04 AM, Ashutosh Bapat <
ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:

> On Tue, Jan 31, 2017 at 5:23 PM, Abbas Butt <abbas(dot)butt(at)enterprisedb(dot)com>
> wrote:
> >
> >
> > On Tue, Jan 31, 2017 at 3:15 AM, Etsuro Fujita <
> fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
> > wrote:
> >>
> >> On 2017/01/31 19:53, Abbas Butt wrote:
> >>>
> >>> On Tue, Jan 31, 2017 at 2:25 AM, Etsuro Fujita
> >>> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp <mailto:fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>>
> wrote:
> >>> On 2017/01/31 18:24, Abbas Butt wrote:
> >>
> >>
> >>> Postgres_fdw optimizes remote queries by pushing down the where
> >>> clause.
> >>> This feature does not work consistently when the query is
> >>> executed from
> >>> within a pl/pgsql function. The optimization works when the
> >>> function
> >>> executes the query for the first 5 times, and fails afterwards.
> >>
> >>
> >>> I understand that this is because PostgreSQL starts using
> >>> generic plan
> >>> with pulled up where clause after the 5th invocation hoping
> that
> >>> it
> >>> would be faster since we have skiped planning the query on each
> >>> invocation, but in this case this decision is causing the query
> >>> to slow
> >>> down.
> >>
> >>
> >>> How should we fix this problem?
> >>
> >>
> >>> ANALYZE for the foreign table doesn't work?
> >>
> >>
> >>> No.
> >>>
> >>> analyze ts.tickets;
> >>> WARNING: skipping "tickets" --- cannot analyze this foreign table
> >>> ANALYZE
> >>
> >>
> >> How the foreign table ts.tickets is defined?
> >
> >
> > test=# \d ts.tickets
> > Foreign table "ts.tickets"
> > Column | Type | Modifiers | FDW Options
> > --------+---------+-----------+-------------
> > id | integer | not null |
> > Server: mysql_server
> > FDW Options: (dbname 'msql_test_db', table_name 'tickets')
> >
> > Its a foreign table, referring to table 'tickets' defined on MySQL.
> >
> Isn't your original complaint about postgres_fdw? You can not tickets,
> which is a mongo_fdw foreign table, is probably because mongo_fdw has
> not implemented analyze FDW routine.
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>

--
--
*Abbas*
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>