Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save rmloveland/8edad38dfb9e98f874ee7b347e3b2d59 to your computer and use it in GitHub Desktop.
Save rmloveland/8edad38dfb9e98f874ee7b347e3b2d59 to your computer and use it in GitHub Desktop.
Version: CockroachDB CCL v19.2.2 (x86_64-apple-darwin14, built 2019/12/11 01:27:47, go1.12.12)
CREATE TABLE current_salaries (
emp_no INT4 NULL,
salary INT4 NULL,
from_date DATE NULL,
to_date DATE NULL,
INDEX current_salaries_salary_idx (salary ASC),
INDEX current_salaries_emp_no_idx (emp_no ASC),
INDEX current_salaries_to_date_idx (to_date ASC),
INDEX current_salaries_salary_idx1 (salary ASC) STORING (to_date),
FAMILY "primary" (emp_no, salary, from_date, to_date, rowid)
);
ALTER TABLE employees.public.current_salaries INJECT STATISTICS '[
{
"columns": [
"emp_no"
],
"created_at": "2020-01-31 18:00:10.232564+00:00",
"distinct_count": 239052,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"salary"
],
"created_at": "2020-01-31 18:00:10.232564+00:00",
"distinct_count": 67796,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"from_date"
],
"created_at": "2020-01-31 18:00:10.232564+00:00",
"distinct_count": 364,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"to_date"
],
"created_at": "2020-01-31 18:00:10.232564+00:00",
"distinct_count": 1,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"rowid"
],
"created_at": "2020-01-31 18:00:10.232564+00:00",
"distinct_count": 239466,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"from_date"
],
"created_at": "2020-01-31 18:01:10.685925+00:00",
"distinct_count": 364,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"rowid"
],
"created_at": "2020-01-31 18:01:10.685925+00:00",
"distinct_count": 239466,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"salary"
],
"created_at": "2020-01-31 18:01:10.685925+00:00",
"distinct_count": 67796,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"to_date"
],
"created_at": "2020-01-31 18:01:10.685925+00:00",
"distinct_count": 1,
"histo_col_type": "DATE",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"emp_no"
],
"created_at": "2020-01-31 18:01:10.685925+00:00",
"distinct_count": 239052,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"rowid"
],
"created_at": "2020-01-31 18:02:11.142258+00:00",
"distinct_count": 239466,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"salary"
],
"created_at": "2020-01-31 18:02:11.142258+00:00",
"distinct_count": 67796,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"emp_no"
],
"created_at": "2020-01-31 18:02:11.142258+00:00",
"distinct_count": 239052,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"to_date"
],
"created_at": "2020-01-31 18:02:11.142258+00:00",
"distinct_count": 1,
"histo_col_type": "DATE",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
},
{
"columns": [
"from_date"
],
"created_at": "2020-01-31 18:02:11.142258+00:00",
"distinct_count": 364,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 240124
}
]';
CREATE TABLE employees (
emp_no INT4 NOT NULL,
birth_date DATE NOT NULL,
first_name VARCHAR(14) NOT NULL,
last_name VARCHAR(16) NOT NULL,
gender STRING NOT NULL,
hire_date DATE NOT NULL,
CONSTRAINT "primary" PRIMARY KEY (emp_no ASC),
FAMILY "primary" (emp_no, birth_date, first_name, last_name, gender, hire_date),
CONSTRAINT imported_from_enum_gender CHECK (gender IN ('M':::STRING, 'F':::STRING))
);
ALTER TABLE employees.public.employees INJECT STATISTICS '[
{
"columns": [
"emp_no"
],
"created_at": "2020-01-31 17:43:59.797731+00:00",
"distinct_count": 301542,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 300024
},
{
"columns": [
"birth_date"
],
"created_at": "2020-01-31 17:43:59.797731+00:00",
"distinct_count": 4743,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 300024
},
{
"columns": [
"first_name"
],
"created_at": "2020-01-31 17:43:59.797731+00:00",
"distinct_count": 1275,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 300024
},
{
"columns": [
"last_name"
],
"created_at": "2020-01-31 17:43:59.797731+00:00",
"distinct_count": 1637,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 300024
},
{
"columns": [
"gender"
],
"created_at": "2020-01-31 17:43:59.797731+00:00",
"distinct_count": 2,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 300024
},
{
"columns": [
"hire_date"
],
"created_at": "2020-01-31 17:43:59.797731+00:00",
"distinct_count": 5389,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 300024
}
]';
EXPLAIN (OPT, ENV, VERBOSE) SELECT e.last_name, s.salary FROM (SELECT emp_no, salary FROM current_salaries WHERE to_date > now() ORDER BY salary DESC LIMIT 25) AS s JOIN employees AS e ON e.emp_no = s.emp_no WHERE s.salary > (SELECT max(current_salaries.salary) * 0.9 FROM current_salaries);
----
project
├── columns: last_name:9 salary:2
├── side-effects
├── stats: [rows=8.33333333]
├── cost: 289463.414
├── prune: (2,9)
└── inner-join (lookup employees)
├── columns: current_salaries.emp_no:1 salary:2 to_date:4 e.emp_no:6 last_name:9
├── key columns: [1] = [6]
├── side-effects
├── stats: [rows=8.33333333, distinct(1)=8.33333312, null(1)=0, distinct(6)=8.33333312, null(6)=0]
├── cost: 289463.321
├── fd: (6)-->(9), (1)==(6), (6)==(1)
├── select
│ ├── columns: current_salaries.emp_no:1 salary:2 to_date:4
│ ├── cardinality: [0 - 25]
│ ├── side-effects
│ ├── stats: [rows=8.33333333, distinct(1)=8.33333312, null(1)=0, distinct(2)=8.33333333, null(2)=0, distinct(4)=0.999960398, null(4)=0]
│ ├── cost: 289412.644
│ ├── prune: (1)
│ ├── interesting orderings: (-2)
│ ├── limit
│ │ ├── columns: current_salaries.emp_no:1 salary:2 to_date:4
│ │ ├── internal-ordering: -2
│ │ ├── cardinality: [0 - 25]
│ │ ├── side-effects
│ │ ├── stats: [rows=25, distinct(1)=24.9999967, null(1)=0, distinct(2)=24.9978562, null(2)=0, distinct(4)=1, null(4)=0]
│ │ ├── cost: 289411.284
│ │ ├── prune: (1)
│ │ ├── interesting orderings: (-2)
│ │ ├── sort
│ │ │ ├── columns: current_salaries.emp_no:1 salary:2 to_date:4
│ │ │ ├── side-effects
│ │ │ ├── stats: [rows=80041.3333, distinct(1)=79973.5091, null(1)=0, distinct(2)=51670.4948, null(2)=0, distinct(4)=1, null(4)=0]
│ │ │ ├── cost: 289411.024
│ │ │ ├── ordering: -2
│ │ │ ├── prune: (1,2)
│ │ │ ├── interesting orderings: (+2) (+1) (+4)
│ │ │ └── select
│ │ │ ├── columns: current_salaries.emp_no:1 salary:2 to_date:4
│ │ │ ├── side-effects
│ │ │ ├── stats: [rows=80041.3333, distinct(1)=79973.5091, null(1)=0, distinct(2)=51670.4948, null(2)=0, distinct(4)=1, null(4)=0]
│ │ │ ├── cost: 261735.19
│ │ │ ├── prune: (1,2)
│ │ │ ├── interesting orderings: (+2) (+1) (+4)
│ │ │ ├── scan current_salaries
│ │ │ │ ├── columns: current_salaries.emp_no:1 salary:2 to_date:4
│ │ │ │ ├── stats: [rows=240124, distinct(1)=239052, null(1)=0, distinct(2)=67796, null(2)=0, distinct(4)=1, null(4)=0]
│ │ │ │ │ histogram(1)= 0 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24
│ │ │ │ │ <--- 10044 ------ 11466 ------ 12941 ------ 14257 ------ 15602 ------ 16765 ------ 18398 ------ 20215 ------ 21720 ------ 23436 ------ 24884 ------ 26341 ------ 27850 ------ 29370 ------ 31060 ------ 32732 ------ 34066 ------ 35455 ------ 37215 ------ 38842 ------ 40263 ------ 41521 ------ 42924 ------ 44777 ------ 46421 ------ 47838 ------ 49612 ------ 50871 ------ 52381 ------ 53723 ------ 55151 ------ 56713 ------ 58701 ------ 60177 ------ 61644 ------ 63137 ------ 64983 ------ 66304 ------ 67593 ------ 69373 ------ 70844 ------ 72407 ------ 73669 ------ 75279 ------ 76765 ------ 78126 ------ 79725 ------ 81409 ------ 82620 ------ 83994 ------ 85501 ------ 86815 ------ 88102 ------ 89691 ------ 91090 ------ 92997 ------ 94362 ------ 95908 ------ 97407 ------ 98810 ------ 100184 ------ 101406 ------ 102979 ------ 104628 ------ 106066 ------ 107616 ------ 108922 ------ 200336 ------ 201799 ------ 203007 ------ 204413 ------ 205809 ------ 207105 ------ 208544 ------ 209972 ------ 211229 ------ 212494 ------ 214166 ------ 216022 ------ 217449 ------ 218897 ------ 220084 ------ 221808 ------ 223175 ------ 224806 ------ 225987 ------ 227758 ------ 229487 ------ 231104 ------ 232510 ------ 233943 ------ 235297 ------ 236808 ------ 238185 ------ 239873 ------ 241583 ------ 242953 ------ 244474 ------ 246292 ------ 247888 ------ 249358 ------ 251245 ------ 252495 ------ 253971 ------ 255413 ------ 256956 ------ 258466 ------ 259782 ------ 261207 ------ 262470 ------ 263770 ------ 265815 ------ 266942 ------ 268537 ------ 270168 ------ 272139 ------ 273427 ------ 275100 ------ 276748 ------ 278172 ------ 279797 ------ 281359 ------ 282506 ------ 284126 ------ 285624 ------ 287226 ------ 288491 ------ 289917 ------ 291403 ------ 292879 ------ 294285 ------ 295899 ------ 297259 ------ 299099 ------ 401085 ------ 402526 ------ 404260 ------ 405616 ------ 407103 ------ 408501 ------ 409794 ------ 411505 ------ 412590 ------ 414315 ------ 415628 ------ 417309 ------ 418384 ------ 419703 ------ 421054 ------ 422715 ------ 424037 ------ 425649 ------ 427016 ------ 428506 ------ 430679 ------ 432375 ------ 433937 ------ 435408 ------ 436912 ------ 438734 ------ 440656 ------ 442170 ------ 444065 ------ 445469 ------ 447128 ------ 448775 ------ 450424 ------ 451633 ------ 452859 ------ 454441 ------ 455682 ------ 457058 ------ 458392 ------ 459955 ------ 461601 ------ 463170 ------ 464446 ------ 466275 ------ 467817 ------ 469255 ------ 470450 ------ 471799 ------ 473198 ------ 474821 ------ 476410 ------ 477776 ------ 479076 ------ 481113 ------ 482656 ------ 484254 ------ 486138 ------ 487677 ------ 489129 ------ 490792 ------ 492064 ------ 493461 ------ 495156 ------ 496767 ------ 498326 ------ 499977
│ │ │ │ │ histogram(2)= 0 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1152 48 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1152 48 1176 48 1176 24 1176 48 1176 24 1176 24 1176 24 1176 24 1176 24 1152 48 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 48 1152 48 1176 24 1176 48 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 48 1152 48 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 48 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1152 48 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 48 1152 48 1176 24 1176 48 1152 48 1152 48 1176 24 1176 24 1176 48 1176 24 1176 24 1152 48 1176 24 1176 24 1176 24 1176 48 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 48 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 48 1176 24 1176 24 1176 24 1176 48 1176 48 1152 48 1176 24 1176 48 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1128 96 1176 48 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1152 48 1176 24 1176 24 1176 24 1176 24 1176 24 1152 48 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1152 48 1152 48 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1176 24 1200 24 1200 24 1176 48 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 48 1176 48 1176 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24 1200 24
│ │ │ │ │ <--- 39704 ------ 42295 ------ 43366 ------ 44168 ------ 44638 ------ 45124 ------ 45542 ------ 45951 ------ 46376 ------ 46768 ------ 47153 ------ 47598 ------ 48103 ------ 48448 ------ 48751 ------ 49131 ------ 49441 ------ 49829 ------ 50269 ------ 50595 ------ 50896 ------ 51220 ------ 51583 ------ 51909 ------ 52177 ------ 52427 ------ 52732 ------ 53069 ------ 53287 ------ 53539 ------ 53829 ------ 54119 ------ 54335 ------ 54654 ------ 54892 ------ 55211 ------ 55485 ------ 55689 ------ 55927 ------ 56181 ------ 56410 ------ 56691 ------ 56924 ------ 57193 ------ 57468 ------ 57744 ------ 57988 ------ 58243 ------ 58485 ------ 58675 ------ 58957 ------ 59155 ------ 59340 ------ 59549 ------ 59807 ------ 60024 ------ 60272 ------ 60542 ------ 60775 ------ 60985 ------ 61244 ------ 61476 ------ 61699 ------ 61965 ------ 62206 ------ 62426 ------ 62599 ------ 62801 ------ 63035 ------ 63232 ------ 63474 ------ 63692 ------ 63899 ------ 64155 ------ 64414 ------ 64679 ------ 64910 ------ 65160 ------ 65362 ------ 65640 ------ 65857 ------ 66078 ------ 66290 ------ 66523 ------ 66731 ------ 66915 ------ 67082 ------ 67309 ------ 67517 ------ 67783 ------ 67979 ------ 68137 ------ 68336 ------ 68527 ------ 68752 ------ 68949 ------ 69115 ------ 69287 ------ 69438 ------ 69657 ------ 69872 ------ 70051 ------ 70247 ------ 70452 ------ 70648 ------ 70779 ------ 70995 ------ 71242 ------ 71463 ------ 71648 ------ 71833 ------ 72034 ------ 72272 ------ 72464 ------ 72712 ------ 72930 ------ 73192 ------ 73428 ------ 73657 ------ 73894 ------ 74080 ------ 74295 ------ 74527 ------ 74786 ------ 74996 ------ 75219 ------ 75449 ------ 75680 ------ 75887 ------ 76273 ------ 76472 ------ 76735 ------ 76989 ------ 77223 ------ 77448 ------ 77793 ------ 78041 ------ 78273 ------ 78523 ------ 78794 ------ 79084 ------ 79430 ------ 79711 ------ 79952 ------ 80219 ------ 80577 ------ 80938 ------ 81226 ------ 81606 ------ 81901 ------ 82318 ------ 82679 ------ 83107 ------ 83485 ------ 83850 ------ 84171 ------ 84520 ------ 84830 ------ 85252 ------ 85632 ------ 85959 ------ 86378 ------ 86712 ------ 87121 ------ 87507 ------ 87869 ------ 88381 ------ 88866 ------ 89355 ------ 89879 ------ 90270 ------ 90763 ------ 91347 ------ 91801 ------ 92303 ------ 92754 ------ 93430 ------ 94128 ------ 94690 ------ 95276 ------ 96070 ------ 96869 ------ 97520 ------ 98452 ------ 99341 ------ 100192 ------ 101130 ------ 102110 ------ 102930 ------ 103809 ------ 104977 ------ 105888 ------ 107551 ------ 109212 ------ 111303 ------ 113318 ------ 115700 ------ 119269 ------ 123494 ------ 145902
│ │ │ │ │ histogram(4)= 0 2.4012e+05
│ │ │ │ │ <--- '9999-01-01'
│ │ │ │ ├── cost: 259333.94
│ │ │ │ ├── prune: (1,2,4)
│ │ │ │ └── interesting orderings: (+2) (+1) (+4)
│ │ │ └── filters
│ │ │ └── to_date > now() [outer=(4), side-effects, constraints=(/4: (/NULL - ])]
│ │ └── const: 25
│ └── filters
│ └── gt [outer=(2), subquery, constraints=(/2: (/NULL - ])]
│ ├── variable: salary
│ └── subquery
│ └── project
│ ├── columns: "?column?":18
│ ├── cardinality: [1 - 1]
│ ├── stats: [rows=1]
│ ├── cost: 1.1
│ ├── key: ()
│ ├── fd: ()-->(18)
│ ├── scalar-group-by
│ │ ├── columns: max:17
│ │ ├── cardinality: [1 - 1]
│ │ ├── stats: [rows=1]
│ │ ├── cost: 1.07
│ │ ├── key: ()
│ │ ├── fd: ()-->(17)
│ │ ├── scan current_salaries@current_salaries_salary_idx,rev
│ │ │ ├── columns: salary:13
│ │ │ ├── constraint: /13/16: (/NULL - ]
│ │ │ ├── limit: 1(rev)
│ │ │ ├── stats: [rows=1]
│ │ │ ├── cost: 1.04
│ │ │ ├── key: ()
│ │ │ └── fd: ()-->(13)
│ │ └── aggregations
│ │ └── const-agg [outer=(13)]
│ │ └── variable: salary
│ └── projections
│ └── max * 0.9 [outer=(17)]
└── filters (true)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment