Re: Making clausesel.c Smarter

On 3 April 2017 at 20:59, David Rowley <david.rowley@2ndquadrant.com> wrote:
> Updated patch attached.

I did a little benchmarking on this to learn how planning time is affected.

Master = 9fa6e08d4a16f9b0461743cff35781e16308c106
Patched = 9fa6e08d4a16f9b0461743cff35781e16308c106 +
smarter_clausesel_2017-04-03.patch

Config:

All standard

Setup:
create table abcd (a int, b int, c int, d int, arr int[]);

Tests:

Test1: explain select * from abcd where a = 1 and b = 1 and c = 1 and d = 1;
Test2: explain select * from abcd where a >= 1 and a <= 10 and b = 1 and c = 1;
Test3: explain select * from abcd a1 inner join abcd a2 on a1.a = a2.a
where a1.b = 1 and a2.b = 1 and a1.c = 1 and a2.c = 1;
Test4: (output of) select 'explain select * from abcd where ' ||
string_agg('arr[' || x::Text || '] = 1',' AND ') from
generate_series(0,999) x;
Test5: (output of) select 'explain select * from abcd where ' ||
string_agg('arr[' || x::Text || '] > 1',' AND ') from
generate_series(0,999) x;
Test6: explain select * from abcd;

Tests were performed running pgbench -T 60 -n

Raw Results:

Master

Test 1
tps = 6993.677785 (excluding connections establishing)
tps = 7072.796544 (excluding connections establishing)
tps = 6877.428676 (excluding connections establishing)

Test2
tps = 6698.456888 (excluding connections establishing)
tps = 7117.481643 (excluding connections establishing)
tps = 7053.070973 (excluding connections establishing)

Test 3
tps = 5137.229119 (excluding connections establishing)
tps = 5091.548602 (excluding connections establishing)
tps = 5175.683112 (excluding connections establishing)

Test 4
tps = 23.816356 (excluding connections establishing)
tps = 27.069840 (excluding connections establishing)
tps = 27.398995 (excluding connections establishing)

Test 5
tps = 54.209078 (excluding connections establishing)
tps = 54.022850 (excluding connections establishing)
tps = 54.076590 (excluding connections establishing)

Test 6
tps = 9328.134781 (excluding connections establishing)
tps = 9181.898271 (excluding connections establishing)
tps = 9402.993637 (excluding connections establishing)

Patched

Test 1
tps = 6560.160644 (excluding connections establishing)
tps = 6714.294344 (excluding connections establishing)
tps = 6901.381552 (excluding connections establishing)

Test 2
tps = 6971.106747 (excluding connections establishing)
tps = 6591.363565 (excluding connections establishing)
tps = 6921.572060 (excluding connections establishing)

Test 3
tps = 4784.606871 (excluding connections establishing)
tps = 4980.609259 (excluding connections establishing)
tps = 4954.237649 (excluding connections establishing)

Test 4
tps = 19.374184 (excluding connections establishing)
tps = 19.836406 (excluding connections establishing)
tps = 19.047484 (excluding connections establishing)

(perf of test 4)
+   40.20%    40.20%  postgres  postgres            [.] equal.part.18
+   29.57%     0.00%  postgres  [unknown]           [.] 0xffffffff00000017
+   25.15%     0.00%  postgres  [unknown]           [.] 0x00000000ffffffff
+   20.29%     0.00%  postgres  [unknown]           [k] 0000000000000000
+   12.36%    12.36%  postgres  postgres            [.] _equalList
+   11.25%     0.00%  postgres  [unknown]           [.] 0x0000558ed98fb148
+    8.53%     8.53%  postgres  postgres            [.] _equalArrayRef
+    6.22%     6.22%  postgres  postgres            [.] process_equivalence    5.93%     5.93%  postgres  postgres
     [.] get_eclass_for_sort_expr
 
+    2.75%     0.00%  postgres  [kernel.kallsyms]   [k] 0xffffffffaf06b772

Test 5
tps = 51.691502 (excluding connections establishing)
tps = 51.296724 (excluding connections establishing)
tps = 51.366643 (excluding connections establishing)

Test 6
tps = 9528.363239 (excluding connections establishing)
tps = 9478.202725 (excluding connections establishing)
tps = 9346.753395 (excluding connections establishing)

Result Comparison

Master median tps Patch median tps comparison
Test 1 6993.7 6714.3 104.16%
Test 2 7053.1 6921.6 101.90%
Test 3 5137.2 4954.2 103.69%
Test 4 27.1 19.4 139.72%
Test 5 54.1 51.4 105.28%
Test 6 9328.1 9478.2 98.42%

Results Analyzed:

Test 1 has caused planning to slow down 4.16%. There's quite a bit of
noise from the results, but I think this indicates there is some
overhead to having to add items to the cslist and searching the cslist
when new quals are seen.

Test 2 has a lesser slowdown than test 1, as this test will excercise
the existing rqlist caching in master too. Patched does a little more
work adding the equality condition to the list too.

Test 3 similar to test 1

Test 4 adds quite an overhead and causes 0.5 million comparisons to
find the expressions in the cslist.

Test 5 shows less overhead than test 4 since the Master code has to
also do the expression caching and searching.

Test 6 is a control test


-- David Rowley                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services