github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/stats/delete_stats.go (about)

     1  // Copyright 2018 The Cockroach Authors.
     2  //
     3  // Use of this software is governed by the Business Source License
     4  // included in the file licenses/BSL.txt.
     5  //
     6  // As of the Change Date specified in that file, in accordance with
     7  // the Business Source License, use of this software will be governed
     8  // by the Apache License, Version 2.0, included in the file
     9  // licenses/APL.txt.
    10  
    11  package stats
    12  
    13  import (
    14  	"context"
    15  
    16  	"github.com/cockroachdb/cockroach/pkg/kv"
    17  	"github.com/cockroachdb/cockroach/pkg/sql/sem/tree"
    18  	"github.com/cockroachdb/cockroach/pkg/sql/sqlbase"
    19  	"github.com/cockroachdb/cockroach/pkg/sql/sqlutil"
    20  	"github.com/cockroachdb/cockroach/pkg/sql/types"
    21  )
    22  
    23  const (
    24  	// keepCount is the number of automatic statistics to keep for a given
    25  	// table and set of columns when deleting old stats. The purpose of keeping
    26  	// several old automatic statistics is to be able to track the amount of
    27  	// time between refreshes. See comments in automatic_stats.go for more
    28  	// details.
    29  	keepCount = 4
    30  )
    31  
    32  // DeleteOldStatsForColumns deletes old statistics from the
    33  // system.table_statistics table. For the given tableID and columnIDs,
    34  // DeleteOldStatsForColumns keeps the most recent keepCount automatic
    35  // statistics and deletes all the others.
    36  func DeleteOldStatsForColumns(
    37  	ctx context.Context,
    38  	executor sqlutil.InternalExecutor,
    39  	txn *kv.Txn,
    40  	tableID sqlbase.ID,
    41  	columnIDs []sqlbase.ColumnID,
    42  ) error {
    43  	columnIDsVal := tree.NewDArray(types.Int)
    44  	for _, c := range columnIDs {
    45  		if err := columnIDsVal.Append(tree.NewDInt(tree.DInt(int(c)))); err != nil {
    46  			return err
    47  		}
    48  	}
    49  
    50  	// This will delete all old statistics for the given table and columns,
    51  	// including stats created manually (except for a few automatic statistics,
    52  	// which are identified by the name AutoStatsName).
    53  	_, err := executor.Exec(
    54  		ctx, "delete-statistics", txn,
    55  		`DELETE FROM system.table_statistics
    56                 WHERE "tableID" = $1
    57                 AND "columnIDs" = $3
    58                 AND "statisticID" NOT IN (
    59                     SELECT "statisticID" FROM system.table_statistics
    60                     WHERE "tableID" = $1
    61                     AND "name" = $2
    62                     AND "columnIDs" = $3
    63                     ORDER BY "createdAt" DESC
    64                     LIMIT $4
    65                 )`,
    66  		tableID,
    67  		AutoStatsName,
    68  		columnIDsVal,
    69  		keepCount,
    70  	)
    71  	return err
    72  }