Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: support interleaved tables #2972

Closed
petermattis opened this issue Oct 30, 2015 · 0 comments
Closed

sql: support interleaved tables #2972

petermattis opened this issue Oct 30, 2015 · 0 comments
Assignees
@danhhz
Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Milestone
Q3

Comments

@petermattis
Copy link
Collaborator

Support F1/Spanner-style interleaved tables where the descendent table's data is interleaved in the parent table allowing for much faster joins when joining on the interleaving key.
@jess-edwards jess-edwards mentioned this issue Oct 30, 2015
Closed
78 tasks
@petermattis petermattis added this to the 1.0 milestone Oct 30, 2015
@petermattis petermattis added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) and removed SQL labels Feb 13, 2016
@petermattis petermattis mentioned this issue Mar 5, 2016
Closed
@ddorian ddorian mentioned this issue Jun 17, 2016
Closed
danhhz added a commit to danhhz/cockroach that referenced this issue Jun 28, 2016
@danhhz
rfc: sql interleaved tables
d8d3ad0 
Support interleaving the data for sql tables such that the rows alternate in the
kv map. This allows a user to optimize reads on tables that are frequently
joined.

For cockroachdb#2972.
danhhz added a commit to danhhz/cockroach that referenced this issue Jun 28, 2016
@danhhz
rfc: sql interleaved tables
375b2d4 
Support interleaving the data for sql tables such that the rows alternate in the
kv map. This allows a user to optimize reads on tables that are frequently
joined.

For cockroachdb#2972.
danhhz added a commit to danhhz/cockroach that referenced this issue Jul 1, 2016
@danhhz
rfc: sql interleaved tables
65f27ea 
Support interleaving the data for sql tables such that the rows alternate in the
kv map. This allows a user to optimize reads on tables that are frequently
joined.

For cockroachdb#2972.
danhhz added a commit to danhhz/cockroach that referenced this issue Jul 5, 2016
@danhhz
rfc: sql interleaved tables
4a6eb31 
Support interleaving the data for sql tables such that the rows alternate in the
kv map. This allows a user to optimize reads on tables that are frequently
joined.

For cockroachdb#2972.
danhhz added a commit to danhhz/cockroach that referenced this issue Jul 5, 2016
@danhhz
rfc: sql interleaved tables
f8172b3 
Support interleaving the data for sql tables such that the rows alternate in the
kv map. This allows a user to optimize reads on tables that are frequently
joined.

For cockroachdb#2972.
@danhhz danhhz mentioned this issue Jul 6, 2016
Merged
danhhz added a commit to danhhz/cockroach that referenced this issue Jul 6, 2016
@danhhz
sql/parser: parsing for interleaved tables/indexes
834d786 
I ran doc generation locally and the SKIP DOC seems to work and keep this off
the diagrams.

For cockroachdb#2972.
danhhz added a commit to danhhz/cockroach that referenced this issue Jul 8, 2016
@danhhz
sql/parser: parsing for interleaved tables/indexes
5bfef97 
I ran doc generation locally and the SKIP DOC seems to work and keep this off
the diagrams.

For cockroachdb#2972.
@petermattis petermattis modified the milestones: Q3, 1.0 Jul 11, 2016
danhhz added a commit to danhhz/cockroach that referenced this issue Jul 11, 2016
@danhhz
sqlbase: interleave descriptors and key coding
376daf8 
Implements the encoding described in
https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/sql_interleaved_tables.md

No change in benchmarks for non-interleaved tables.

name                       old time/op    new time/op    delta
Insert1000_Cockroach-8       9.73ms ± 4%    9.71ms ± 3%    ~             (p=0.690 n=5+5)
Update1000_Cockroach-8       28.6ms ± 4%    29.0ms ± 2%    ~             (p=0.310 n=5+5)
Delete1000_Cockroach-8       49.8ms ± 4%    48.2ms ± 5%    ~             (p=0.222 n=5+5)
Scan1000_Cockroach-8         2.28ms ± 2%    2.24ms ± 2%    ~             (p=0.222 n=5+5)
WideTable1000_Cockroach-8     109ms ± 4%     112ms ± 2%    ~             (p=0.151 n=5+5)

name                       old alloc/op   new alloc/op   delta
Insert1000_Cockroach-8       4.18MB ± 0%    4.18MB ± 0%    ~             (p=0.413 n=4+5)
Update1000_Cockroach-8       6.55MB ± 0%    6.54MB ± 0%    ~             (p=0.690 n=5+5)
Delete1000_Cockroach-8       3.19MB ± 0%    3.19MB ± 0%    ~             (p=0.690 n=5+5)
Scan1000_Cockroach-8          317kB ± 0%     317kB ± 0%  +0.04%          (p=0.008 n=5+5)
WideTable1000_Cockroach-8    21.2MB ± 0%    21.2MB ± 0%    ~             (p=0.690 n=5+5)

name                       old allocs/op  new allocs/op  delta
Insert1000_Cockroach-8        20.4k ± 0%     20.4k ± 0%    ~             (p=0.540 n=4+5)
Update1000_Cockroach-8        29.5k ± 0%     29.5k ± 0%    ~             (p=0.056 n=5+5)
Delete1000_Cockroach-8        21.6k ± 0%     21.6k ± 0%    ~             (p=0.810 n=5+5)
Scan1000_Cockroach-8          2.30k ± 0%     2.30k ± 0%    ~     (all samples are equal)
WideTable1000_Cockroach-8      223k ± 0%      223k ± 0%    ~             (p=0.690 n=5+5)

For cockroachdb#2972.
@danhhz danhhz mentioned this issue Jul 11, 2016
Merged
danhhz added a commit to danhhz/cockroach that referenced this issue Jul 15, 2016
@danhhz
sql: allow interleaving table and index data
15f7725 
Also changes the interleaving sentinel to encoding.descNotNull (0xfe) because
the other one could be used in keys. We want to be able to pull table and index
ids out of a key without the descriptor and it was possible for there to be
ambiguity with the previously used encoding.descNull (0xff).

Followup work:
- Allow interleaving indexes declared in a CREATE TABLE
- Support the CASCADE/RESTRICT foreign key shorthands
- Clean up the InterleavedBy back references when dropping a table/index
- Write some interleaving benchmarks
- Optimize the case where lots of interleaved data has to be scanned over (it
  currently always looks at every key.)
- Remove the SKIP DOC comments on opt_interleave in sql.y

For cockroachdb#2972.

Benchmarks are unchanged for non-interleaved data.

name                       old time/op    new time/op    delta
Insert1000_Cockroach-8       9.75ms ± 4%    9.71ms ± 5%   ~             (p=0.690 n=5+5)
Update1000_Cockroach-8       26.6ms ± 3%    26.3ms ± 5%   ~             (p=0.841 n=5+5)
Delete1000_Cockroach-8       13.1ms ± 3%    13.4ms ± 3%   ~             (p=0.421 n=5+5)
Scan1000_Cockroach-8         2.20ms ± 1%    2.20ms ± 1%   ~             (p=0.841 n=5+5)
WideTable1000_Cockroach-8     105ms ± 4%     107ms ± 6%   ~             (p=0.690 n=5+5)

name                       old alloc/op   new alloc/op   delta
Insert1000_Cockroach-8       4.25MB ± 0%    4.25MB ± 0%   ~             (p=0.111 n=4+5)
Update1000_Cockroach-8       6.42MB ± 0%    6.42MB ± 0%   ~             (p=0.841 n=5+5)
Delete1000_Cockroach-8       1.07MB ± 0%    1.07MB ± 0%   ~             (p=1.000 n=5+5)
Scan1000_Cockroach-8          317kB ± 0%     317kB ± 0%   ~             (p=0.444 n=5+5)
WideTable1000_Cockroach-8    21.3MB ± 0%    21.3MB ± 0%   ~             (p=0.222 n=5+5)

name                       old allocs/op  new allocs/op  delta
Insert1000_Cockroach-8        20.4k ± 0%     20.4k ± 0%   ~             (p=0.056 n=5+5)
Update1000_Cockroach-8        28.7k ± 0%     28.7k ± 0%   ~             (p=0.643 n=5+5)
Delete1000_Cockroach-8        12.6k ± 0%     12.6k ± 0%   ~             (p=1.000 n=5+5)
Scan1000_Cockroach-8          2.30k ± 0%     2.30k ± 0%   ~     (all samples are equal)
WideTable1000_Cockroach-8      224k ± 0%      224k ± 0%   ~             (p=0.841 n=5+5)
@danhhz danhhz mentioned this issue Jul 15, 2016
Merged
danhhz added a commit to danhhz/cockroach that referenced this issue Jul 18, 2016
@danhhz
sql: allow interleaving table and index data
d38067e 
Also changes the interleaving sentinel to encoding.descNotNull (0xfe) because
the other one could be used in keys. We want to be able to pull table and index
ids out of a key without the descriptor and it was possible for there to be
ambiguity with the previously used encoding.descNull (0xff).

Followup work:
- Allow interleaving indexes declared in a CREATE TABLE
- Support the CASCADE/RESTRICT foreign key shorthands
- Clean up the InterleavedBy back references when dropping a table/index
- Write some interleaving benchmarks
- Optimize the case where lots of interleaved data has to be scanned over (it
  currently always looks at every key.)
- Remove the SKIP DOC comments on opt_interleave in sql.y

For cockroachdb#2972.

Benchmarks are unchanged for non-interleaved data.

name                       old time/op    new time/op    delta
Insert1000_Cockroach-8       9.75ms ± 4%    9.71ms ± 5%   ~             (p=0.690 n=5+5)
Update1000_Cockroach-8       26.6ms ± 3%    26.3ms ± 5%   ~             (p=0.841 n=5+5)
Delete1000_Cockroach-8       13.1ms ± 3%    13.4ms ± 3%   ~             (p=0.421 n=5+5)
Scan1000_Cockroach-8         2.20ms ± 1%    2.20ms ± 1%   ~             (p=0.841 n=5+5)
WideTable1000_Cockroach-8     105ms ± 4%     107ms ± 6%   ~             (p=0.690 n=5+5)

name                       old alloc/op   new alloc/op   delta
Insert1000_Cockroach-8       4.25MB ± 0%    4.25MB ± 0%   ~             (p=0.111 n=4+5)
Update1000_Cockroach-8       6.42MB ± 0%    6.42MB ± 0%   ~             (p=0.841 n=5+5)
Delete1000_Cockroach-8       1.07MB ± 0%    1.07MB ± 0%   ~             (p=1.000 n=5+5)
Scan1000_Cockroach-8          317kB ± 0%     317kB ± 0%   ~             (p=0.444 n=5+5)
WideTable1000_Cockroach-8    21.3MB ± 0%    21.3MB ± 0%   ~             (p=0.222 n=5+5)

name                       old allocs/op  new allocs/op  delta
Insert1000_Cockroach-8        20.4k ± 0%     20.4k ± 0%   ~             (p=0.056 n=5+5)
Update1000_Cockroach-8        28.7k ± 0%     28.7k ± 0%   ~             (p=0.643 n=5+5)
Delete1000_Cockroach-8        12.6k ± 0%     12.6k ± 0%   ~             (p=1.000 n=5+5)
Scan1000_Cockroach-8          2.30k ± 0%     2.30k ± 0%   ~     (all samples are equal)
WideTable1000_Cockroach-8      224k ± 0%      224k ± 0%   ~             (p=0.841 n=5+5)
danhhz added a commit to danhhz/cockroach that referenced this issue Jul 22, 2016
@danhhz
sql: enable interleaved tables
097831a 
There is more performance tuning to do and joins between interleaved tables are
not optimized, but the format version changes are done.

This commit and after are incompatible with releases prior to beta-20160721.

Closes cockroachdb#2972.
@danhhz danhhz mentioned this issue Jul 22, 2016
Merged
danhhz added a commit to danhhz/cockroach that referenced this issue Jul 26, 2016
@danhhz
sql: handle dropping interleaved tables and indexes
7cef833 
Move a bit more of the scattered sql-to-kv logic into rowwriter.go and
tablewriter.go.

For cockroachdb#2972.
@danhhz danhhz mentioned this issue Jul 26, 2016
Merged
@a-robinson a-robinson mentioned this issue Sep 1, 2016
Merged
danhhz added a commit to danhhz/cockroach that referenced this issue Sep 6, 2016
@danhhz
sql: enable interleaved tables
994cb0f 
There is more performance tuning to do and joins between interleaved tables are
not optimized, but the format version changes are done.

This commit and after are incompatible with releases prior to beta-20160721.

Closes cockroachdb#2972.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@danhhz danhhz

Labels
C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception)
Projects
None yet
Milestone
Q3
Development

No branches or pull requests
2 participants
@danhhz @petermattis