sql: 100x performance degradation in pgjdbc query to fetch columns

**Describe the problem**

I was running the liquibase integration tests and encountered a panic:

```
* ERROR: [n1,client=[::1]:65462,hostnossl,user=root] a panic has occurred!
* txn: no bytes in account to release, current 47738, free 47764
* (1) attached stack trace
*   -- stack trace:
*   | github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn.func1
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:508
*   | runtime.gopanic
*   | 	/usr/local/opt/go@1.13/libexec/src/runtime/panic.go:679
*   | github.com/cockroachdb/cockroach/pkg/util/log.ReportOrPanic
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/util/log/crash_reporting.go:340
*   | github.com/cockroachdb/cockroach/pkg/util/mon.(*BoundAccount).Shrink
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/util/mon/bytes_usage.go:583
*   | github.com/cockroachdb/cockroach/pkg/sql/rowcontainer.(*RowContainer).PopFirst
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowcontainer/datum_row_container.go:298
*   | github.com/cockroachdb/cockroach/pkg/sql.(*vTableLookupJoinNode).Next
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/virtual_table.go:275
*   | github.com/cockroachdb/cockroach/pkg/sql.(*planNodeToRowSource).Next
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/plan_node_to_row_source.go:178
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*hashJoiner).receiveNext
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/hashjoiner.go:656
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*hashJoiner).build
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/hashjoiner.go:303
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*hashJoiner).Next
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/hashjoiner.go:237
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*windower).accumulateRows
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/windower.go:282
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*windower).Next
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/windower.go:234
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*sortAllProcessor).fill
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/sorter.go:291
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*sortAllProcessor).Start
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/sorter.go:272
*   | github.com/cockroachdb/cockroach/pkg/sql/execinfra.(*ProcessorBase).Run
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/execinfra/processorsbase.go:774
*   | github.com/cockroachdb/cockroach/pkg/sql/flowinfra.(*FlowBase).Run
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/flowinfra/flow.go:392
*   | github.com/cockroachdb/cockroach/pkg/sql.(*DistSQLPlanner).Run
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:422
*   | github.com/cockroachdb/cockroach/pkg/sql.(*DistSQLPlanner).PlanAndRun
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:991
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execWithDistSQLEngine
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1002
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).dispatchToExecutionEngine
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:873
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmtInOpenState
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:639
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmt
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:114
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1472
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).run
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1401
*   | github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:510
*   | github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*conn).processCommandsAsync.func1
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:627
* Wraps: (2) while executing: SELECT * FROM (SELECT _._, _._, _._, _._, _._ OR ((_._ = _) AND _._) AS _, _._, _._, _._, row_number() OVER (PARTITION BY _._ ORDER BY _._) AS _, _ AS _, pg_get_expr(_._, _._) AS _, _._, _._, _._ FROM _._ AS _ JOIN _._ AS _ ON (_._ = _._) JOIN _._ AS _ ON (_._ = _._) JOIN _._ AS _ ON (_._ = _._) LEFT JOIN _._ AS _ ON ((_._ = _._) AND (_._ = _._)) LEFT JOIN _._ AS _ ON ((_._ = _._) AND (_._ = _._)) LEFT JOIN _._ AS _ ON ((_._ = _._) AND (_._ = _)) LEFT JOIN _._ AS _ ON ((_._ = _._) AND (_._ = _)) WHERE ((((_._ IN (_, _, __more3__)) AND (_._ > _)) AND (NOT _._)) AND (_._ LIKE _)) AND (_._ LIKE _)) AS _ WHERE _ AND (_ LIKE _) ORDER BY _, _._, _
* Wraps: (3) attached stack trace
*   -- stack trace:
*   | github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn.func1
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:508
*   | runtime.gopanic
*   | 	/usr/local/opt/go@1.13/libexec/src/runtime/panic.go:679
*   | [...repeated from below...]
* Wraps: (4) attached stack trace
*   -- stack trace:
*   | github.com/cockroachdb/cockroach/pkg/util/log.ReportOrPanic
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/util/log/crash_reporting.go:338
*   | github.com/cockroachdb/cockroach/pkg/util/mon.(*BoundAccount).Shrink
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/util/mon/bytes_usage.go:583
*   | github.com/cockroachdb/cockroach/pkg/sql/rowcontainer.(*RowContainer).PopFirst
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowcontainer/datum_row_container.go:298
*   | github.com/cockroachdb/cockroach/pkg/sql.(*vTableLookupJoinNode).Next
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/virtual_table.go:275
*   | github.com/cockroachdb/cockroach/pkg/sql.(*planNodeToRowSource).Next
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/plan_node_to_row_source.go:178
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*hashJoiner).receiveNext
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/hashjoiner.go:656
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*hashJoiner).build
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/hashjoiner.go:303
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*hashJoiner).Next
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/hashjoiner.go:237
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*windower).accumulateRows
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/windower.go:282
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*windower).Next
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/windower.go:234
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*sortAllProcessor).fill
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/sorter.go:291
*   | github.com/cockroachdb/cockroach/pkg/sql/rowexec.(*sortAllProcessor).Start
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/rowexec/sorter.go:272
*   | github.com/cockroachdb/cockroach/pkg/sql/execinfra.(*ProcessorBase).Run
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/execinfra/processorsbase.go:774
*   | github.com/cockroachdb/cockroach/pkg/sql/flowinfra.(*FlowBase).Run
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/flowinfra/flow.go:392
*   | github.com/cockroachdb/cockroach/pkg/sql.(*DistSQLPlanner).Run
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:422
*   | github.com/cockroachdb/cockroach/pkg/sql.(*DistSQLPlanner).PlanAndRun
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/distsql_running.go:991
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execWithDistSQLEngine
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:1002
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).dispatchToExecutionEngine
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:873
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmtInOpenState
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:639
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execStmt
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor_exec.go:114
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).execCmd
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1472
*   | github.com/cockroachdb/cockroach/pkg/sql.(*connExecutor).run
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:1401
*   | github.com/cockroachdb/cockroach/pkg/sql.(*Server).ServeConn
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/conn_executor.go:510
*   | github.com/cockroachdb/cockroach/pkg/sql/pgwire.(*conn).processCommandsAsync.func1
*   | 	/Users/rafiss/go/src/github.com/cockroachdb/cockroach/pkg/sql/pgwire/conn.go:627
*   | runtime.goexit
*   | 	/usr/local/opt/go@1.13/libexec/src/runtime/asm_amd64.s:1357
* Wraps: (5) txn: no bytes in account to release, current 47738, free 47764
* Error types: (1) *withstack.withStack (2) *safedetails.withSafeDetails (3) *withstack.withStack (4) *withstack.withStack (5) *errutil.leafError
```

**To Reproduce**

Ran the Liquibase integration tests on a fresh cluster.

This is the query that caused the panic:
```
SELECT
  *
FROM
  (
    SELECT
      n.nspname,
      c.relname,
      a.attname,
      a.atttypid,
      a.attnotnull
      OR ((t.typtype = 'd') AND t.typnotnull)
        AS attnotnull,
      a.atttypmod,
      a.attlen,
      t.typtypmod,
      row_number() OVER (
        PARTITION BY a.attrelid ORDER BY a.attnum
      )
        AS attnum,
      NULL AS attidentity,
      pg_get_expr(def.adbin, def.adrelid) AS adsrc,
      dsc.description,
      t.typbasetype,
      t.typtype
    FROM
      pg_catalog.pg_namespace AS n
      JOIN pg_catalog.pg_class AS c ON (c.relnamespace = n.oid)
      JOIN pg_catalog.pg_attribute AS a ON (a.attrelid = c.oid)
      JOIN pg_catalog.pg_type AS t ON (a.atttypid = t.oid)
      LEFT JOIN pg_catalog.pg_attrdef AS def ON
          (
            (a.attrelid = def.adrelid)
            AND (a.attnum = def.adnum)
          )
      LEFT JOIN pg_catalog.pg_description AS dsc ON
          ((c.oid = dsc.objoid) AND (a.attnum = dsc.objsubid))
      LEFT JOIN pg_catalog.pg_class AS dc ON
          (
            (dc.oid = dsc.classoid)
            AND (dc.relname = 'pg_class')
          )
      LEFT JOIN pg_catalog.pg_namespace AS dn ON
          (
            (dc.relnamespace = dn.oid)
            AND (dn.nspname = 'pg_catalog')
          )
    WHERE
      (
        (
          (
            (c.relkind IN ('r', 'p', 'v', 'f', 'm'))
            AND (a.attnum > 0)
          )
          AND (NOT a.attisdropped)
        )
        AND (n.nspname LIKE 'public')
      )
      AND (c.relname LIKE '%')
  )
    AS c
WHERE
  true AND (attname LIKE '%')
ORDER BY
  nspname, c.relname, attnum;
```

But running just the query on a fresh cluster is fine. I haven't figured out what preceding steps would make this panic.

**Environment:**
Using CockroachDB master (sha 33c1bb6595)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sql: 100x performance degradation in pgjdbc query to fetch columns #55140

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

sql: 100x performance degradation in pgjdbc query to fetch columns #55140

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions