github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/explain_analyze_plans (about)

     1  # LogicTest: 5node
     2  
     3  # These tests are different from explain_analyze because they require manual
     4  # data placement.
     5  
     6  statement ok
     7  CREATE TABLE kv (k INT PRIMARY KEY, v INT, FAMILY (k, v))
     8  
     9  statement ok
    10  INSERT INTO kv SELECT i, i FROM generate_series(1,5) AS g(i);
    11  
    12  statement ok
    13  CREATE TABLE kw (k INT PRIMARY KEY, w INT, FAMILY (k, w))
    14  
    15  statement ok
    16  INSERT INTO kw SELECT i, i FROM generate_series(1,5) AS g(i)
    17  
    18  # Split into 5 parts, each row from each table goes to one node.
    19  statement ok
    20  ALTER TABLE kv SPLIT AT SELECT i FROM generate_series(1,5) AS g(i)
    21  
    22  statement ok
    23  ALTER TABLE kw SPLIT AT SELECT i FROM generate_series(1,5) AS g(i)
    24  
    25  statement ok
    26  ALTER TABLE kv EXPERIMENTAL_RELOCATE SELECT ARRAY[i], i FROM generate_series(1, 5) as g(i)
    27  
    28  statement ok
    29  ALTER TABLE kw EXPERIMENTAL_RELOCATE SELECT ARRAY[i], i FROM generate_series(1, 5) as g(i)
    30  
    31  # Verify that EXPLAIN ANALYZE (DISTSQL) annotates plans with collected
    32  # statistics.
    33  
    34  # Verify data placement.
    35  query TTTI colnames,rowsort
    36  SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE kv]
    37  ----
    38  start_key  end_key  replicas  lease_holder
    39  NULL       /1       {1}       1
    40  /1         /2       {1}       1
    41  /2         /3       {2}       2
    42  /3         /4       {3}       3
    43  /4         /5       {4}       4
    44  /5         NULL     {5}       5
    45  
    46  # Verify data placement.
    47  query TTTI colnames,rowsort
    48  SELECT start_key, end_key, replicas, lease_holder from [SHOW RANGES FROM TABLE kw]
    49  ----
    50  start_key  end_key  replicas  lease_holder
    51  NULL       /1       {5}       5
    52  /1         /2       {1}       1
    53  /2         /3       {2}       2
    54  /3         /4       {3}       3
    55  /4         /5       {4}       4
    56  /5         NULL     {5}       5
    57  
    58  # This query verifies stat collection for the tableReader, mergeJoiner, and
    59  # aggregator.
    60  query T
    61  SELECT url FROM [EXPLAIN ANALYZE (DISTSQL) SELECT kv.k, avg(kw.k) FROM kv JOIN kw ON kv.k=kw.k GROUP BY kv.k]
    62  ----
    63  https://cockroachdb.github.io/distsqlplan/decode.html#eJzsmltv2kgcxd_3U4zmiagm9owNIZYqQbvZFV1islykzVYocvAsWBibHRuSKMp3rwypwiWe8aVmJlHeuNjw-5_8c-b0NI8w_N-DJuxfdC6-DsCSeuCPXvcSfL_456rTalugZbU61_9egMrv7f6g_3fnBDxfOludzhRgryaV2d3p7GRz22wFvnXbFpjdga61vgR8BvH74M9ed3gFvlyvXxxBBfqBQyx7TkJofocIKhBDBepQgQZUYA2OFLigwZiEYUDjSx7XN7Sde2hqCnT9xTKKXx4pcBxQAs1HGLmRR6AJB_atR3rEdghVNahAh0S2662_ZkHduU0fmrMVVGB_YfuhCapq_MXdZWSCZoxxa0fjKQlBsIwW8YvxR0TLhbf1UnxZSDwyjtyVGz2YQDvV4svCyPY8ELlzYgIthKMnBW5u2QzwE_D2AUztcLqL1kRw9DRaf8aEQBM9KfkmridMfPcysYrknBknzvzyOQF1CCXO_ud8ir841VWvyHdJ6IR8C1yfUBXtbYxH_osqTfTp5DN1J9PNwxfplCYuph65J-Nl5AZ-koIv6uhZNqI1mVAysaOAqqh2ILoCuxuBnn_8Lev6xuoObqxhp1NponjA_vCy0sTxo6_doTV4fvxrJ1UgDe5CQIntPN-3u0sKnNv3YE7mAX0AyzDGxRr4y_1SaMuMYlvGFhtrxcTuDy9v2rHcevysR3yH0PWigSZWm_rRlq2WQqSl_5pMrypkBdVgoeK9TUwxSq34KPWdUVB6J0X8s0PFVVWX0ElR3pnPUpweks6ME2c-4umB3sbpwdmI7dOj_utOj7xuX65GRU6PzFvGOT3Qezw9cHr7wSksV6-qhoT2g_LO3EhhuZLOjBNnPqLl4rdhuZyN2Lbcsw_LTbbczFvGsVz8Hi1XT28_egrLNapqTUL7QXlnPk9huZLOjBNnPqLl6m_DcjkbsW25jQ_LTbbczFvGsVz9PVqukd5-jBSWW6tKaD4o78Q1vuFWVQRs3wEIBNGUUAmnx4nTH9F6jbdhvZzd2Lbe8w_rTbbezFvGsV7jPVov5_9CeiRcBH5I9mrq1z9ZixUkzoRsFA-DJR2TKxqM11-zedpd37dufxwSRpt38eZJ2__5VhjZUdqaW0vtXlmQGvIhIb0IU3qTz8QkoU64kE55mVCGDccyIDXkQ9rb8IxMeTecwyShTriQTnmZ8D6Tts20i6Tt36wzBzJEOBsbCWkiXITDVJOPCUuoExaik8H8_dhD2r-5xrwZ13d_u44zUF2-0MZGEhJG2EjFQltJTBLqVCy05T0CzuQLbWwkIWGEjVQstJXEJKFOxUJb3g1vMM-Vc3ZoOy8vtOXVmI1ULLTl1ZjDJCSMsJnEhDYOkxCd0EGXwEptR_oHaZYu4Vg6ZSkT5GASE714UIWUKmujxCh1UCiIT18cJjFdEJtJTP7iQRVSKveaczZKjFIHXcXOCYMMdgZDB7WC-BDGYRJTnfGgxBzlbCgxOYwHJSaIsRswMT89CRswDpOYeCFjB8aDEhNZy2zBckNJWINxmMTECxmLMB6UmMhaZhWWG4rdhSFOGYYkbMM4TIKCmIx9GAeqWBDLbSgyNmJYwkYMl9iIlcQkJF5wmMQEMR6UjEqJCWK4xEYs95pL2IhxmMQEMR6UjEoJ-kMydiOGOY0YlrAR4zCJCWI8KDHxQsZGjAclJvTI0IiNnn77EQAA__-Wr_Sp
    64  
    65  # This query verifies stats collection for the hashJoiner, distinct and sorter.
    66  query T
    67  SELECT url FROM [EXPLAIN ANALYZE (DISTSQL) SELECT DISTINCT(kw.w) FROM kv JOIN kw ON kv.k = kw.w ORDER BY kw.w]
    68  ----
    69  https://cockroachdb.github.io/distsqlplan/decode.html#eJzMWU1v4zYQvfdXEHNKUDkS9eE4AhZIunFRL1J7a-fQ7cIHxWJjwYrkknSyQeD_XkjehSNrQ-ojFnmLJA71ZvTevAz9Auy_GHyYDW-GH2_Rhsbo9-nkT_R1-Pfnm6vRGF2Nr26-_DNEJ9ej2e3sr5tT9H1pdj0af7xFJ6uns6fTXdjqEX2ajMZo9YQmY7R6PFuhDyh7jibT6-EU_fYlv5qDAUkaknHwQBj4XwGDATYY4IABLhjgwdyANU0XhLGUZkte8oBR-A18y4AoWW94dntuwCKlBPwX4BGPCfhwG9zFZEqCkFDTAgNCwoMozl-zptFDQJ8vV49gwGwdJMxHPTN78WTDfXSZwbgL-GJJGEo3fJ3dzLbgm3X86la2jJGYLHj0GPFnH1lnVraM8SCOEY8eiI8sBvOtAbuQXQI_AN49o2XAlkVolxjm23m-xz0BH2-NZhn338j4aZ-xiV_nbGuTs_1mzvt9NklKQ0JJWNhpnkXKlvykcH8EbPkpjRJCTXxAlZj8y08u8ekHGt0v87_eq2LkG1lseJQmb1VtXxGnDguuI8ajZMFN7JXK3BVgt8UnFKVkW81S8tqn5NX5BrOUckJNGx_C_bUzvP0CXly9c2B5rzTtnulo3y1r5HxeoVsWcta1W-KOuyXWvltKWLDvln1NuqVdnbZ2Bak6PdPVXqo1ch5UkGohZ12lancsVVt7qUpYsJfquSZSdarT1qkgVbdnetpLtUbOFxWkWshZV6k6HUvV0V6qEhbspTrQRKpuddq6FaTq9bQXao2MPblQeyZGQRIijFK-JFR7ybodS9bVXrISPuwle6GJZCUz9pSwdZowcvgpf7qzlX0_Et6THR9YuqEL8pmmi_w1u8tJHpdPDCFhfPfU3l2Mkh-PGA_yvauUxKrM-jqQBvpBwq3KVL051MLU169OtqUCE67BcFsHSAP9IOFWZWrKcAmmvn51OmB4R5jsQ0zWa0xOAZJ1GOwIg92iYg-DXWGwJ36zJwy2i5SzuillXz87FENSYodiSO3s8EiYlNihhE2t7LBpUz3Xzw7FkJTYoRhSOzs8EiYldihhUys7bMrwgdBXLsSmdFHHDjv6N0jDGU2CSYkrSTCpsSUZqFa-dCxGqZnTdBzUdJzUjjmqHQtUK3NqTHMdhzUsntawZFzDtea1jupcGgM1MCgxplYGdaQ6KTIoCSglg5OMUWoMqnRgoYFBiTG1MqjGNBdjUmRQElBqDhMljFJjUKVTi6JBnUsMqjQSamBQpZHw_QzqSJjUTFBiTIoMSgJKjUFJGKXGoEqHF-9nUI1pLsakZoISY1JkUBJQagxKwig1P3eVTi-KPxxhsUHZpalQgUHNt7_8HwAA__-U49YN
    70  
    71  # This query verifies stats collection for WITH ORDINALITY and the hashJoiner.
    72  query T
    73  SELECT url FROM [EXPLAIN ANALYZE (DISTSQL) SELECT * FROM kv WITH ORDINALITY AS a, kv WITH ORDINALITY AS b]
    74  ----
    75  https://cockroachdb.github.io/distsqlplan/decode.html#eJzMkVFv0zAUhd_5FdZ9AmRokqY8-CkRK1pQaEYTCcaUBze52qy5drCdalXV_46STKKttm4FJPbo6_P5nnO8AftTAoN8mk4_FqQ1knyaZ1_I1fT7RRonMxLP4vTyx5S8PkvyIv-aviH30reD8HZFviXFOcnmZ8ksTpPiksQ54fSRi0UJFJSuccaXaIFdgQ8lhcboCq3VphttekFS3wHzKAjVtK4blxQqbRDYBpxwEoFBwRcS58hrNCMPKNTouJD9s40RS27W0e0KKOQNV5aRd0BhwV11g5bo1jWtY6TDXNvIndEEKFiUWDmxEm7NiPfe62TWcSmJE0tkxLNQbikMyL076_g1AvO39PkJMlMLxaVw65G_H-AvjOIdVq0TWj1tNjjF7G7dwUupe_yHdY__R93ho2Z_e2yVNjUarPcMlh35lOSBxOfc3nzWQqEZhfuJsy5T5NMooNGYRiGNJjT68Lw_C_5BF5NTPm6OttHK4mEnD77sdUVgfY1DsVa3psILo6t-zXDMeq4f1GjdcOsPh0QNV53BXdg_Cod7sH8IB0fh8fHN4xM2B4dweBSeHGwut69-BQAA__-lffvJ
    76  
    77  # Verify that EXPLAIN ANALYZE on an unsupported query doesn't return an error.
    78  statement ok
    79  EXPLAIN ANALYZE (DISTSQL) SHOW QUERIES;
    80  
    81  statement ok
    82  EXPLAIN ANALYZE (DISTSQL) EXPLAIN SELECT 1
    83  
    84  # This query verifies support for zeroNode in DistSQL.
    85  query B
    86  SELECT automatic FROM [EXPLAIN (DISTSQL) SELECT sum(k) FROM kv WHERE FALSE]
    87  ----
    88  true
    89  
    90  # This query verifies stat collection for the tableReader and windower.
    91  query T
    92  SELECT url FROM [EXPLAIN ANALYZE (DISTSQL) SELECT avg(k) OVER () FROM kv]
    93  ----
    94  https://cockroachdb.github.io/distsqlplan/decode.html#eJzMlE2P2jAQhu_9FdacQDLNJz34BG1phUphC2j7scrBG49oRBKntsOCEP-9ioO6mxW0qbQSHD2e1_M-M9bsQf9KgcFiNBm9W5JSpeTDfPaZ3I2-3UyG4ykZToeT7z9GpPN-vFguvky65JjKN6vOuktmt6M56XRr1XoTAYVcCpzyDDWwO_CAgg8UAqAQAoU-RBQKJWPUWqoqZW8FY7EF5lJI8qI0VTiiEEuFwPZgEpMiMFjy-xTnyAUqxwUKAg1PUlumUEnG1W6w3gCFRcFzzUjPqQrPSsPIoLJxz038EzWRpSmqYPWEKYv0SahK05hibJJNYnaMuK_dKk0bnqbEJBky4mqIDhRqydGpNnyFwLwDPUPzCFHmUglUKBoA0eEE79ckF_IBldNvwg5vP3YGXvcPnN8Orn8GDrcYlyaR-SMgBSUfNFHIxVHXbACFjG9JhplUO1JqFIwELvmUvD3bGr_RGq_9oL1_D9rxe05w0VH77Xn8FjxBzwkvyhO05wla8IQ9-4UvxxO25wlb8PR7V7NYTtDMURcy1_hswZx-2a0WD4oV1ltKy1LFeKNkbMvUx5nV2YBAbepbrz6Mc3tlDT4Ve38Vv2mI3edi_38qWxRL9bKDaFoKrs9SeH2W-ldgKTq8-h0AAP__SsbYwA==
    95  
    96  # Very simple query to make it easier to spot regressions when rewriting results
    97  # in test files.
    98  query T
    99  SELECT url FROM [EXPLAIN ANALYZE (DISTSQL) SELECT k FROM kv WHERE k = 0];
   100  ----
   101  https://cockroachdb.github.io/distsqlplan/decode.html#eJyMkM9L-0AQxe_fv2KY70VhNZvrgtCiEQOxrUnBHyWHbTLUkG027k6KpeR_lyS9KAge5zPvvd03J_QfBhVmURLdrqFzBu7T5SNsopdVMo8XMF_Mk9e3CC7u4mydPSWXcJbWk7A-wPNDlEZQww3IHAU2tqSF3pNHtcEQc4GtswV5b92ATqMgLj9RSYFV03Y84FxgYR2hOiFXbAgVrvXWUEq6JBdIFFgS68qMsa2r9todZ_UBBWatbryCQF4FMviPApcdK5iFKHCruXgnD7bjdoBDDHet-YE8GSq4OlR8VCCv5chYGwNc7UmB9Jj3AifL-bee9Y5Qhb34e6OUfGsbT9_K_JYs-1wglTuaruZt5wpaOVuMz0zjcvSNoCTP0zachriZVn3e__sKAAD__xmzmlc=