Re: [HACKERS] An issue in remote query optimization - Mailing list pgsql-hackers

From Abbas Butt
Subject Re: [HACKERS] An issue in remote query optimization
Date
Msg-id CALtH27cBAzWwB0z7VM4WeO0YJX9khpCGUN32qwUGMC87rAXocQ@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] An issue in remote query optimization  (Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>)
List pgsql-hackers
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.bapat@enterprisedb.com> wrote:
On Tue, Jan 31, 2017 at 5:23 PM, Abbas Butt <abbas.butt@enterprisedb.com> wrote:
>
>
> On Tue, Jan 31, 2017 at 3:15 AM, Etsuro Fujita <fujita.etsuro@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.etsuro@lab.ntt.co.jp <mailto:fujita.etsuro@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.
>
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
Skype ID: gabbasb
www.enterprisedb.com

Follow us on Twitter

@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers and more

pgsql-hackers by date:

Previous
From: Konstantin Knizhnik
Date:
Subject: Re: [HACKERS] Deadlock in XLogInsert at AIX
Next
From: Alvaro Herrera
Date:
Subject: Re: [HACKERS] Patch: Write Amplification Reduction Method (WARM)