Re: Optimization for updating foreign tables in Postgres FDW

Hi Hanada-san,

Thank you for the answer.

(2014/08/04 19:36), Shigeru Hanada wrote:
> 2014-07-25 16:30 GMT+09:00 Etsuro Fujita :
>> (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".

Yeah, I've revised the comment for that in the updated version of the 
patch I sent in just now.  Could you check it?

>>> 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?

> 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?

No.  Is the overhead so large that it cannot be ignored?

>> 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;

OK  I also will evaluate the performance under the same workloads.

>>> 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.

The name has been changed in the updated version.

Thanks,

Best regards,
Etsuro Fujita