Re: Optimization for updating foreign tables in Postgres FDW

Hi Fujita-san,

Here is a new review result from Eitoku-san.

2014-07-25 16:30 GMT+09:00 Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>:
> (2014/07/24 18:30), Shigeru Hanada wrote:
> I'm not sure that I understand your question correctly, but the reason for
> that is because foreign tables cannot have INSTEAD OF triggers.

Now I see the reason, but then I worry (though it unlikely happens) a
case that new trigger type might be added in future. The code says
that "only BEFORE and AFTER triggers are unsafe for direct update",
but it would be more safe to code that "any trigger other than event
trigger is unsafe for direct update".

>> We found that this patch speeds up DELETE case remarkably, as you
>> describe above, but we saw only less than 2x speed on UPDATE cases.
>> Do you have any numbers of UPDATE cases?
>
>
> Here is the result for an UPDATE case.

Hmm, performance gain on UPDATE cases seems similar to our results,
except planning times. In your environment the patch reduces planning
time too, but we got longer planning times with your patch (in only
once in six trial, we got shorter planning time than average of
patched version). Could you try multiple times on your environment?

> I think that the precise effect of this optimization for DELETE/UPDATE would
> depend on eg, data, queries (inc. w/ or w/o RETRUNING clauses) and
> server/network performance. Could you tell me these information about the
> UPDATE evaluation?

I tried on a CentOS 6.5 on VMware on a Note PC with Core i3 1.17GHz,
2.0GB memory and single HDD, so the performance is poor.

The SQLs used for performance test are quite simple, update 10
thousands rows at a time, and repeat it for different section of the
table for six times. The definition of foreign table ft is same as
the one in your case.

EXPLAIN ANALYZE VERBOSE UPDATe ft SET data = 'abcdefg' WHERE id >= 0
AND id < 10000;
EXPLAIN ANALYZE VERBOSE UPDATe ft SET data = 'abcdefg' WHERE id >=
10000 AND id < 20000;
EXPLAIN ANALYZE VERBOSE UPDATe ft SET data = 'abcdefg' WHERE id >=
20000 AND id < 30000;
EXPLAIN ANALYZE VERBOSE UPDATe ft SET data = 'abcdefg' WHERE id >=
30000 AND id < 40000;
EXPLAIN ANALYZE VERBOSE UPDATe ft SET data = 'abcdefg' WHERE id >=
40000 AND id < 50000;
EXPLAIN ANALYZE VERBOSE UPDATe ft SET data = 'abcdefg' WHERE id >=
50000 AND id < 60000;

>> Some more random thoughts:
>>
>> * Naming of new behavior
>> You named this optimization "Direct Update", but I'm not sure that
>> this is intuitive enough to express this behavior. I would like to
>> hear opinions of native speakers.

Update push-down seems nice with according to others.

--
Shigeru HANADA