sql: investigate performance regressions on some queries

[yuzefovich]

Our friends from Apollo team at Georgia Tech have found multiple queries on which our 19.1 version performs better than 19.2 which indicates some performance regressions. I reran all reported queries on a single node setup of 19.1.8, 19.2.5, and 20.1.0.beta-4-dirty and observed the regressions on most of the queries (all times are in seconds).

Case #	19.1.8	19.2.5	20.1.0
0	0.875	1.922	1.815
1	1.110	2.896	3.118
3	0.759	1.856	1.810
5	0.057	0.299	0.304
6	0.196	0.301	0.314
9	0.283	0.665	0.630
10	1.201	3.279	3.598
11	1.097	3.326	3.391
12	0.310	0.686	0.627
14	0.747	3.181	2.997
16	0.965	1.957	1.966
18	2.037	2.346	2.323
19	0.758	3.190	2.959
22	1.019	1.923	2.018
24	2.031	2.303	2.375

Note that query 6 runs in about the same time in 20.1.0 as in 19.2.5 if we set distsql=on. Further investigation is needed for why the behavior of distsql=auto has changed between 19.2.5 and 20.1. This has been addressed by #47365, and I updated the run times.

I have not looked into any other regressions.

The repro instructions can be found in our Apollo slack channel.

[awoods187]

cc @RaduBerinde

[asubiotto]

Wrote a script to check for plan differences in different versions. Here is the output for these queries. In each query, the top plan is 19.1.8 and the bottom is 20.1.0:
diffoutput.txt

Some differences are due to the output changing between versions. I haven't taken a close look at them yet but wanted to upload them in case anyone saw anything interesting. In query 1, for example, there seems to be an extra ORDER BY in the window function as well as an extra render stage.

[asubiotto]

And here is the explain analyze output:
diffoutput_analyze.txt

[asubiotto]

Looking at the explain analyze output of some queries that have window functions it seems that a lot of these regressions can be explained by the fact that we previously did not respect the work limit in the windower processor. We seem to be properly spilling to disk in 20.1 so I would classify these as expected regressions. Queries that include window functions here are: 1, 10, 11, 14, 18, 19, 24 so we can eliminate those.

One thing for the optimizer team cc @RaduBerinde: Query 5 seems to use a merge join instead of a lookup join.

Still needs further investigation: 0, 3, 6, 9, 12, 16, and 22. The EXPLAIN (VERBOSE) output doesn't seem different for these and the EXPLAIN ANALYZE doesn't seem to display properly and for some reason the encoded links all have a bunch of repeated "A"s (example: Query 6 explain analyze plan)

[awoods187]

thanks for taking a look at these @asubiotto! I'm curious about query 5 @rytaft or @RaduBerinde

[rytaft]

I've looked into this, and I'm pretty sure that query 5 changed because we've made lookup joins more expensive since 19.1. In particular, #40248 and #43003 probably had an impact. I haven't actually done a git bisect, but I'm pretty sure that's the cause.

Part of the problem here is that our row count estimate of the distinct table after filtering is incorrect since we can't determine that the filter on district is actually false. We could do that pretty easily by creating a rule that converts exists(<subquery known to return 0 rows>) to false. I'll open an issue to create such a rule.

[rytaft]

Actually -- just went ahead and submitted a PR since it was so trivial: #48162. I confirmed that the resulting plan for query 5 is much better.

[yuzefovich]

I think we addressed the two main problems identified by this issue (#47365, #48162), and most of the remaining regressions were expected given the fix to the window functions, so I'm closing this issue.