What's actually happening in the "Result" node and "Append" in a Postgres plan?

I have a table (postgres 9.6) which is partitioned by date into about 70 child tables.

The EXPLAIN output below is truncated as most of it is identical bitmap index/heap scans of each of the child tables (full, verbose output here). The parts I'm interested in are the Append and Result nodes near the top of the tree.

• From what I've been able to glean (e.g. here and elsewhere), Append is simply the sum of all the subqueries. In fact it takes about 3.5s longer than this. Where's the extra coming from?

• I can only find the one reference to Result which states This operation is used when your query selects some constant value. This node is taking about 7 seconds yet the query selects no constant values.

I would love to save these 10 seconds if possible, but I don't actually know what these nodes are doing, so I don't know what to try.

EXPLAIN ANALYZE output:

```
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ HashAggregate (cost=4459946.40..4473566.96 rows=1089645 width=64) (actual time=26289.308..26419.989 rows=190112 loops=1) │
│ Group Key: frontend_prescription.processing_date, frontend_prescription.pct_id, substr((frontend_prescription.presentation_code)::text, 1, 9) │
│ Buffers: shared hit=172527

The Append has to do some buffer management. It also makes a lot of calls to some clock function (gettimeofday, for example), to satisfy the timing component of EXPLAIN ANALYZE. That overhead might be the dominant time sink.

In your case, the Result node is computing the result of the SUBSTR function. You can see this is in the Output field of the verbose plan. It is also making a lot of calls to some clock function.

If you run explain (analyze, timing off), the execution time of that should tell you how much of the time in your EXPLAIN ANALYZE is going just towards instrumentation overhead.

StackExchange Database Administrators Q#165611, answer score: 6