OPT: suboptimal behaviors on some queries

[xinyuliu12]

Hello everyone!

First off, I am sorry for gestures vaguely all of this. Second, I think we may have found a few interesting cases wherein semantically equivalent queries exhibit significantly different runtime performance. Each test case comes with a pair of equivalent queries and their corresponding EXPLAIN ANALYZE (DEBUG) outputs.

It would be helpful if you could comment on whether they suggest any potentially useful optimization opportunities. Thank you very much!

Case#	Time Difference on v20.2.0-alpha.1	Time Difference on v21.2.3
0	180.3	339.5
1	3.14	1.1
2	2.1	1.05
3	5.9	3.8
4	669.14	1051
5	4.06	1.26
6	109.5	1.72
7	3.63	1.04
8	1.9	1.02
9	3.41	0.93
10	52.12	4.2
11	8.94	35
12	2.2	1.0
13	2.6	105
14	16.3	1.0

Test environment

• Ubuntu 20.04 machine "Linux panda 5.4.0-40-generic add weighted reservoir sampling (AE-S) #44-Ubuntu SMP Tue Jun 23 00:01:04 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux"
• Cockroachdb v20.2.0-alpha.1
• Database: A database based on the SCOTT schema, which has three tables: EMP (100k rows), BONUS (100k rows), and DEPT (10k rows).

Reproduce the performance difference

1. Install cockroachdb v20.2.0-alpha.1

    $ mkdir -p $(go env GOPATH)/src/github.com/cockroachdb
    $ cd $(go env GOPATH)/src/github.com/cockroachdb
    $ git clone https://github.com/cockroachdb/cockroach
    $ cd cockroach
    $ git checkout eaa939c
    $ make build
    $ sudo make install

• start a cockroachdb node in your preferred working directory

    $ cockroach start --insecure --store=node1 --listen-addr=localhost:26257 --http-addr=localhost:8080 --join=localhost:26257,localhost:26258,localhost:26259 --background
    $ cockroach init --insecure --host=localhost:26257

2. Set up the database instance based on the SCOTT schema

• Download dataset from the link: https://drive.google.com/file/d/1g6JZpSHcVzlbakGsqPuozqCan9IqJt5Y/view?usp=sharing

    $ tar xzvf scott100.tar.gz
    $ cd scott100
    $ chmod +x setup.sh

• Create the database instance and its schema using CockroachDB's built-in SQL client:

    $ cockroach sql --insecure --host=localhost:26257
    $ create database scott100;
    $ exit
    $ cockroach sql --insecure --host=localhost:26257 -d scott100 < create.sql

• Load the dataset to the database

    $ ./setup.sh

3. Test the SQL query that exhibits performance issue

• Execute the queries after logging to the testing database using the following command
  $ cockroach sql --insecure --host=localhost:26257 -d scott100

Jira issue: CRDB-11671

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[cucaroach]

Hi xinyuliu12, thanks for the report! We aren't making any non-security related changes to 20.2, would it be possible for you to try to reproduce this with 21.2.latest? Thanks!

[xinyuliu12]

Thank you so much for your response!
Sure, I will try to reproduce them on the 21.2.latest version and update the report accordingly. Thanks!

[xinyuliu12]

Hi Tommy, I tried to reproduce them on the a pre-built V21.2.3 version (stable) and reported the time difference under the new Time Difference on v21.2.3 column. If the significant time difference persists on V21.2.3 (> 1.5x), I also update the super link, which now points to the V21.2.3's EXPLAIN ANALYZE (DEBUG) outputs.
I would be happy to provide any additional information that you may need. Thanks!