On Mon, Feb 10, 2014 at 8:59 PM, Alexander Korotkov <aekorot...@gmail.com> wrote: > Done. Patch is splitted.
Thanks! I think the 1st patch now has a bug in initial_cost_mergejoin; you still pass the "presorted_keys" argument to cost_sort, making it calculate a partial sort cost, but generated plans never use partial sort. I think 0 should be passed instead. Patch attached, needs to be applied on top of partial-sort-basic-1 and then reverse-applied on partial-sort-merge-1. With partial-sort-basic-1 and this fix on the same test suite, the planner overhead is now a more manageable 0.5% to 1.3%; one test is faster by 0.5%. Built with asserts disabled, ran on Intel i5-3570K. In an effort to reduce variance, I locked the server and pgbench to a single CPU core (taskset -c 3), but there are still noticeable run-to-run differences, so these numbers are a bit fuzzy. The faster result is definitely not a fluke, however; it happens every time. > On Mon, Feb 10, 2014 at 2:33 PM, Marti Raudsepp <ma...@juffo.org> wrote: >> AFAICT this only happens once per plan and the overhead is O(n) to the >> number of pathkeys? I was of course wrong about that, it also adds extra overhead when iterating over the paths list. ---- Test "taskset -c 3 run2.sh" from https://github.com/intgr/benchjunk/tree/master/partial_sort Overhead percentages (between best of each 3 runs): join5.sql 0.7 star5.sql 0.8 line5.sql 0.5 lim_join5.sql -0.5 lim_star5.sql 1.3 lim_line5.sql 0.5 limord_join5.sql 0.6 limord_star5.sql 0.5 limord_line5.sql 0.7 Raw results: git 48870dd join5.sql tps = 509.328070 (excluding connections establishing) join5.sql tps = 509.772190 (excluding connections establishing) join5.sql tps = 510.651517 (excluding connections establishing) star5.sql tps = 499.208698 (excluding connections establishing) star5.sql tps = 498.200314 (excluding connections establishing) star5.sql tps = 496.269315 (excluding connections establishing) line5.sql tps = 797.968831 (excluding connections establishing) line5.sql tps = 797.011690 (excluding connections establishing) line5.sql tps = 796.379258 (excluding connections establishing) lim_join5.sql tps = 394.946024 (excluding connections establishing) lim_join5.sql tps = 395.417689 (excluding connections establishing) lim_join5.sql tps = 395.482958 (excluding connections establishing) lim_star5.sql tps = 423.434393 (excluding connections establishing) lim_star5.sql tps = 423.774305 (excluding connections establishing) lim_star5.sql tps = 424.386099 (excluding connections establishing) lim_line5.sql tps = 733.007330 (excluding connections establishing) lim_line5.sql tps = 731.794731 (excluding connections establishing) lim_line5.sql tps = 732.356280 (excluding connections establishing) limord_join5.sql tps = 385.317921 (excluding connections establishing) limord_join5.sql tps = 385.915870 (excluding connections establishing) limord_join5.sql tps = 384.747848 (excluding connections establishing) limord_star5.sql tps = 417.992615 (excluding connections establishing) limord_star5.sql tps = 416.944685 (excluding connections establishing) limord_star5.sql tps = 418.262647 (excluding connections establishing) limord_line5.sql tps = 708.979203 (excluding connections establishing) limord_line5.sql tps = 710.926866 (excluding connections establishing) limord_line5.sql tps = 710.928907 (excluding connections establishing) 48870dd + partial-sort-basic-1.patch.gz + fix-cost_sort.patch join5.sql tps = 505.488181 (excluding connections establishing) join5.sql tps = 507.222759 (excluding connections establishing) join5.sql tps = 506.549654 (excluding connections establishing) star5.sql tps = 495.432915 (excluding connections establishing) star5.sql tps = 494.906793 (excluding connections establishing) star5.sql tps = 492.623808 (excluding connections establishing) line5.sql tps = 789.315968 (excluding connections establishing) line5.sql tps = 793.875456 (excluding connections establishing) line5.sql tps = 790.545990 (excluding connections establishing) lim_join5.sql tps = 396.956732 (excluding connections establishing) lim_join5.sql tps = 397.515213 (excluding connections establishing) lim_join5.sql tps = 397.578669 (excluding connections establishing) lim_star5.sql tps = 417.459963 (excluding connections establishing) lim_star5.sql tps = 418.024803 (excluding connections establishing) lim_star5.sql tps = 418.830234 (excluding connections establishing) lim_line5.sql tps = 729.186915 (excluding connections establishing) lim_line5.sql tps = 726.288788 (excluding connections establishing) lim_line5.sql tps = 728.123296 (excluding connections establishing) limord_join5.sql tps = 383.484767 (excluding connections establishing) limord_join5.sql tps = 383.021960 (excluding connections establishing) limord_join5.sql tps = 383.722051 (excluding connections establishing) limord_star5.sql tps = 414.138460 (excluding connections establishing) limord_star5.sql tps = 414.063766 (excluding connections establishing) limord_star5.sql tps = 416.130110 (excluding connections establishing) limord_line5.sql tps = 706.002589 (excluding connections establishing) limord_line5.sql tps = 705.632796 (excluding connections establishing) limord_line5.sql tps = 704.991305 (excluding connections establishing) Regards, Marti
commit c310b6649c4ff9929d4d26ff965b88cbe915dd6c Author: Marti Raudsepp <ma...@juffo.org> Date: Wed Feb 12 22:26:26 2014 +0200 fix cost_sort for partial sorts in initial_cost_mergejoin diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index 0e60fd7..c289924 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -2083,7 +2083,7 @@ initial_cost_mergejoin(PlannerInfo *root, JoinCostWorkspace *workspace, cost_sort(&sort_path, root, outersortkeys, - pathkeys_common(outer_path->pathkeys, outersortkeys), + 0, outer_path->startup_cost, outer_path->total_cost, outer_path_rows, @@ -2111,7 +2111,7 @@ initial_cost_mergejoin(PlannerInfo *root, JoinCostWorkspace *workspace, cost_sort(&sort_path, root, innersortkeys, - pathkeys_common(inner_path->pathkeys, innersortkeys), + 0, inner_path->startup_cost, inner_path->total_cost, inner_path_rows,
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers