cockroachdb / cockroachdb/cockroach

sql: add after trigger routines to EXPLAIN ANALYZE output

Open
#172,280 1 comment 0 reactions 1 assignee Claimed by @ZhouXing19 View on GitHub
A-sql-explain A-sql-routine A-sql-udf C-enhancement docs-known-limitation O-qa T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Now that we show routine plans in EXPLAIN ANALYZE, we can see plans for before trigger routines as well. But after trigger routines don't show up. Here's a repro:

```sql
CREATE TABLE a (a INT);
CREATE TABLE b (b INT);
CREATE TABLE c (c INT);

CREATE FUNCTION fa() RETURNS TRIGGER LANGUAGE PLpgSQL AS $$
BEGIN
INSERT INTO a VALUES (NEW.c);
RETURN NEW;
END;
$$;

CREATE FUNCTION fb() RETURNS TRIGGER LANGUAGE PLpgSQL AS $$
BEGIN
INSERT INTO b VALUES (NEW.c);
RETURN NEW;
END;
$$;

CREATE TRIGGER tb BEFORE INSERT ON c FOR EACH ROW EXECUTE FUNCTION fb();
CREATE TRIGGER ta AFTER INSERT ON c FOR EACH ROW EXECUTE FUNCTION fa();

EXPLAIN ANALYZE INSERT INTO c VALUES (1);
```

This shows:

```
planning time: 259µs
execution time: 813µs
distribution: local
plan type: custom
cumulative time spent waiting in admission control: 1µs
regions: us-east1
estimated RUs consumed: 0

• root

├── • insert
│ │ sql nodes: n1
│ │ regions: us-east1
│ │ execution time: 47µs
│ │ sql cpu time: 16µs
│ │ actual row count: 1
│ │ into: c(c, rowid)
│ │
│ ├── • before-triggers
│ │ trigger: tb
│ │
│ └── • buffer
│ │ sql nodes: n1
│ │ regions: us-east1
│ │ execution time: 17µs
│ │ sql cpu time: 17µs
│ │ actual row count: 1
│ │ label: buffer 1
│ │
│ └── • render
│ │ execution time: 26µs
│ │ sql cpu time: 26µs
│ │ actual row count: 1
│ │
│ └── • filter
│ │ sql nodes: n1
│ │ regions: us-east1
│ │ execution time: 1µs
│ │ sql cpu time: 1µs
│ │ actual row count: 1
│ │ estimated row count: 1
│ │ filter: fb IS DISTINCT FROM NULL
│ │
│ └── • render
│ │ execution time: 333µs
│ │ sql cpu time: 250µs
│ │ actual row count: 1
│ │
│ └── • values
│ sql nodes: n1
│ regions: us-east1
│ execution time: 5µs
│ sql cpu time: 5µs
│ actual row count: 1
│ size: 3 columns, 1 row

├── • after-triggers
│ │ trigger: ta
│ │
│ └── • render
│ │ execution time: 150µs
│ │ sql cpu time: 150µs
│ │ actual row count: 1
│ │
│ └── • render
│ │ execution time: 3µs
│ │ sql cpu time: 3µs
│ │ actual row count: 1
│ │
│ └── • scan buffer
│ sql nodes: n1
│ regions: us-east1
│ execution time: 3µs
│ sql cpu time: 3µs
│ actual row count: 1
│ estimated row count: 1
│ label: buffer 1000000

├── • routine
│ │ name: fb
│ │ invocations: 1
│ │
│ └── • body stmt
│ │
│ └── • values
│ sql nodes: n1
│ regions: us-east1
│ execution time: 3µs
│ sql cpu time: 3µs
│ actual row count: 1
│ size: 1 column, 1 row

└── • routine
│ name: _stmt_exec_1
│ invocations: 1

├── • body stmt
│ │ sql: INSERT INTO defaultdb.public.b VALUES (new.c)
│ │
│ └── • insert fast path
│ sql nodes: n1
│ regions: us-east1
│ execution time: 182µs
│ sql cpu time: 14µs
│ actual row count: 1
│ into: b(b, rowid)
│ size: 2 columns, 1 row

└── • body stmt

└── • values
sql nodes: n1
regions: us-east1
execution time: 3µs
sql cpu time: 3µs
actual row count: 1
size: 1 column, 1 row
(117 rows)

Time: 2ms total (execution 1ms / network 1ms)
```

Jira issue: CRDB-65492

Epic CRDB-66209

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.