systay

2024-06-04T06:01:03.093029Z	    8 Connect	root@localhost on  using TCP/IP
2024-06-04T06:01:03.094962Z	    8 Query	/* mysql-connector-java-8.0.30 (Revision: 1de2fe873fe26189564c030a343885011412976a) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2024-06-0

systay

2024-06-04T06:01:03.093029Z	    8 Connect	root@localhost on  using TCP/IP
2024-06-04T06:01:03.094962Z	    8 Query	/* mysql-connector-java-8.0.30 (Revision: 1de2fe873fe26189564c030a343885011412976a) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2024-06-0

systay

Initial tree:
Horizon
└── Union (DISTINCT)
    ├── Horizon
    │   └── Union (DISTINCT)
    │       ├── Horizon
    │       │   └── Union (DISTINCT)
    │       │       ├── Horizon
    │       │       │   └── Union (DISTINCT)
    │       │       │       ├── Horizon

systay

{
	infSchema := map[string]map[string]query.Type{}
	cols := map[string]query.Type{}
	cols["USER"] = query.Type(6165)
	cols["HOST"] = query.Type(6165)
	cols["GRANTEE"] = query.Type(6165)
	cols["GRANTEE_HOST"] = query.Type(6165)
	cols["ROLE_NAME"] = query.Type(6165)
	cols["ROLE_HOST"] = query.Type(6165)
	cols["IS_GRANTABLE"] = query.Type(6165)

systay

SELECT O_YEAR, SUM(CASE WHEN NATION = 'BRAZIL' THEN VOLUME ELSE 0 END)/SUM(VOLUME) AS MKT_SHARE
FROM (SELECT datepart(yy,O_ORDERDATE) AS O_YEAR, L_EXTENDEDPRICE*(1-L_DISCOUNT) AS VOLUME, N2.N_NAME AS NATION
 FROM PART, SUPPLIER, LINEITEM, ORDERS, CUSTOMER, NATION N1, NATION N2, REGION
 WHERE P_PARTKEY = L_PARTKEY AND S_SUPPKEY = L_SUPPKEY AND L_ORDERKEY = O_ORDERKEY
 AND O_CUSTKEY = C_CUSTKEY AND C_NATIONKEY = N1.N_NATIONKEY AND
 N1.N_REGIONKEY = R_REGIONKEY AND R_NAME = 'AMERICA' AND S_NATIONKEY = N2.N_NATIONKEY
 AND O_ORDERDATE BETWEEN '1995-01-01' AND '1996-12-31' AND P_TYPE= 'ECONOMY ANODIZED STEEL') AS ALL_NATIONS
GROUP BY O_YEAR
ORDER BY O_YEAR

systay

| -> Table scan on <temporary>  (actual time=0.001..0.002 rows=8 loops=1)
    -> Aggregate using temporary table  (actual time=0.635..0.637 rows=8 loops=1)
        -> Nested loop left join  (actual time=0.382..0.555 rows=8 loops=1)
            -> Nested loop left join  (actual time=0.375..0.532 rows=8 loops=1)
                -> Nested loop left join  (actual time=0.373..0.526 rows=8 loops=1)
                    -> Nested loop inner join  (actual time=0.367..0.513 rows=8 loops=1)
                        -> Inner hash join (no condition)  (actual time=0.207..0.218 rows=13 loops=1)
                            -> Table scan on i  (cost=0.00 rows=0) (actual time=0.001..0.005 rows=13 loops=1)
                                -> Fill information schema table i  (actual time=0.172..0.177 rows=13 loops=1)
                            -> Hash

systay

    --- FAIL: TestMultipleUpdatesFromDifferentShards/3 (0.04s)
        uptate_controller_test.go:170: 
            	Error Trace:	uptate_controller_test.go:170
            	Error:      	Not equal: 
            	            	expected: 2
            	            	actual  : 1
            	Test:       	TestMultipleUpdatesFromDifferentShards/3
            	Messages:   	signal required
        uptate_controller_test.go:172: 
            	Error Trace:	uptate_controller_test.go:172

systay

Traditional query optimizing is mostly about two things: in which order and from where to access data, and how to then combine it so it answers the query that the user wrote.

You have problably seen the tree shapes execution plans that are produced from query planning. I'll use an example from the MySQL docs:

mysql> EXPLAIN FORMAT=TREE
    -> SELECT *
    ->     FROM t1
    ->     JOIN t2
    ->         ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
    ->     JOIN t3

systay

"select id2 from user uu where id in (select id from user where id = uu.id and user.col in (select col from (select col, id, user_id from user_extra where user_id = 5) uu where uu.user_id = uu.id))"
"unsupported: cross-shard correlated subquery"
{
  "QueryType": "SELECT",
  "Original": "select id2 from user uu where id in (select id from user where id = uu.id and user.col in (select col from (select col, id, user_id from user_extra where user_id = 5) uu where uu.user_id = uu.id))",
  "Instructions": {
    "OperatorType": "Subquery",
    "Variant": "PulloutIn",
    "Inputs": [
      {

systay

2021-06-18T05:20:15.8665971Z ##[section]Starting: Request a runner to run this job
2021-06-18T05:20:15.8930680Z Can't find any online and idle self-hosted runner in current repository that matches the required labels: 'ubuntu-18.04'
2021-06-18T05:20:15.9163032Z Can't find any online and idle self-hosted runner in current repository's organization account that matches the required labels: 'ubuntu-18.04'
2021-06-18T05:20:15.9737770Z Found online and idle hosted runner in current repository's organization account that matches the required labels: 'ubuntu-18.04'
2021-06-18T05:20:16.0681670Z ##[section]Finishing: Request a runner to run this job
2021-06-18T05:20:21.9347717Z Current runner version: '2.278.0'
2021-06-18T05:20:21.9375285Z ##[group]Operating System
2021-06-18T05:20:21.9376160Z Ubuntu
2021-06-18T05:20:21.9376616Z 18.04.5
2021-06-18T05:20:21.9377051Z LTS