Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.