Re: Optimization for updating foreign tables in Postgres FDW

(2014/08/26 12:20), Etsuro Fujita wrote:
> (2014/08/25 21:58), Albe Laurenz wrote:
>> I played with it, and apart from Hanada's comments I have found the
>> following:
>>
>> test=> EXPLAIN (ANALYZE, VERBOSE) UPDATE rtest SET val=NULL WHERE id > 3;
>> QUERY PLAN
>> ----------------------------------------------------------------------------------------------------------------------------------
>>
>> Update on laurenz.rtest (cost=100.00..14134.40 rows=299970
>> width=10) (actual time=0.005..0.005 rows=0 loops=1)
>> -> Foreign Scan on laurenz.rtest (cost=100.00..14134.40
>> rows=299970 width=10) (actual time=0.002..0.002 rows=299997 loops=1)
>> Output: id, val, ctid
>> Remote SQL: UPDATE laurenz.test SET val = NULL::text WHERE
>> ((id > 3))
>> Planning time: 0.179 ms
>> Execution time: 3706.919 ms
>> (6 rows)
>>
>> Time: 3708.272 ms
>>
>> The "actual time" readings are surprising.
>> Shouldn't these similar to the actual execution time, since most of
>> the time is spent
>> in the foreign scan node?
>
> I was also thinkng that this is confusing to the users. I think this is
> because the patch executes the UPDATE/DELETE statement during
> postgresBeginForeignScan, not postgresIterateForeignScan, as you
> mentioned below:
>
>> Reading the code, I noticed that the pushed down UPDATE or DELETE
>> statement is executed
>> during postgresBeginForeignScan rather than during
>> postgresIterateForeignScan.

> I'll modify the patch so as to execute the statement during
> postgresIterateForeignScan.

Done.

>> It is not expected that postgresReScanForeignScan is called when the
>> UPDATE/DELETE
>> is pushed down, right? Maybe it would make sense to add an assertion
>> for that.
>
> IIUC, that is right. As ModifyTable doesn't support rescan currently,
> postgresReScanForeignScan needn't to be called in the update pushdown
> case. The assertion is a good idea. I'll add it.

Done.

You can find the updated version of the patch at

http://www.postgresql.org/message-id/53FFFA50.6020007@lab.ntt.co.jp

Thanks,

Best regards,
Etsuro Fujita