cockroachdb / cockroachdb/cockroach
sql: add after trigger routines to EXPLAIN ANALYZE output
- 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
Assessment
This issue has not been assessed yet.