Re: Use virtual tuple slot for Unique node

On Sat, 23 Sept 2023 at 03:15, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
> So not a win in this case. Could you peek at the outer slot type, and
> use the same kind of slot for the Unique's result? Or some more
> complicated logic, like use a virtual slot if all the values are
> pass-by-val? I'd also like to keep this simple, though...
>
> Would this kind of optimization make sense elsewhere?

There are a few usages of ExecGetResultSlotOps(). e.g ExecInitHashJoin().

If I adjust the patch to:

- ExecInitResultTupleSlotTL(&uniquestate->ps, &TTSOpsMinimalTuple);
+ ExecInitResultTupleSlotTL(&uniquestate->ps,
+
ExecGetResultSlotOps(outerPlanState(uniquestate),
+
NULL));

Then I get the following performance on my Zen2 machine.

Test 1

drop table if exists t;
create table t(a int, b int);
insert into t select x,x from generate_series(1,1000000)x;
create index on t (a,b);
vacuum analyze t;

explain (analyze, timing off) select distinct a,b from t;

Master:
Execution Time: 149.669 ms
Execution Time: 149.019 ms
Execution Time: 151.240 ms

Patched:
Execution Time: 96.950 ms
Execution Time: 94.509 ms
Execution Time: 93.498 ms

Test 2

drop table if exists t;
create table t(a text, b text);
insert into t select x::text,x::text from generate_series(1,1000000)x;
create index on t (a,b);
vacuum analyze t;

explain (analyze, timing off) select distinct a,b from t;

Master:
Execution Time: 185.282 ms
Execution Time: 178.948 ms
Execution Time: 179.217 ms

Patched:
Execution Time: 141.031 ms
Execution Time: 141.136 ms
Execution Time: 142.163 ms

Test 3

set enable_hashagg=off;
explain (analyze, timing off) select distinct g::text, 'a', 'b',
'c','d', 'e','f','g','h' from generate_series(1, 50000) g;

Master:
Execution Time: 87.599 ms
Execution Time: 87.721 ms
Execution Time: 87.635 ms

Patched:
Execution Time: 83.449 ms
Execution Time: 84.314 ms
Execution Time: 86.239 ms

David