Skip to content

Instantly share code, notes, and snippets.

@rmloveland

rmloveland/top-25-employee-salaries-fast-hardcoded

Created January 30, 2020 22:30
Show Gist options
  • Save rmloveland/3742ac8bae6e7cfb911adba848fc5473 to your computer and use it in GitHub Desktop.
Save rmloveland/3742ac8bae6e7cfb911adba848fc5473 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
top-25-employee-salaries-fast-hardcoded
Version: CockroachDB CCL v19.2.2 (x86_64-apple-darwin14, built 2019/12/11 01:27:47, go1.12.12)
CREATE TABLE salaries (
emp_no INT8 NOT NULL,
salary INT8 NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
CONSTRAINT "primary" PRIMARY KEY (emp_no ASC, from_date ASC),
CONSTRAINT salaries_ibfk_1 FOREIGN KEY (emp_no) REFERENCES employees(emp_no) ON DELETE CASCADE,
INDEX salaries_salary_idx (salary ASC),
INDEX salaries_to_date_idx (to_date ASC),
FAMILY "primary" (emp_no, salary, from_date, to_date)
);
ALTER TABLE employees.public.salaries INJECT STATISTICS '[
{
"columns": [
"emp_no"
],
"created_at": "2020-01-30 22:05:52.818289+00:00",
"distinct_count": 301542,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 2844047
},
{
"columns": [
"salary"
],
"created_at": "2020-01-30 22:05:52.818289+00:00",
"distinct_count": 84756,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 2844047
},
{
"columns": [
"from_date"
],
"created_at": "2020-01-30 22:05:52.818289+00:00",
"distinct_count": 6382,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 2844047
},
{
"columns": [
"to_date"
],
"created_at": "2020-01-30 22:05:52.818289+00:00",
"distinct_count": 6118,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 2844047
},
{
"columns": [
"emp_no"
],
"created_at": "2020-01-30 22:23:57.063245+00:00",
"distinct_count": 301542,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 2844047
},
{
"columns": [
"salary"
],
"created_at": "2020-01-30 22:23:57.063245+00:00",
"distinct_count": 84756,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 2844047
},
{
"columns": [
"from_date"
],
"created_at": "2020-01-30 22:23:57.063245+00:00",
"distinct_count": 6382,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 2844047
},
{
"columns": [
"to_date"
],
"created_at": "2020-01-30 22:23:57.063245+00:00",
"distinct_count": 6118,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 2844047
},
{
"columns": [
"emp_no"
],
"created_at": "2020-01-30 22:25:00.910528+00:00",
"distinct_count": 301542,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 2844047
},
{
"columns": [
"salary"
],
"created_at": "2020-01-30 22:25:00.910528+00:00",
"distinct_count": 84756,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 2844047
},
{
"columns": [
"to_date"
],
"created_at": "2020-01-30 22:25:00.910528+00:00",
"distinct_count": 6118,
"histo_col_type": "DATE",
"name": "__auto__",
"null_count": 0,
"row_count": 2844047
},
{
"columns": [
"from_date"
],
"created_at": "2020-01-30 22:25:00.910528+00:00",
"distinct_count": 6382,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 2844047
}
]';
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-30 22:05:50.305791+00:00",
"distinct_count": 301542,
"histo_col_type": "INT8",
"name": "__auto__",
"null_count": 0,
"row_count": 300024
},
{
"columns": [
"birth_date"
],
"created_at": "2020-01-30 22:05:50.305791+00:00",
"distinct_count": 4743,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 300024
},
{
"columns": [
"first_name"
],
"created_at": "2020-01-30 22:05:50.305791+00:00",
"distinct_count": 1275,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 300024
},
{
"columns": [
"last_name"
],
"created_at": "2020-01-30 22:05:50.305791+00:00",
"distinct_count": 1637,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 300024
},
{
"columns": [
"gender"
],
"created_at": "2020-01-30 22:05:50.305791+00:00",
"distinct_count": 2,
"histo_col_type": "",
"name": "__auto__",
"null_count": 0,
"row_count": 300024
},
{
"columns": [
"hire_date"
],
"created_at": "2020-01-30 22:05:50.305791+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 salaries AS s JOIN employees AS e ON e.emp_no = s.emp_no WHERE (s.salary > 145000) AND (s.to_date > now()) ORDER BY s.salary DESC LIMIT 25;
----
project
├── columns: last_name:8 salary:2
├── cardinality: [0 - 25]
├── side-effects
├── stats: [rows=1]
├── cost: 2.16184389
├── ordering: -2
├── prune: (2,8)
├── interesting orderings: (-2)
└── limit
├── columns: s.emp_no:1 salary:2 to_date:4 e.emp_no:5 last_name:8
├── internal-ordering: -2
├── cardinality: [0 - 25]
├── side-effects
├── stats: [rows=1]
├── cost: 2.14184389
├── fd: (5)-->(8), (1)==(5), (5)==(1)
├── ordering: -2
├── interesting orderings: (-2)
├── inner-join (lookup employees)
│ ├── columns: s.emp_no:1 salary:2 to_date:4 e.emp_no:5 last_name:8
│ ├── key columns: [1] = [5]
│ ├── side-effects
│ ├── stats: [rows=1, distinct(1)=9.48015667e-05, null(1)=0, distinct(5)=9.48015667e-05, null(5)=0]
│ ├── cost: 2.12184389
│ ├── fd: (5)-->(8), (1)==(5), (5)==(1)
│ ├── ordering: -2
│ ├── prune: (8)
│ ├── interesting orderings: (+1) (+2,+1) (+4,+1) (+5)
│ ├── select
│ │ ├── columns: s.emp_no:1 salary:2 to_date:4
│ │ ├── side-effects
│ │ ├── stats: [rows=9.48015667e-05, distinct(1)=9.48015667e-05, null(1)=0, distinct(2)=9.48015667e-05, null(2)=0, distinct(4)=9.48015667e-05, null(4)=0]
│ │ │ histogram(2)=
│ │ ├── cost: 0.0314646842
│ │ ├── ordering: -2
│ │ ├── prune: (1)
│ │ ├── interesting orderings: (+1) (+2,+1) (+4,+1)
│ │ ├── index-join salaries
│ │ │ ├── columns: s.emp_no:1 salary:2 to_date:4
│ │ │ ├── stats: [rows=0.0002844047]
│ │ │ ├── cost: 0.0214618402
│ │ │ ├── ordering: -2
│ │ │ ├── interesting orderings: (+1,+3) (+2,+1,+3)
│ │ │ └── scan s@salaries_salary_idx,rev
│ │ │ ├── columns: s.emp_no:1 salary:2 from_date:3
│ │ │ ├── constraint: /2/1/3: [/145001 - ]
│ │ │ ├── stats: [rows=0.0002844047, distinct(2)=0.0002844047, null(2)=0]
│ │ │ │ histogram(2)=
│ │ │ ├── cost: 0.010301469
│ │ │ ├── key: (1,3)
│ │ │ ├── fd: (1,3)-->(2)
│ │ │ ├── ordering: -2
│ │ │ └── interesting orderings: (+1,+3) (+2,+1,+3)
│ │ └── filters
│ │ └── to_date > now() [outer=(4), side-effects, constraints=(/4: (/NULL - ])]
│ └── filters (true)
└── const: 25
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment