sql,cli: \d metacommand should not show indexes by default

[rafiss]

Describe the problem

In psql, the \d metacommand does not show indexes, but in cockroach sql, it does.

From the psql documentation:

If \d is used without a pattern argument, it is equivalent to \dtvmsE which will show a list of all visible tables, views, materialized views, sequences and foreign tables. This is purely a convenience measure.

cockroach sql appears to treat it as \dtivmsE instead.

To Reproduce

Setup with:

create table tbl(a int primary key, b int, index (b));

With PSQL

defaultdb=> \d
                List of relations
    Schema    |       Name        | Type  | Owner
--------------+-------------------+-------+-------
 pg_extension | geography_columns | table | node
 pg_extension | geometry_columns  | table | node
 pg_extension | spatial_ref_sys   | table | node
 public       | tbl               | table | root
(4 rows)

With Cockroach SQL

root@localhost:26257/defaultdb> \d
List of relations:
 Schema |   Name    | Type  | Owner | Table
---------+-----------+-------+-------+--------
 public | tbl       | table | root  | NULL
 public | tbl_b_idx | index | root  | tbl
 public | tbl_pkey  | index | root  | tbl
(3 rows)

Expected behavior
Only tbl should appear in the results. (Postgres also shows some tables from pg_extension, but let's ignore those for now.)

Additional data / screenshots
I captured the queries that were running.

In psql:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

In cockroach sql:

   SELECT n.nspname as "Schema",
          c.relname as "Name",
          CASE c.relkind
          WHEN 'r' THEN 'table'
          WHEN 'v' THEN 'view'
          WHEN 'm' THEN 'materialized view'
          WHEN 'i' THEN 'index'
          WHEN 'S' THEN 'sequence'
          WHEN 's' THEN 'special'
          WHEN 't' THEN 'TOAST table'
          WHEN 'f' THEN 'foreign table'
          WHEN 'p' THEN 'partitioned table'
          WHEN 'I' THEN 'partitioned index'
          END as "Type",
          pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
          c2.relname AS "Table"
     FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n on n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
    WHERE c.relkind IN ('r','p','v','m','i','S','f','')
      AND n.nspname !~ '^pg_'
      AND n.nspname <> 'information_schema'
      AND n.nspname <> 'crdb_internal'
      AND pg_catalog.pg_table_is_visible(c.oid)
 ORDER BY 1,2

Additional context
See slack thread: https://cockroachlabs.slack.com/archives/C0168LW5THS/p1715331247139219

Jira issue: CRDB-38618

[sean-]

This has always been a UX surprise, and there's no good reason that I can think of why we wouldn't want to be compatible with PostgreSQL. The muscle memory runs deep.