sql: query performance regression in pg_catalog query that resolves database objects and their descriptions

[dikshant]

This query is really slow and seems to be a regression in 23.1 compared to 22.2.8:

SELECT
  current_database(), n.nspname, relname, d.description
FROM
  pg_description AS d
  INNER JOIN pg_class AS c ON d.objoid = c.oid
  INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE
  d.objsubid = 0
  AND n.nspname
    NOT IN (
        'gp_toolkit':::STRING:::NAME,
        'information_schema':::STRING:::NAME,
        'pgagent':::STRING:::NAME
      )
  AND n.nspname NOT LIKE 'pg_%';

Explain:

root@localhost:26257/defaultdb> EXPLAIN ANALYZE (VERBOSE) SELECT
                             ->   current_database(), n.nspname, relname, d.description
                             -> FROM
                             ->   pg_description AS d
                             ->   INNER JOIN pg_class AS c ON d.objoid = c.oid
                             ->   INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace
                             -> WHERE
                             ->   d.objsubid = 0
                             ->   AND n.nspname
                             ->     NOT IN (
                             ->         'gp_toolkit':::STRING:::NAME,
                             ->         'information_schema':::STRING:::NAME,
                             ->         'pgagent':::STRING:::NAME
                             ->       )
                             ->   AND n.nspname NOT LIKE 'pg_%';
                                                        info
--------------------------------------------------------------------------------------------------------------------
  planning time: 1ms
  execution time: 4.5s
  distribution: local
  vectorized: true
  maximum memory usage: 310 KiB
  network usage: 0 B (0 messages)
  sql cpu time: 4.5s
  regions: local

  • render
  │ columns: (current_database, nspname, relname, description)
  │ render current_database: 'defaultdb'
  │ render description: description
  │ render relname: relname
  │ render nspname: nspname
  │
  └── • hash join (inner)
      │ columns: (oid, nspname, objoid, objsubid, description, oid, relname, relnamespace)
      │ nodes: n1
      │ regions: local
      │ actual row count: 76
      │ vectorized batch count: 2
      │ estimated max memory allocated: 180 KiB
      │ estimated max sql temp disk usage: 0 B
      │ sql cpu time: 177µs
      │ estimated row count: 156 (missing stats)
      │ equality: (oid) = (relnamespace)
      │
      ├── • filter
      │   │ columns: (oid, nspname)
      │   │ nodes: n1
      │   │ regions: local
      │   │ actual row count: 2
      │   │ vectorized batch count: 2
      │   │ sql cpu time: 9µs
      │   │ estimated row count: 111 (missing stats)
      │   │ filter: (nspname NOT IN ('gp_toolkit', 'information_schema', 'pgagent')) AND (nspname NOT LIKE 'pg_%')
      │   │
      │   └── • virtual table
      │         columns: (oid, nspname)
      │         nodes: n1
      │         regions: local
      │         actual row count: 5
      │         vectorized batch count: 3
      │         sql cpu time: 69µs
      │         estimated row count: 1,000 (missing stats)
      │         table: pg_namespace@primary
      │
      └── • virtual table lookup join (inner)
          │ columns: (objoid, objsubid, description, oid, relname, relnamespace)
          │ nodes: n1
          │ regions: local
          │ actual row count: 294
          │ vectorized batch count: 9
          │ sql cpu time: 4.5s
          │ estimated row count: 99 (missing stats)
          │ table: pg_class@pg_class_oid_idx
          │ equality: (objoid) = (oid)
          │
          └── • filter
              │ columns: (objoid, objsubid, description)
              │ nodes: n1
              │ regions: local
              │ actual row count: 2,230
              │ vectorized batch count: 12
              │ sql cpu time: 14µs
              │ estimated row count: 10 (missing stats)
              │ filter: objsubid = 0
              │
              └── • virtual table
                    columns: (objoid, objsubid, description)
                    nodes: n1
                    regions: local
                    actual row count: 2,230
                    vectorized batch count: 12
                    sql cpu time: 2ms
                    estimated row count: 1,000 (missing stats)
                    table: pg_description@primary
(78 rows)

Time: 4.535s total (execution 4.535s / network 0.000s)

The slow part

      └── • virtual table lookup join (inner)
          │ columns: (objoid, objsubid, description, oid, relname, relnamespace)
          │ nodes: n1
          │ regions: local
          │ actual row count: 294
          │ vectorized batch count: 9
          │ sql cpu time: 4.5s
          │ estimated row count: 99 (missing stats)
          │ table: pg_class@pg_class_oid_idx
          │ equality: (objoid) = (oid)

However on 22.2.8 this is much faster

root@localhost:26257/defaultdb> EXPLAIN ANALYZE (VERBOSE) SELECT                                                                            current_database(), n.nspname, relname, d.description                                                                                   FROM                                                                                                                                        pg_description AS d                                                                                                                       INNER JOIN pg_class AS c ON d.objoid = c.oid                                                                                              INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace                                                                                  WHERE                                                                                                                                       d.objsubid = 0                                                                                                                            AND n.nspname                                                                                                                               NOT IN (                                                                                                                                      'gp_toolkit':::STRING:::NAME,                                                                                                             'information_schema':::STRING:::NAME,                                                                                                     'pgagent':::STRING:::NAME                                                                                                               )                                                                                                                                     AND n.nspname NOT LIKE 'pg_%';
                                                        info
--------------------------------------------------------------------------------------------------------------------
  planning time: 834µs
  execution time: 18ms
  distribution: local
  vectorized: true
  maximum memory usage: 300 KiB
  network usage: 0 B (0 messages)
  regions: local

  • render
  │ columns: (current_database, nspname, relname, description)
  │ render current_database: 'defaultdb'
  │ render description: description
  │ render relname: relname
  │ render nspname: nspname
  │
  └── • hash join (inner)
      │ columns: (oid, nspname, objoid, objsubid, description, oid, relname, relnamespace)
      │ nodes: n1
      │ regions: local
      │ actual row count: 64
      │ vectorized batch count: 2
      │ estimated max memory allocated: 190 KiB
      │ estimated max sql temp disk usage: 0 B
      │ estimated row count: 156 (missing stats)
      │ equality: (oid) = (relnamespace)
      │
      ├── • filter
      │   │ columns: (oid, nspname)
      │   │ nodes: n1
      │   │ regions: local
      │   │ actual row count: 2
      │   │ vectorized batch count: 2
      │   │ estimated row count: 111 (missing stats)
      │   │ filter: (nspname NOT IN ('gp_toolkit', 'information_schema', 'pgagent')) AND (nspname NOT LIKE 'pg_%')
      │   │
      │   └── • virtual table
      │         columns: (oid, nspname)
      │         nodes: n1
      │         regions: local
      │         actual row count: 5
      │         vectorized batch count: 3
      │         estimated row count: 1,000 (missing stats)
      │         table: pg_namespace@primary
      │
      └── • virtual table lookup join (inner)
          │ columns: (objoid, objsubid, description, oid, relname, relnamespace)
          │ nodes: n1
          │ regions: local
          │ actual row count: 282
          │ vectorized batch count: 9
          │ estimated row count: 99 (missing stats)
          │ table: pg_class@pg_class_oid_idx
          │ equality: (objoid) = (oid)
          │
          └── • filter
              │ columns: (objoid, objsubid, description)
              │ nodes: n1
              │ regions: local
              │ actual row count: 282
              │ vectorized batch count: 9
              │ estimated row count: 10 (missing stats)
              │ filter: objsubid = 0
              │
              └── • virtual table
                    columns: (objoid, objsubid, description)
                    nodes: n1
                    regions: local
                    actual row count: 282
                    vectorized batch count: 9
                    estimated row count: 1,000 (missing stats)
                    table: pg_description@primary
(71 rows)


Time: 20ms total (execution 19ms / network 0ms)

root@localhost:26257/defaultdb>

Statement Bundle

Commit used to build cockroach: f8523ec

Jira issue: CRDB-27560

[mgartner]

Adding a S-2 label because this is a regression from v22.2, and I want to prioritize looking into it.

[mgartner]

Here's a simplified reproduction:

SELECT relname, d.description
FROM pg_description AS d
  INNER JOIN pg_class AS c ON d.objoid = c.oid
WHERE d.objsubid = 0;

On v22.2.6 in the demo it takes ~10ms. On master it takes ~4.5s.

[rafiss]

when we fix this, let's be sure to add the repro into pkg/bench/rttanalysis/orm_queries_bench_test.go

[DrewKimball]

CPU profile one a single node repeatedly running the simplified repro:

[rafiss]

We saw this problem come up in #102851. @fqazi tracked it down to the fact that populating pg_description fetches all descriptors to load comments. We'll work on this in our next milestone.