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

     1  # This file contains telemetry tests for sql.plan.* counters.
     2  
     3  exec
     4  CREATE TABLE x (a INT PRIMARY KEY)
     5  ----
     6  
     7  # Tests for EXPLAIN counters.
     8  
     9  feature-whitelist
    10  sql.plan.explain
    11  sql.plan.explain-analyze
    12  sql.plan.explain-opt
    13  sql.plan.explain-opt-verbose
    14  sql.plan.explain-distsql
    15  ----
    16  
    17  feature-usage
    18  EXPLAIN SELECT * FROM x
    19  ----
    20  sql.plan.explain
    21  
    22  feature-usage
    23  EXPLAIN (DISTSQL) SELECT * FROM x
    24  ----
    25  sql.plan.explain-distsql
    26  
    27  feature-usage
    28  EXPLAIN ANALYZE SELECT * FROM x
    29  ----
    30  sql.plan.explain-analyze
    31  
    32  feature-usage
    33  EXPLAIN ANALYZE (DEBUG) SELECT * FROM x
    34  ----
    35  
    36  feature-usage
    37  EXPLAIN (OPT) SELECT * FROM x
    38  ----
    39  sql.plan.explain-opt
    40  
    41  feature-usage
    42  EXPLAIN (OPT, VERBOSE) SELECT * FROM x
    43  ----
    44  sql.plan.explain-opt-verbose
    45  
    46  # Tests for hints.
    47  
    48  feature-whitelist
    49  sql.plan.hints.*
    50  ----
    51  
    52  feature-usage
    53  SELECT x FROM (VALUES (1)) AS a(x) INNER HASH JOIN (VALUES (1)) AS b(y) ON x = y
    54  ----
    55  sql.plan.hints.hash-join
    56  
    57  feature-usage
    58  SELECT x FROM (VALUES (1)) AS a(x) INNER MERGE JOIN (VALUES (1)) AS b(y) ON x = y
    59  ----
    60  sql.plan.hints.merge-join
    61  
    62  feature-usage
    63  SELECT a FROM (VALUES (1)) AS b(y) INNER LOOKUP JOIN x ON y = a
    64  ----
    65  sql.plan.hints.lookup-join
    66  
    67  feature-usage
    68  SELECT * FROM x@primary
    69  ----
    70  sql.plan.hints.index
    71  sql.plan.hints.index.select
    72  
    73  feature-usage
    74  UPDATE x@primary SET a=1 WHERE a>1
    75  ----
    76  sql.plan.hints.index
    77  sql.plan.hints.index.update
    78  
    79  feature-usage
    80  DELETE FROM x@primary WHERE a>1
    81  ----
    82  sql.plan.hints.index
    83  sql.plan.hints.index.delete
    84  
    85  # Tests for tracking important setting changes.
    86  
    87  feature-whitelist
    88  sql.plan.reorder-joins.*
    89  sql.plan.automatic-stats.*
    90  ----
    91  
    92  feature-usage
    93  SET CLUSTER SETTING sql.defaults.reorder_joins_limit = 0
    94  ----
    95  sql.plan.reorder-joins.set-limit-0
    96  
    97  feature-usage
    98  SET CLUSTER SETTING sql.defaults.reorder_joins_limit = 3
    99  ----
   100  sql.plan.reorder-joins.set-limit-3
   101  
   102  feature-usage
   103  SET CLUSTER SETTING sql.defaults.reorder_joins_limit = 6
   104  ----
   105  sql.plan.reorder-joins.set-limit-6
   106  
   107  feature-usage
   108  SET CLUSTER SETTING sql.defaults.reorder_joins_limit = 20 
   109  ----
   110  sql.plan.reorder-joins.set-limit-more
   111  
   112  feature-usage
   113  SET CLUSTER SETTING sql.stats.automatic_collection.enabled = on
   114  ----
   115  sql.plan.automatic-stats.enabled
   116  
   117  feature-usage
   118  SET CLUSTER SETTING sql.stats.automatic_collection.enabled = off
   119  ----
   120  sql.plan.automatic-stats.disabled
   121  
   122  feature-usage
   123  RESET CLUSTER SETTING sql.stats.automatic_collection.enabled
   124  ----
   125  sql.plan.automatic-stats.enabled
   126  
   127  # Test telemetry for manual statistics creation.
   128  feature-whitelist
   129  sql.plan.stats.created
   130  ----
   131  
   132  feature-usage
   133  CREATE STATISTICS stats FROM x
   134  ----
   135  sql.plan.stats.created
   136  
   137  # Test various planning counters.
   138  feature-whitelist
   139  sql.plan.cte.*
   140  sql.plan.lateral-join
   141  sql.plan.subquery.*
   142  ----
   143  
   144  feature-usage
   145  WITH a AS (SELECT 1) SELECT * FROM a
   146  ----
   147  sql.plan.cte
   148  
   149  feature-usage
   150  WITH RECURSIVE a AS (SELECT 1 UNION ALL SELECT * FROM a WHERE false) SELECT * FROM a
   151  ----
   152  sql.plan.cte
   153  sql.plan.cte.recursive
   154  
   155  feature-usage
   156  SELECT * FROM (VALUES (1), (2)) AS a(x), LATERAL (SELECT a.x+1)
   157  ----
   158  sql.plan.lateral-join
   159  
   160  feature-usage
   161  SELECT * FROM (VALUES (1), (2)) AS a(x) JOIN LATERAL (SELECT a.x+1 AS x) AS b ON a.x < b.x
   162  ----
   163  sql.plan.lateral-join
   164  
   165  feature-usage
   166  SELECT 1 = (SELECT a FROM x LIMIT 1)
   167  ----
   168  sql.plan.subquery
   169  
   170  feature-usage
   171  SELECT x FROM (VALUES (1)) AS b(x) WHERE EXISTS(SELECT * FROM (VALUES (1)) AS a(x) WHERE a.x = b.x)
   172  ----
   173  sql.plan.subquery.correlated
   174  
   175  # Test some sql.plan.ops counters, using some esoteric operators unlikely to be
   176  # executed in background activity).
   177  feature-whitelist
   178  sql.plan.ops.cast.string::inet
   179  sql.plan.ops.bin.jsonb - string
   180  sql.plan.ops.array.*
   181  ----
   182  
   183  feature-usage
   184  SELECT '1.2.3.4'::STRING::INET
   185  ----
   186  sql.plan.ops.cast.string::inet
   187  
   188  feature-usage
   189  SELECT '{"a":"b","c":123}'::JSON - 'a'
   190  ----
   191  sql.plan.ops.bin.jsonb - string
   192  
   193  feature-usage
   194  SELECT ARRAY (SELECT 1)[1]
   195  ----
   196  sql.plan.ops.array.flatten
   197  sql.plan.ops.array.ind
   198  
   199  feature-usage
   200  INSERT INTO x SELECT unnest(ARRAY[9, 10, 11, 12])
   201  ----
   202  sql.plan.ops.array.cons
   203  
   204  # Test a few sql.plan.opt.node counters.
   205  feature-whitelist
   206  sql.plan.opt.node.project-set
   207  sql.plan.opt.node.join.*
   208  ----
   209  
   210  feature-usage
   211  SELECT EXISTS(SELECT * FROM generate_series(1,2))
   212  ----
   213  sql.plan.opt.node.project-set
   214  
   215  feature-usage
   216  SELECT * FROM x AS x1 NATURAL INNER MERGE JOIN x AS x2
   217  ----
   218  sql.plan.opt.node.join.algo.merge
   219  sql.plan.opt.node.join.type.inner