Huge generated UNION ALL faster than JOIN?

Hi,

I recently stumbled across an interesting query performance question
over at StackOverflow [1], which caught my attention and I started to
investigate the issue further.

One of the things consuming most of the time was an Index Only Scan
executed millions of times. And on top came the Nested Loop which
finally reduced the rows but also took a lot of time to do so.

Explain plan: https://explain.depesz.com/s/4GYT

My initial test using a ~200M row table [2] revealed a completely
different plan with a no-brainer performance of ~10.7 milliseconds vs.
the original 6.25 minutes (10x more rows cannot explain that).

As the query included a LIMIT I also started to play around with OFFSET
because I know that at least OFFSET + LIMIT (sorted) rows have to be
read, with interesting result using one of my optimization attempts:

To eliminate the JOIN and the massive amount of loops I decided to
generate a huge UNION ALL query using a function [3] on-the-fly and
although performing pretty poor for no OFFSET, it makes quite a huge
difference with higher ones.

Here, the times for "exec_a" are with plans as generated by PostgreSQL
11.2 and "exec_d" with my UNION ALL version (all times include planning
and execution):

 c_offset |  exec_a   |  exec_d
----------+-----------+-----------
        0 |    10.694 |   746.892
    10000 |   175.858 |   653.218
   100000 |  1632.205 |   791.913
  1000000 | 11244.091 |  2274.160
  5000000 | 11567.438 |  9428.352
 10000000 | 13442.229 | 17026.783

Complete plans for all executions here:
exec_a: https://explain.depesz.com/s/Ck1
exec_d: https://explain.depesz.com/s/ZoUu

A retest after upgrading to PostgreSQL 11.3 and adding another 200M rows
revealed even different numbers:

 c_offset |  exec_a   | exec_a_x2 |  exec_d   | exec_d_x2
----------+-----------+-----------+-----------+-----------
        0 |    10.694 |    16.616 |   746.892 |   630.440
    10000 |   175.858 |   182.922 |   653.218 |   646.173
   100000 |  1632.205 |  1682.033 |   791.913 |   782.874
  1000000 | 11244.091 | 24781.706 |  2274.160 |  2306.577
  5000000 | 11567.438 | 24798.120 |  9428.352 |  8886.781
 10000000 | 13442.229 | 27315.650 | 17026.783 | 16808.223

One major difference for the "exec_a" plans is that starting with OFFSET
of 1000000, the planner switches from a "Merge Append" + "Nested Loop"
to a "Parallel Append" + "Hash Join" + "Sort" + "Gather Merge", whereas
the plans for "exec_d" always remain single-threaded.

My question now is why can't the optimizer generate a plan that in this
case does 114 loops of "events" scans instead of a million loops on the
"subscription_signal"? There even is an index that spans both relevant
columns here (see [2]) which is used extensively in my UNION variant (as
intended and expected).

Also I observed that while the parallel append is going to be faster
eventually due to better I/O scalability (at least on my system using an
SSD separately for log and different index/data tablespaces) it leads to
a lot of CPU cores being saturated as well as a lot more I/O in general
and also includes the bottleneck of per-worker disk-sorts. From the
perspective of system resources this is not really helpful and it also
doesn't seem to bring much benefit in my case as parallel append just
saves ~10-20% (for OFFSET 1000000) vs. standard Append (with parallel
index/seq scans of partitions).

Using a single-threaded approach (to preserve resources for concurrent
queries, max_parallel_workers_per_gather = 0), the UNION ALL approach is
superior starting at offset 100000:

 c_offset |  exec_a   |  exec_d
----------+-----------+-----------
        0 |    18.028 |   292.762
    10000 |   188.548 |   308.824
   100000 |  1710.029 |   455.101
  1000000 | 81325.527 |  1993.886
  5000000 | 84206.901 |  8638.194
 10000000 | 84846.488 | 16814.890

One thing that really disturbs me in this case is the decision of the
optimizer to generate an Append + Hash starting with offset 1000000
instead of simply continuing with a Merge Append, which pushes down
limits and returns just 10M intermediate rows whereas Append does not -
yet - and results into 270M intermediate rows, resulting these numbers
(enable_hashjoin turned off to force a Merge Append):

 c_offset |  exec_a   |  exec_a_m
----------+-----------+------------
  1000000 | 81325.527 |  16517.566

...but then degrades further because it switches to Append again (no way
to test a Merge Append performance here, I guess):
  5000000 | 84206.901 | 107161.533
 10000000 | 84846.488 | 109368.087


Is there anything I can do about it (apart from my generated huge UNION)
to speed things up?

Please note that I'm using Timescale extension just as a simple way of
managing the partitions and indexes and intentionally set the time
column to a one different from the query filter to not have it optimize
things away under the hood.

Looking forward to any pointers here.

Cheers,

    Ancoron

Refs:
[1] https://stackoverflow.com/questions/55470713
[2] https://paste.ofcode.org/szj7f7fCSYk7jQNdd5Wvbx
[3] https://paste.ofcode.org/ibZ8fNmNFDrsyxa3NktdWB