Created
May 2, 2019 20:18
-
-
Save zombor/03713f73f0a69d34ce1a8218f6ef62b2 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
text | |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | |
Version: CockroachDB CCL v19.1.0 (x86_64-unknown-linux-gnu, built 2019/04/29 18:36:40, go1.11.6) | |
CREATE TABLE user_card_conditions ( | |
id UUID NOT NULL DEFAULT gen_random_uuid(), | |
user_id UUID NOT NULL, | |
card_condition_id UUID NOT NULL, | |
quantity INT8 NOT NULL DEFAULT 0:::INT8, | |
price INT8 NOT NULL DEFAULT 0:::INT8, | |
created_at TIMESTAMP NOT NULL, | |
updated_at TIMESTAMP NOT NULL, | |
CONSTRAINT user_card_conditions_pkey PRIMARY KEY (id ASC), | |
CONSTRAINT fk_rails_814558ae8b FOREIGN KEY (card_condition_id) REFERENCES card_conditions (id) ON DELETE CASCADE, | |
INDEX index_user_card_conditions_on_card_condition_id (card_condition_id ASC), | |
CONSTRAINT fk_rails_1c477dc301 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, | |
INDEX index_user_card_conditions_on_user_id (user_id ASC), | |
INDEX user_card_conditions_user_id_quantity_idx (user_id ASC, quantity ASC), | |
FAMILY "primary" (id, user_id, card_condition_id, quantity, price, created_at, updated_at) | |
); | |
ALTER TABLE synkt.public.user_card_conditions INJECT STATISTICS '[ | |
{ | |
"columns": [ | |
"id" | |
], | |
"created_at": "2019-05-01 02:35:37.513567+00:00", | |
"distinct_count": 191534, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"card_condition_id" | |
], | |
"created_at": "2019-05-01 02:35:37.513567+00:00", | |
"distinct_count": 189938, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"user_id" | |
], | |
"created_at": "2019-05-01 02:35:37.513567+00:00", | |
"distinct_count": 1, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"quantity" | |
], | |
"created_at": "2019-05-01 02:35:37.513567+00:00", | |
"distinct_count": 3, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"price" | |
], | |
"created_at": "2019-05-01 02:35:37.513567+00:00", | |
"distinct_count": 4, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"created_at" | |
], | |
"created_at": "2019-05-01 02:35:37.513567+00:00", | |
"distinct_count": 1, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"updated_at" | |
], | |
"created_at": "2019-05-01 02:35:37.513567+00:00", | |
"distinct_count": 4, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"id" | |
], | |
"created_at": "2019-05-01 02:52:52.82342+00:00", | |
"distinct_count": 191534, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"card_condition_id" | |
], | |
"created_at": "2019-05-01 02:52:52.82342+00:00", | |
"distinct_count": 189938, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"user_id" | |
], | |
"created_at": "2019-05-01 02:52:52.82342+00:00", | |
"distinct_count": 1, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"quantity" | |
], | |
"created_at": "2019-05-01 02:52:52.82342+00:00", | |
"distinct_count": 3, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"price" | |
], | |
"created_at": "2019-05-01 02:52:52.82342+00:00", | |
"distinct_count": 4, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"created_at" | |
], | |
"created_at": "2019-05-01 02:52:52.82342+00:00", | |
"distinct_count": 1, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"updated_at" | |
], | |
"created_at": "2019-05-01 02:52:52.82342+00:00", | |
"distinct_count": 4, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
} | |
]'; | |
CREATE TABLE card_conditions ( | |
id UUID NOT NULL DEFAULT gen_random_uuid(), | |
card_id UUID NOT NULL, | |
condition STRING NOT NULL, | |
created_at TIMESTAMP NOT NULL, | |
updated_at TIMESTAMP NOT NULL, | |
CONSTRAINT card_conditions_pkey PRIMARY KEY (id ASC), | |
CONSTRAINT fk_rails_5fee70a3a8 FOREIGN KEY (card_id) REFERENCES cards (id) ON DELETE CASCADE, | |
INDEX index_card_conditions_on_card_id (card_id ASC), | |
FAMILY "primary" (id, card_id, condition, created_at, updated_at) | |
); | |
ALTER TABLE synkt.public.card_conditions INJECT STATISTICS '[ | |
{ | |
"columns": [ | |
"id" | |
], | |
"created_at": "2019-05-01 02:35:44.848671+00:00", | |
"distinct_count": 189938, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"card_id" | |
], | |
"created_at": "2019-05-01 02:35:44.848671+00:00", | |
"distinct_count": 42466, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"condition" | |
], | |
"created_at": "2019-05-01 02:35:44.848671+00:00", | |
"distinct_count": 6, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"created_at" | |
], | |
"created_at": "2019-05-01 02:35:44.848671+00:00", | |
"distinct_count": 191393, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
}, | |
{ | |
"columns": [ | |
"updated_at" | |
], | |
"created_at": "2019-05-01 02:35:44.848671+00:00", | |
"distinct_count": 191393, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 191858 | |
} | |
]'; | |
CREATE TABLE cards ( | |
id UUID NOT NULL DEFAULT gen_random_uuid(), | |
card_set_id UUID NOT NULL, | |
scryfall_id STRING NOT NULL, | |
name STRING NOT NULL, | |
type_line STRING NOT NULL, | |
rarity STRING NOT NULL, | |
multiverseid INT8 NULL, | |
number STRING NULL, | |
names STRING[] NULL, | |
mana_cost STRING NULL, | |
cmc FLOAT8 NULL, | |
colors STRING[] NULL, | |
color_identity STRING[] NULL, | |
supertypes STRING[] NULL, | |
types STRING[] NULL, | |
subtypes STRING[] NULL, | |
text STRING NULL, | |
flavor STRING NULL, | |
artist STRING NULL, | |
power STRING NULL, | |
toughness STRING NULL, | |
loyalty INT8 NULL, | |
variations STRING[] NULL, | |
watermark STRING NULL, | |
border STRING NULL, | |
reserved BOOL NULL, | |
layout STRING NULL, | |
created_at TIMESTAMP NOT NULL, | |
updated_at TIMESTAMP NOT NULL, | |
photo STRING NULL, | |
collector_number STRING NULL, | |
image STRING NULL, | |
CONSTRAINT cards_pkey PRIMARY KEY (id ASC), | |
CONSTRAINT fk_rails_2b6dc2726c FOREIGN KEY (card_set_id) REFERENCES card_sets (id) ON DELETE CASCADE, | |
INDEX index_cards_on_card_set_id (card_set_id ASC), | |
FAMILY "primary" (id, card_set_id, scryfall_id, name, type_line, rarity, multiverseid, number, names, mana_cost, cmc, colors, color_identity, supertypes, types, subtypes, text, flavor, artist, power, toughness, loyalty, variations, watermark, border, reserved, layout, created_at, updated_at, photo, collector_number, image) | |
); | |
ALTER TABLE synkt.public.cards INJECT STATISTICS '[ | |
{ | |
"columns": [ | |
"id" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 42466, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"card_set_id" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 540, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"scryfall_id" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 41999, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"name" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 19548, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"type_line" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 2000, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"rarity" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 4, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"multiverseid" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 34219, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 7712, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"number" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 2980, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"names" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 0, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 42021, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"mana_cost" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 740, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 206, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"cmc" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 19, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"colors" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 32, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 206, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"color_identity" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 32, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"supertypes" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 0, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 42021, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"types" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 0, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 42021, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"subtypes" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 0, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 42021, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"text" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 18139, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 366, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"flavor" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 15266, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 17904, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"artist" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 749, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"power" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 34, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 21729, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"toughness" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 36, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 21729, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"loyalty" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 9, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 41668, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"variations" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 0, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 42021, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"watermark" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 0, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 42021, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"border" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 5, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"reserved" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 2, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"layout" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 15, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"created_at" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 42274, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"updated_at" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 42234, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"photo" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 41978, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 206, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"collector_number" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 0, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 42021, | |
"row_count": 42021 | |
}, | |
{ | |
"columns": [ | |
"image" | |
], | |
"created_at": "2019-05-01 02:35:40.01445+00:00", | |
"distinct_count": 41669, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 42021 | |
} | |
]'; | |
CREATE TABLE card_sets ( | |
id UUID NOT NULL DEFAULT gen_random_uuid(), | |
name VARCHAR NOT NULL, | |
code VARCHAR NOT NULL, | |
set_type VARCHAR NOT NULL, | |
released_at DATE NOT NULL, | |
created_at TIMESTAMP NOT NULL, | |
updated_at TIMESTAMP NOT NULL, | |
CONSTRAINT card_sets_pkey PRIMARY KEY (id ASC), | |
FAMILY "primary" (id, name, code, set_type, released_at, created_at, updated_at) | |
); | |
ALTER TABLE synkt.public.card_sets INJECT STATISTICS '[ | |
{ | |
"columns": [ | |
"id" | |
], | |
"created_at": "2019-05-01 02:35:43.264911+00:00", | |
"distinct_count": 542, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 542 | |
}, | |
{ | |
"columns": [ | |
"name" | |
], | |
"created_at": "2019-05-01 02:35:43.264911+00:00", | |
"distinct_count": 542, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 542 | |
}, | |
{ | |
"columns": [ | |
"code" | |
], | |
"created_at": "2019-05-01 02:35:43.264911+00:00", | |
"distinct_count": 542, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 542 | |
}, | |
{ | |
"columns": [ | |
"set_type" | |
], | |
"created_at": "2019-05-01 02:35:43.264911+00:00", | |
"distinct_count": 20, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 542 | |
}, | |
{ | |
"columns": [ | |
"released_at" | |
], | |
"created_at": "2019-05-01 02:35:43.264911+00:00", | |
"distinct_count": 303, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 542 | |
}, | |
{ | |
"columns": [ | |
"created_at" | |
], | |
"created_at": "2019-05-01 02:35:43.264911+00:00", | |
"distinct_count": 542, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 542 | |
}, | |
{ | |
"columns": [ | |
"updated_at" | |
], | |
"created_at": "2019-05-01 02:35:43.264911+00:00", | |
"distinct_count": 542, | |
"histo_col_type": "", | |
"name": "__auto__", | |
"null_count": 0, | |
"row_count": 542 | |
} | |
]'; | |
EXPLAIN (OPT, ENV) SELECT count(*) FROM user_card_conditions INNER JOIN card_conditions ON card_conditions.id = user_card_conditions.card_condition_id INNER JOIN cards ON cards.id = card_conditions.card_id INNER JOIN card_sets ON card_sets.id = cards.card_set_id WHERE ((user_card_conditions.user_id = '10cd581e-18bc-4cc3-896c-e1552b8e8420') AND (cards.name ~* 'duress')) AND (card_conditions.condition = 'near-mint'); | |
---- | |
scalar-group-by | |
├── inner-join | |
│ ├── select | |
│ │ ├── scan user_card_conditions | |
│ │ └── filters | |
│ │ └── user_id = '10cd581e-18bc-4cc3-896c-e1552b8e8420' | |
│ ├── inner-join | |
│ │ ├── select | |
│ │ │ ├── scan card_conditions | |
│ │ │ └── filters | |
│ │ │ └── condition = 'near-mint' | |
│ │ ├── inner-join | |
│ │ │ ├── select | |
│ │ │ │ ├── scan cards | |
│ │ │ │ └── filters | |
│ │ │ │ └── cards.name ~* 'duress' | |
│ │ │ ├── scan card_sets | |
│ │ │ └── filters | |
│ │ │ └── card_sets.id = card_set_id | |
│ │ └── filters | |
│ │ └── cards.id = card_id | |
│ └── filters | |
│ └── card_conditions.id = card_condition_id | |
└── aggregations | |
└── count-rows | |
(759 rows) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment