maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #12537
Re: MDEV-8306: EXPLAIN JSON output is not JSON-ish
Hi Sergey,
On Sat, Feb 13, 2021 at 5:52 PM Sergey Petrunia <sergey@xxxxxxxxxxx> wrote:
> Hi Varun,
>
> (This email is not the complete input, and not the most important part of
> the
> input. More to follow. I think it's better to split input into multiple
> smaller
> pieces, it's easier to track, and you get to see the first portions of the
> input sooner)
>
> EXPLAIN EXTENDED SELECT t3.b, t2.a, t1.b, t1.a
> FROM t1,t2,t3
> WHERE t1.b=t3.b
> ORDER BY t1.b DESC, t2.a DESC
> LIMIT 3;
>
>
> +------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+
> | id | select_type | table | type | possible_keys | key | key_len
> | ref | rows | filtered | Extra |
>
> +------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+
> | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL
> | NULL | 10 | 100.00 | |
> | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL
> | NULL | 20 | 100.00 | Using join buffer (flat, BNL join) |
> | 1 | SIMPLE | <sort-nest> | ALL | NULL | NULL | NULL
> | NULL | 3 | 100.00 | Using filesort |
> | 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL
> | NULL | 100 | 5.00 | Using where |
>
> +------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+
>
> So, here <sort-nest> includes t2 and t1, and then t3 is joined with it.
>
> However in EXPLAIN FORMAT=JSON output they all look like peers:
>
> {
> "query_block": {
> "select_id": 1,
> "table": {
> "table_name": "t2",
> "access_type": "ALL",
> "rows": 10,
> "filtered": 100
> },
> "block-nl-join": {
> "table": {
> "table_name": "t1",
> "access_type": "ALL",
> "rows": 20,
> "filtered": 100
> },
> "buffer_type": "flat",
> "buffer_size": "65",
> "join_type": "BNL"
> },
> "read_sorted_file": {
> "filesort": {
> "sort_key": "`sort-nest`.b desc, `sort-nest`.a desc",
> "table": {
> "table_name": "<sort-nest>",
> "access_type": "ALL",
> "rows": 3,
> "filtered": 100
> }
> }
> },
> "table": {
> "table_name": "t3",
> "access_type": "ALL",
> "rows": 100,
> "filtered": 5,
> "attached_condition": "t3.b = `sort-nest`.b"
> }
> }
> }
>
> which is counter-intuitive.
Well i didn't make changes to how to display the sort-nest in
EXPLAIN/ANALYZE FORMAT=JSON.
But i think we can use do it in the way that inside :
"table": {
"table_name": "<sort-nest>",
"access_type": "ALL",
"rows": 3,
"filtered": 100
}
we put in the inner tables too as it is done with materialization.
>
>
> Also, `sort-nest` is quoted. I assume it is because of the '-' in it?
> Maybe we
> should change the name to avoid the quoting?
>
Well i think we can have just *sortnest* in the naming.
>
> BR
> Sergei
> --
> Sergei Petrunia, Software Developer
> MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
>
>
On Sat, Feb 13, 2021 at 5:52 PM Sergey Petrunia <sergey@xxxxxxxxxxx> wrote:
> Hi Varun,
>
> (This email is not the complete input, and not the most important part of
> the
> input. More to follow. I think it's better to split input into multiple
> smaller
> pieces, it's easier to track, and you get to see the first portions of the
> input sooner)
>
> EXPLAIN EXTENDED SELECT t3.b, t2.a, t1.b, t1.a
> FROM t1,t2,t3
> WHERE t1.b=t3.b
> ORDER BY t1.b DESC, t2.a DESC
> LIMIT 3;
>
>
> +------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+
> | id | select_type | table | type | possible_keys | key | key_len
> | ref | rows | filtered | Extra |
>
> +------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+
> | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL
> | NULL | 10 | 100.00 | |
> | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL
> | NULL | 20 | 100.00 | Using join buffer (flat, BNL join) |
> | 1 | SIMPLE | <sort-nest> | ALL | NULL | NULL | NULL
> | NULL | 3 | 100.00 | Using filesort |
> | 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL
> | NULL | 100 | 5.00 | Using where |
>
> +------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+
>
> So, here <sort-nest> includes t2 and t1, and then t3 is joined with it.
>
> However in EXPLAIN FORMAT=JSON output they all look like peers:
>
> {
> "query_block": {
> "select_id": 1,
> "table": {
> "table_name": "t2",
> "access_type": "ALL",
> "rows": 10,
> "filtered": 100
> },
> "block-nl-join": {
> "table": {
> "table_name": "t1",
> "access_type": "ALL",
> "rows": 20,
> "filtered": 100
> },
> "buffer_type": "flat",
> "buffer_size": "65",
> "join_type": "BNL"
> },
> "read_sorted_file": {
> "filesort": {
> "sort_key": "`sort-nest`.b desc, `sort-nest`.a desc",
> "table": {
> "table_name": "<sort-nest>",
> "access_type": "ALL",
> "rows": 3,
> "filtered": 100
> }
> }
> },
> "table": {
> "table_name": "t3",
> "access_type": "ALL",
> "rows": 100,
> "filtered": 5,
> "attached_condition": "t3.b = `sort-nest`.b"
> }
> }
> }
>
> which is counter-intuitive.
>
> Also, `sort-nest` is quoted. I assume it is because of the '-' in it?
> Maybe we
> should change the name to avoid the quoting?
>
> BR
> Sergei
> --
> Sergei Petrunia, Software Developer
> MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
>
>
On Sat, Feb 13, 2021 at 5:51 PM Sergey Petrunia <sergey@xxxxxxxxxxx> wrote:
> Hi Varun,
>
> (This email is not the complete input, and not the most important part of
> the
> input. More to follow. I think it's better to split input into multiple
> smaller
> pieces, it's easier to track, and you get to see the first portions of the
> input sooner)
>
> EXPLAIN EXTENDED SELECT t3.b, t2.a, t1.b, t1.a
> FROM t1,t2,t3
> WHERE t1.b=t3.b
> ORDER BY t1.b DESC, t2.a DESC
> LIMIT 3;
>
>
> +------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+
> | id | select_type | table | type | possible_keys | key | key_len
> | ref | rows | filtered | Extra |
>
> +------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+
> | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL
> | NULL | 10 | 100.00 | |
> | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL
> | NULL | 20 | 100.00 | Using join buffer (flat, BNL join) |
> | 1 | SIMPLE | <sort-nest> | ALL | NULL | NULL | NULL
> | NULL | 3 | 100.00 | Using filesort |
> | 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL
> | NULL | 100 | 5.00 | Using where |
>
> +------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+
>
> So, here <sort-nest> includes t2 and t1, and then t3 is joined with it.
>
> However in EXPLAIN FORMAT=JSON output they all look like peers:
>
> {
> "query_block": {
> "select_id": 1,
> "table": {
> "table_name": "t2",
> "access_type": "ALL",
> "rows": 10,
> "filtered": 100
> },
> "block-nl-join": {
> "table": {
> "table_name": "t1",
> "access_type": "ALL",
> "rows": 20,
> "filtered": 100
> },
> "buffer_type": "flat",
> "buffer_size": "65",
> "join_type": "BNL"
> },
> "read_sorted_file": {
> "filesort": {
> "sort_key": "`sort-nest`.b desc, `sort-nest`.a desc",
> "table": {
> "table_name": "<sort-nest>",
> "access_type": "ALL",
> "rows": 3,
> "filtered": 100
> }
> }
> },
> "table": {
> "table_name": "t3",
> "access_type": "ALL",
> "rows": 100,
> "filtered": 5,
> "attached_condition": "t3.b = `sort-nest`.b"
> }
> }
> }
>
> which is counter-intuitive.
>
> Also, `sort-nest` is quoted. I assume it is because of the '-' in it?
> Maybe we
> should change the name to avoid the quoting?
>
> BR
> Sergei
> --
> Sergei Petrunia, Software Developer
> MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
>
>
>
References
