sql: ANALYZE doesn't collect inverted stats on columns that are indexed with both forward and inverted indexes

[jordanlewis]

It appears that when a column is indexed with both a forward and inverted index, only forward statistics are collected for that column. Both inverted and forward statistics should be collected, though.

See commentary on #91762 for more information.

Hmm, I'm actually not sure how to prove whether collected statistics are inverted or not, so it's hard for me to make an experiment that explains what's going on. I think the easiest way to see it is that you'd expect that there would be two statistics rows for a column that's both inverted and forward indexed, and that doesn't seem to happen:

demo@127.0.0.1:26257/defaultdb> create table a (a int primary key, b text);
demo@127.0.0.1:26257/defaultdb> insert into a values(1, 'hi');
demo@127.0.0.1:26257/defaultdb> create index on a(b);
demo@127.0.0.1:26257/defaultdb> create index on a using gin(b gin_trgm_ops);
demo@127.0.0.1:26257/defaultdb> analyze a;
demo@127.0.0.1:26257/defaultdb> show statistics for table a;                                                                                                                                                statistics_name | column_names |          created           | row_count | distinct_count | null_count | avg_size |    histogram_id
------------------+--------------+----------------------------+-----------+----------------+------------+----------+---------------------
  NULL            | {a}          | 2022-11-17 05:40:59.977253 |         1 |              1 |          0 |        1 | 814590394299416577
  NULL            | {b}          | 2022-11-17 05:40:59.977253 |         1 |              1 |          0 |        4 | 814590394305314817
(2 rows)

Jira issue: CRDB-21530

[michae2]

Here's a query that can be used to show which type of histogram is created:

SELECT
  stat->>'name' AS name,
  stat->>'created_at' AS created_at,
  stat->>'histo_col_type' AS histo_col_type
FROM
  (
    SELECT
      jsonb_array_elements(statistics) AS stat
    FROM
      [SHOW STATISTICS USING JSON FOR TABLE a]
  )
WHERE
  stat->'columns' = '["b"]';

Interestingly, it looks like the order of index creation matters. If the inverted index is created first, that will be the type of histogram when both indexes exist.