Skip to content

Instantly share code, notes, and snippets.

@jordanlewis

jordanlewis/gist:cb6c74f5caafba69e61cea0dcade4491

Created January 16, 2019 18:59
Show Gist options
  • Save jordanlewis/cb6c74f5caafba69e61cea0dcade4491 to your computer and use it in GitHub Desktop.
Save jordanlewis/cb6c74f5caafba69e61cea0dcade4491 to your computer and use it in GitHub Desktop.
Download ZIP
log of cockroach demo for explain meeting
Raw
gistfile1.txt
```
[13:09]% ./cockroach demo jordan@Termato:~/repo/src/github.com/cockroachdb/cockroach on master -> origin/master
#
# Welcome to the CockroachDB demo database!
#
# You are connected to a temporary, in-memory CockroachDB
# instance. Your changes will not be saved!
#
# Web UI: http://127.0.0.1:63371
#
# Server version: CockroachDB CCL v2.2.0-alpha.20181217-403-g5e14c5e8fd (x86_64-apple-darwin18.2.0, built 2019/01/05 00:19:05, go1.11) (same version as client)
# Cluster ID: e42c810b-e6e5-473a-a4e0-e44bc8465b83
#
# Enter \? for a brief introduction.
#
[email protected]:63370/defaultdb> create table a (a int primary key, b int, c int);
CREATE TABLE
Time: 4.266ms
[email protected]:63370/defaultdb> create index a(b);
invalid syntax: statement ignored: syntax error at or near "("
DETAIL: source SQL:
create index a(b)
^
HINT: try \h CREATE INDEX
[email protected]:63370/defaultdb> create index on a(b);
CREATE INDEX
Time: 77.509ms
[email protected]:63370/defaultdb> explain select * from a where c=1;
tree | field | description
+------+--------+-------------+
scan | |
| table | a@primary
| spans | ALL
| filter | c = 1
(4 rows)
Time: 3.553ms
[email protected]:63370/defaultdb> explain select * from a where a=1;
tree | field | description
+------+-------+-------------+
scan | |
| table | a@primary
| spans | /1-/1/#
(3 rows)
Time: 2.551ms
[email protected]:63370/defaultdb> /1 /2 /3
->
->
->
-> ;
invalid syntax: statement ignored: syntax error at or near "/"
DETAIL: source SQL:
/1 /2 /3
^
[email protected]:63370/defaultdb> explain select * from a where a=1 or a=2;
tree | field | description
+------+----------+-------------+
scan | |
| table | a@primary
| spans | /1-/2/#
| parallel |
(4 rows)
Time: 3.451ms
[email protected]:63370/defaultdb> show create table a;
table_name | create_statement
+------------+-----------------------------------------------+
a | CREATE TABLE a (
| a INT8 NOT NULL,
| b INT8 NULL,
| c INT8 NULL,
| CONSTRAINT "primary" PRIMARY KEY (a ASC),
| INDEX a_b_idx (b ASC),
| FAMILY "primary" (a, b, c)
| )
(1 row)
Time: 9.339ms
[email protected]:63370/defaultdb> explain select a from a where b = 3;
tree | field | description
+-----------+-------+-------------+
render | |
└── scan | |
| table | a@a_b_idx
| spans | /3-/4
(4 rows)
Time: 609µs
[email protected]:63370/defaultdb> explain select c from a where b = 3;
tree | field | description
+-----------------+-------+-------------+
render | |
└── index-join | |
├── scan | |
│ | table | a@a_b_idx
│ | spans | /3-/4
└── scan | |
| table | a@primary
(7 rows)
Time: 1.595ms
[email protected]:63370/defaultdb>
[email protected]:63370/defaultdb>
[email protected]:63370/defaultdb> explain select distinct b from a;
tree | field | description
+-----------+-------------+-------------+
distinct | |
│ | distinct on | b
│ | order key | b
└── scan | |
| table | a@a_b_idx
| spans | ALL
(6 rows)
Time: 4.686ms
[email protected]:63370/defaultdb> explain select sum(a) from a group by b;
tree | field | description
+----------------+-------------+-------------+
render | |
└── group | |
│ | aggregate 0 | b
│ | aggregate 1 | sum(a)
│ | group by | @2
│ | ordered | @2
└── scan | |
| table | a@a_b_idx
| spans | ALL
(9 rows)
Time: 3.633ms
[email protected]:63370/defaultdb> explain select sum(a) from a group by c;
tree | field | description
+----------------+-------------+-------------+
render | |
└── group | |
│ | aggregate 0 | c
│ | aggregate 1 | sum(a)
│ | group by | @2
└── scan | |
| table | a@primary
| spans | ALL
(8 rows)
Time: 1.896ms
[email protected]:63370/defaultdb> create table b (a int primary key, b int, c int);
CREATE TABLE
Time: 3.895ms
[email protected]:63370/defaultdb> explain select * from a join b on a.a = b.a;
tree | field | description
+-----------+----------------+-------------+
join | |
│ | type | inner
│ | equality | (a) = (a)
│ | mergeJoinOrder | +"(a=a)"
├── scan | |
│ | table | a@primary
│ | spans | ALL
└── scan | |
| table | b@primary
| spans | ALL
(10 rows)
Time: 12.959ms
[email protected]:63370/defaultdb> create table c (a int, b int, index (b desc));
CREATE TABLE
Time: 4.081ms
[email protected]:63370/defaultdb> explain select * from a join c on a.a = c.b;
tree | field | description
+-----------+-------------------+-------------+
join | |
│ | type | inner
│ | equality | (a) = (b)
│ | left cols are key |
├── scan | |
│ | table | a@primary
│ | spans | ALL
└── scan | |
| table | c@primary
| spans | ALL
(10 rows)
Time: 7.246ms
[email protected]:63370/defaultdb> explain select * from a join b on a.a = b.b;
tree | field | description
+-----------+-------------------+-------------+
join | |
│ | type | inner
│ | equality | (a) = (b)
│ | left cols are key |
├── scan | |
│ | table | a@primary
│ | spans | ALL
└── scan | |
| table | b@primary
| spans | ALL
(10 rows)
Time: 715µs
[email protected]:63370/defaultdb> explain select * from a join b on a.a = b.a where a > 3 and a < 5;
pq: column reference "a" is ambiguous (candidates: a.a, b.a)
[email protected]:63370/defaultdb> explain select * from a join b on a.a = b.a where a.a > 3 and a.a < 5;
tree | field | description
+-----------+----------------+-------------+
join | |
│ | type | inner
│ | equality | (a) = (a)
│ | mergeJoinOrder | +"(a=a)"
├── scan | |
│ | table | a@primary
│ | spans | /4-/4/#
└── scan | |
| table | b@primary
| spans | /4-/4/#
(10 rows)
Time: 6.703ms
[email protected]:63370/defaultdb> select * from a order by c;
a | b | c
+---+---+---+
(0 rows)
Time: 1.226ms
[email protected]:63370/defaultdb> explain select * from a order by c;
tree | field | description
+-----------+-------+-------------+
sort | |
│ | order | +c
└── scan | |
| table | a@primary
| spans | ALL
(5 rows)
Time: 1.701ms
[email protected]:63370/defaultdb> explain select * from a order by c limit 5;
tree | field | description
+----------------+-------+-------------+
limit | |
│ | count | 5
└── sort | |
│ | order | +c
└── scan | |
| table | a@primary
| spans | ALL
(7 rows)
Time: 2.658ms
[email protected]:63370/defaultdb> explain(distsql) select * from a order by c limit 5;
automatic | url
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
true | https://cockroachdb.github.io/distsqlplan/decode.html#eJyUkEFLxDAQhe_-CnlXA9useMlprwuisnqTHGLzWAJtUyZTUJb-d2lz0BVW9Dhv8r0vzAlDjnwIPQvcKyy8wSi5ZSlZlqg-2Md3uMYgDeOkS-wN2iyEO0GTdoTDS3jreGCIlE0Dg0gNqVtrR0l9kI9dgJ8N8qRfLUXDkXB2Nn83PWdRysaeS3a3NzC4T33S67uLou1_RAeWMQ-FZ6JLzc3sDRiPrGcreZKWT5LbVVPHx5Vbg8iidWvrsB_qavngd9j-Cm9_wH6--gwAAP__4rKaag==
(1 row)
Time: 13.555ms
[email protected]:63370/defaultdb> select * from a where a = 3;
a | b | c
+---+---+---+
(0 rows)
Time: 670µs
[email protected]:63370/defaultdb> explain select * from a where a = 3;
tree | field | description
+------+-------+-------------+
scan | |
| table | a@primary
| spans | /3-/3/#
(3 rows)
+------+-------+-------------+
scan | |
| table | a@primary
| spans | /3-/3/#
(3 rows)
Time: 654µs
[email protected]:63370/defaultdb> explain select * from a where c = 3;
tree | field | description
+------+--------+-------------+
scan | |
| table | a@primary
| spans | ALL
| filter | c = 3
(4 rows)
Time: 519µs
[email protected]:63370/defaultdb> explain select sum(a) from a group by b where c = 3;
invalid syntax: statement ignored: syntax error at or near "where"
DETAIL: source SQL:
explain select sum(a) from a group by b where c = 3
^
[email protected]:63370/defaultdb> explain select sum(a) from a group by b having c = 3;
pq: column "c" must appear in the GROUP BY clause or be used in an aggregate function
[email protected]:63370/defaultdb> explain select sum(a) from a group by b,c having c = 3;
tree | field | description
+---------------------+-------------+-------------+
render | |
└── group | |
│ | aggregate 0 | b
│ | aggregate 1 | sum(a)
│ | group by | @2
└── render | |
└── scan | |
| table | a@primary
| spans | ALL
| filter | c = 3
(10 rows)
Time: 2.76ms
[email protected]:63370/defaultdb> ^D
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment