From: | Abbas Butt <abbas(dot)butt(at)enterprisedb(dot)com> |
---|---|
To: | Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: An issue in remote query optimization |
Date: | 2017-01-31 11:53:21 |
Message-ID: | CALtH27dh19zXz0DGV-h89wzWC-MiPwWi0yEQ56kgHYMWeqmh7g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
> 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>
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Verite | 2017-01-31 11:57:45 | Re: Improvements in psql hooks for variables |
Previous Message | Nikita Glukhov | 2017-01-31 11:38:39 | Re: [PATCH]: fix bug in SP-GiST box_ops |