Adding spiffs exposes further specialization opportunities
The first step of applying field specialization is to look for runtime hotspots with profiling tools. Once field specialization is applied to address the top few hotspot routines, their overhead typically will reduce significantly. Then we can start the field specialization process once again. Since the original hotspots have been addressed, other routines will rise in the profile, becoming new hotspots that present further opportunities for field specialization.
Let’s look at an example of evaluating Q6 from TPCH (scale factor 1G). We profiled the query execution using perf.
As show in the profile output below, on the stock Postgres, the number one hotspot is the table scan routine slot_deform_tuple(). (This profile concerns the number of executed instructions.)
Overhead Symbol
-------- -----------------------------
34.19% slot_deform_tuple
13.04% ExecMakeFunctionResultNoSets
5.68% heapgettup_pagemode
4.08% ExecEvalScalarVarFast
3.84% slot_getattr
...
We applied field specialization to the scan routine, exploiting the invariants associated with the schema, making this a table spiff. The profile of the execution of Q6 with the table spiff enabled is presented below. The number of executed instructions from the specialized code for the table spiff was below 2%. This over 30% reduction in instructions executed translated to 10% runtime improvement.
Overhead Symbol
-------- -----------------------------
16.35% ExecMakeFunctionResultNoSets
7.21% heapgettup_pagemode
6.85% slot_getattr
4.92% ExecEvalScalarVarFast
3.98% date_lt_timestamp
...
As we can see, the slot_deform_tuple() routine is no longer at the top. The elimination of this hotspot now brings ExecMakeFunctionResultNoSets() to the top of the profile and exposes a new hot routine: date_lt_timestamp(). We now turn our attention to the top routine, which is employed by predicate evaluation.
There are several invariants associated with the predicate-evaluation routine: the constant value present within each predicate, the ID of the column referenced by each predicate, as well as the number of operands in each predicate.
With the help of this predicate spiff, the execution profile below now shows that the overhead of the predicate evaluation routine is significantly reduced, from 16% to 9% overall, which further brings another routine to the top of the list. (Note that 0x00007f959507108f represents the address of the executed specialized code for the predicate spiff.)
Overhead Symbol
-------- -----------------------------
9.71% 0x00007f959507108f
9.40% heapgettup_pagemode
5.40% slot_getsomeattrs
4.91% ExecQual
3.81% heapgetpage
...
Note that two other hot routines are now available for possible specialization.