sql/stats: after inject or restore of partial statistics, cannot create merged statistics

[faizaanmadhani]

Now that users can collect partial statistics at the extremes of columns using CREATE STATISTICS ON <col_name> FROM <table_name> USING EXTREMES, ensure that statements bundles include both full statistics and partial statistics.

The other thing we need to be sure of is that merged statistics are correctly created when injecting statment bundle stats so that we can mimic customer behavior.

Jira issue: CRDB-22695

Epic CRDB-25383

[michae2]

Good news is that we are including partial statistics in statement bundles, so the first part is already done.

Bad news is that the second part still needs work. After ALTER TABLE INJECT STATISTICS (or a restore) we cannot produce the original merged statistics because the statisticID / fullStatisticID relationship is not set up correctly. Here's a demonstration:

CREATE TABLE uw (name TEXT PRIMARY KEY, location GEOGRAPHY(POINT)) WITH (sql_stats_automatic_collection_enabled = false);
INSERT INTO uw VALUES ('Washington', 'POINT(-122.30 47.66)'), ('Waterloo', 'POINT(-80.54 43.47)'), ('Wisconsin', 'POINT(-89.42 43.08)');
CREATE STATISTICS uwfull FROM uw;
INSERT INTO uw VALUES ('Warsaw', 'POINT(21.02 52.24)'), ('Winnipeg', 'POINT(-97.15 49.89)'), ('Wyoming', 'POINT(-105.57 41.31)');
CREATE STATISTICS uwpartial ON name FROM uw USING EXTREMES;
SHOW STATISTICS FOR TABLE uw WITH MERGE;
EXPLAIN SELECT * FROM uw;
EXPLAIN ANALYZE (DEBUG) SELECT * FROM uw;

-- Then download and unzip the bundle, and run `cockroach debug statement-bundle recreate`. In that session:

SHOW STATISTICS FOR TABLE uw WITH MERGE;
EXPLAIN SELECT * FROM uw;

Before recreating the bundle this looks like:

demo@127.0.0.1:26257/defaultdb> SHOW STATISTICS FOR TABLE uw WITH MERGE;
  statistics_name | column_names |          created           | row_count | distinct_count | null_count | avg_size |                                  partial_predicate                                  |    histogram_id    | full_histogram_id
------------------+--------------+----------------------------+-----------+----------------+------------+----------+-------------------------------------------------------------------------------------+--------------------+---------------------
  uwfull          | {name}       | 2023-01-13 19:05:17.304051 |         3 |              3 |          0 |       12 | NULL                                                                                | 830886108550234113 |                  0
  uwfull          | {location}   | 2023-01-13 19:05:17.304051 |         3 |              3 |          0 |       75 | NULL                                                                                |               NULL |                  0
  uwpartial       | {name}       | 2023-01-13 19:06:20.958132 |         2 |              2 |          0 |       10 | (name IS NULL) OR ((name < 'Washington':::STRING) OR (name > 'Wisconsin':::STRING)) | 830886317125959681 | 830886108550234113
  __merged__      | {name}       | 2023-01-13 19:06:20.958132 |         5 |              5 |          0 |       11 | NULL                                                                                |                  0 |               NULL
(4 rows)

Time: 6ms total (execution 6ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> EXPLAIN SELECT * FROM uw;
                                     info
-------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • scan
    estimated row count: 5 (100% of the table; stats collected 3 minutes ago)
    table: uw@uw_pkey
    spans: FULL SCAN
(7 rows)

Time: 1ms total (execution 1ms / network 0ms)

After recreating the bundle we're missing the merged stats:

demo@127.0.0.1:26257/defaultdb> SHOW STATISTICS FOR TABLE uw WITH MERGE;
  statistics_name | column_names |          created           | row_count | distinct_count | null_count | avg_size |                                  partial_predicate                                  |    histogram_id    | full_histogram_id
------------------+--------------+----------------------------+-----------+----------------+------------+----------+-------------------------------------------------------------------------------------+--------------------+---------------------
  uwfull          | {name}       | 2023-01-13 19:05:17.304051 |         3 |              3 |          0 |       12 | NULL                                                                                | 830888138034216961 |               NULL
  uwfull          | {location}   | 2023-01-13 19:05:17.304051 |         3 |              3 |          0 |       75 | NULL                                                                                |               NULL |               NULL
  uwpartial       | {name}       | 2023-01-13 19:06:20.958132 |         2 |              2 |          0 |       10 | (name IS NULL) OR ((name < 'Washington':::STRING) OR (name > 'Wisconsin':::STRING)) | 830888138038083585 | 830886108550234113
(3 rows)

Time: 3ms total (execution 3ms / network 0ms)

demo@127.0.0.1:26257/defaultdb> EXPLAIN SELECT * FROM uw;
                                      info
--------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • scan
    estimated row count: 3 (100% of the table; stats collected 11 minutes ago)
    table: uw@uw_pkey
    spans: FULL SCAN
(7 rows)

Time: 4ms total (execution 4ms / network 0ms)

I'm going to change this issue to be about this second problem.

[michae2]

As a workaround we can fix the restored / injected partial statistics with something like:

UPDATE
  system.table_statistics AS p
SET
  "fullStatisticID"
    = (
      SELECT
        "statisticID"
      FROM
        system.table_statistics AS f
      WHERE
        f."tableID" = p."tableID"
        AND f."columnIDs" = p."columnIDs"
        AND f."createdAt" < p."createdAt"
        AND f."partialPredicate" IS NULL
      ORDER BY
        f."createdAt" DESC
      LIMIT
        1
    )
WHERE
  p."tableID" = 'uw'::REGCLASS::INT8
  AND p."createdAt" > '2023-01-13'
  AND p."partialPredicate" IS NOT NULL
RETURNING
  p."tableID",
  p."columnIDs",
  p.name,
  p."statisticID",
  p."fullStatisticID";

But this won't always be correct, so I think it would be better to really fix this. Probably by changing

cockroach/pkg/sql/stats/new_stat.go

Lines 102 to 122 in 6f5d89e

	_, err := executor.Exec(
	ctx, "insert-statistic", txn,
	`INSERT INTO system.table_statistics (
	"tableID",
	"name",
	"columnIDs",
	"rowCount",
	"distinctCount",
	"nullCount",
	"avgSize",
	histogram
	) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`,
	tableID,
	nameVal,
	columnIDsVal,
	rowCount,
	distinctCount,
	nullCount,
	avgSize,
	histogramVal,
	)

to not throw away the statisticID.

[mgartner]

@michae2 can we push this to 23.2 given that we'll disable partial statistics by default in 23.1 (assuming we do #95233).