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 <fujita.ets...@lab.ntt.co.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".
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
