[Maria-developers] MDEV-8306: EXPLAIN JSON output is not JSON-ish
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
Hi Varun, I'm looking at JOIN::substitute_base_with_nest_field_items() and I have these questions:
/* Substituting SELECT list field items with sort-nest's field items */ while ((item= it++)) { Item *real_item= item->real_item(); if ((new_item= real_item->transform(thd, &Item::replace_with_nest_items, TRUE, (uchar *) nest_info)) != real_item) { new_item->name= item->name; thd->change_item_tree(it.ref(), new_item);
Why thd->change_item_tree() call is present for select list elements but but not for other kinds of items?
/* Substituting WHERE clause's field items with sort-nest's field items */ if (conds) { conds= conds->transform(thd, &Item::replace_with_nest_items, TRUE, (uchar *) nest_info); conds->update_used_tables(); }
As far as I understand, the items from the WHERE clause can be attached both outside and inside the nest. Moreover, if the WHERE clause has this form (condA(t1) AND condB(t2) OR (condC(t1) AND condD(t2)) and the join order is t1,t2, then make_cond_for_table() will attach condA(t1) to both t1 and t2. How is this handled? (My first attempt to construct an example for this was not successful due to MDEV-22360 checks. But I am not convinced that we can rely on MDEV-22360 's check to avoid such cases. And even if we could, I don't think it would have been a good idea) BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
On Sat, Feb 13, 2021 at 03:23:50PM +0300, Sergey Petrunia wrote:
/* Substituting WHERE clause's field items with sort-nest's field items */ if (conds) { conds= conds->transform(thd, &Item::replace_with_nest_items, TRUE, (uchar *) nest_info); conds->update_used_tables(); }
As far as I understand, the items from the WHERE clause can be attached both outside and inside the nest.
Moreover, if the WHERE clause has this form
(condA(t1) AND condB(t2) OR (condC(t1) AND condD(t2))
and the join order is t1,t2, then make_cond_for_table() will attach condA(t1) to both t1 and t2.
How is this handled?
Ah, I've found extract_condition_for_the_nest() function. This is now cleara. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
Hi Sergey, On Sat, Feb 13, 2021 at 5:52 PM Sergey Petrunia <sergey@mariadb.com> 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@mariadb.com> 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@mariadb.com> 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
participants (2)
-
Sergey Petrunia -
varun gupta