SHOW INDEX/ES stmts information about computed indexes is misleading

[lopezator]

Is your feature request related to a problem? Please describe.

Imagine the following schema:

CREATE TABLE foo (id string PRIMARY key, json JSON);

With the following index:

CREATE INDEX foo__json__bar ON foo (id, (json->>'bar'));

If I use my IDE to get information about which JSON property I am indexing, I get this:

Just crdb_internal_idx_expr which is not very useful.

Which is indeed the same information I get if I execute the following stmt:

SHOW INDEXES FROM foo

  table_name |   index_name   | non_unique | seq_in_index |      column_name       | direction | storing | implicit | visible
-------------+----------------+------------+--------------+------------------------+-----------+---------+----------+----------
  foo        | foo__json__bar |     t      |            1 | id                     | ASC       |    f    |    f     |    t
  foo        | foo__json__bar |     t      |            2 | crdb_internal_idx_expr | ASC       |    f    |    f     |    t
  foo        | foo_pkey       |     f      |            1 | id                     | ASC       |    f    |    f     |    t
  foo        | foo_pkey       |     f      |            2 | json                   | N/A       |    t    |    f     |    t
(4 rows)

To get useful information about the index, I have to use:

SHOW CREATE TABLE foo

  table_name |                        create_statement
-------------+-----------------------------------------------------------------
  foo        | CREATE TABLE public.foo (
             |     id STRING NOT NULL,
             |     json JSONB NULL,
             |     CONSTRAINT foo_pkey PRIMARY KEY (id ASC),
             |     INDEX foo__json__bar (id ASC, (json->>'bar':::STRING) ASC)
             | )
(1 row)

Where I can see the useful piece of information I was looking for, but it seems a bit misleading having to use SHOW CREATE TABLE to gather information about an index. IMHO SHOW INDEXES returning it would be awesome, and possibly the IDEs could benefit from this feature as well.

Describe the solution you'd like

Return the json/computed column information in the SHOW INDEXES stmt:

INDEX foo__json__bar (id ASC, (json->>'bar':::STRING) ASC)

Describe alternatives you've considered

I can use SHOW CREATE TABLE foo for now, but it's not very intuitive.

Additional context

Thank you Shaun (Cockroach Labs) from CockroachDB Community Slack for the useful tip!

Jira issue: CRDB-23086

Epic CRDB-23454

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

Thanks for the request! Out of curiosity, do you know what information the IDE would display if you created the same kind of index in PostgreSQL? There may be a pg_catalog table that we should populate.

[lopezator]

Thanks for the request! Out of curiosity, do you know what information the IDE would display if you created the same kind of index in PostgreSQL? There may be a pg_catalog table that we should populate.

Hello! In postgres the feedback is more descriptive 🙂

Let me know if you need something else.

Thank you for the quick reply @rafiss !!