On Tue, Jan 31, 2017 at 3:15 AM, Etsuro Fujita <fujita.ets...@lab.ntt.co.jp> wrote:
> On 2017/01/31 19:53, Abbas Butt wrote: > >> On Tue, Jan 31, 2017 at 2:25 AM, Etsuro Fujita >> <fujita.ets...@lab.ntt.co.jp <mailto:fujita.ets...@lab.ntt.co.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. > Best regards, > Etsuro Fujita > > > -- -- *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>