Skip to content

Instantly share code, notes, and snippets.

@rmloveland

rmloveland/top-25-employee-salaries-create-table-as

Created January 31, 2020 18:06
Show Gist options
  • Save rmloveland/6d4bc416be8e5b75a748cb5d97f16e5d to your computer and use it in GitHub Desktop.
Save rmloveland/6d4bc416be8e5b75a748cb5d97f16e5d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
top-25-employee-salaries-create-table-as
Version: CockroachDB CCL v19.2.2 (x86_64-apple-darwin14, built 2019/12/11 01:27:47, go1.12.12)
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
}
]';
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
}
]';
EXPLAIN (OPT, ENV, VERBOSE) SELECT e.last_name, s.salary FROM employees AS e JOIN current_salaries AS s ON e.emp_no = s.emp_no WHERE (s.to_date > now()) AND (s.salary > (SELECT max(salary) * 0.9 FROM current_salaries)) ORDER BY s.salary DESC LIMIT 25;
----
project
├── columns: last_name:4 salary:8
├── cardinality: [0 - 25]
├── side-effects
├── stats: [rows=25]
├── cost: 432333.457
├── ordering: -8
├── prune: (4,8)
├── interesting orderings: (-8)
└── limit
├── columns: e.emp_no:1 last_name:4 s.emp_no:7 s.salary:8 s.to_date:10
├── internal-ordering: -8
├── cardinality: [0 - 25]
├── side-effects
├── stats: [rows=25]
├── cost: 432333.197
├── fd: (1)-->(4), (1)==(7), (7)==(1)
├── ordering: -8
├── interesting orderings: (-8)
├── sort
│ ├── columns: e.emp_no:1 last_name:4 s.emp_no:7 s.salary:8 s.to_date:10
│ ├── side-effects
│ ├── stats: [rows=26680.4444, distinct(1)=26673.5378, null(1)=0, distinct(7)=26673.5378, null(7)=0]
│ ├── cost: 432332.937
│ ├── fd: (1)-->(4), (1)==(7), (7)==(1)
│ ├── ordering: -8
│ ├── prune: (4)
│ ├── interesting orderings: (+1) (+8) (+7) (+10)
│ └── inner-join (lookup employees)
│ ├── columns: e.emp_no:1 last_name:4 s.emp_no:7 s.salary:8 s.to_date:10
│ ├── key columns: [7] = [1]
│ ├── side-effects
│ ├── stats: [rows=26680.4444, distinct(1)=26673.5378, null(1)=0, distinct(7)=26673.5378, null(7)=0]
│ ├── cost: 423953.402
│ ├── fd: (1)-->(4), (1)==(7), (7)==(1)
│ ├── prune: (4)
│ ├── interesting orderings: (+1) (+8) (+7) (+10)
│ ├── select
│ │ ├── columns: s.emp_no:7 s.salary:8 s.to_date:10
│ │ ├── side-effects
│ │ ├── stats: [rows=26680.4444, distinct(7)=26673.5378, null(7)=0, distinct(8)=26680.4444, null(8)=0, distinct(10)=1, null(10)=0]
│ │ ├── cost: 261736.29
│ │ ├── prune: (7)
│ │ ├── interesting orderings: (+8) (+7) (+10)
│ │ ├── scan s
│ │ │ ├── columns: s.emp_no:7 s.salary:8 s.to_date:10
│ │ │ ├── stats: [rows=240124, distinct(7)=239052, null(7)=0, distinct(8)=67796, null(8)=0, distinct(10)=1, null(10)=0]
│ │ │ │ histogram(7)= 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(8)= 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(10)= 0 2.4012e+05
│ │ │ │ <--- '9999-01-01'
│ │ │ ├── cost: 259333.94
│ │ │ ├── prune: (7,8,10)
│ │ │ └── interesting orderings: (+8) (+7) (+10)
│ │ └── filters
│ │ ├── s.to_date > now() [outer=(10), side-effects, constraints=(/10: (/NULL - ])]
│ │ └── gt [outer=(8), subquery, constraints=(/8: (/NULL - ])]
│ │ ├── variable: s.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: current_salaries.salary:13
│ │ │ │ ├── constraint: /13/16: (/NULL - ]
│ │ │ │ ├── limit: 1(rev)
│ │ │ │ ├── stats: [rows=1]
│ │ │ │ ├── cost: 1.04
│ │ │ │ ├── key: ()
│ │ │ │ └── fd: ()-->(13)
│ │ │ └── aggregations
│ │ │ └── const-agg [outer=(13)]
│ │ │ └── variable: current_salaries.salary
│ │ └── projections
│ │ └── max * 0.9 [outer=(17)]
│ └── filters (true)
└── const: 25
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment