Foreign key usage

hello -

i have tried to create a test grandparent–>parent–>child<–alternateParent relationship to see how cockroachdb performs. the tables were populated using a simple node script with several “for” loops. here is the row count:

  • grandparent: 10 rows
  • parenttable: 1000 rows
  • childtable: 50k rows
  • alternateParent: 50 rows

i started this question with this forum post

it took a great deal of time to load the child table. i have a similar MariaDB table which is close to a half-million records.

here are the results of the first query:

SELECT count(*)
FROM parenttable, alternateParent, childtable
WHERE 1=1
AND (alt3ernateParent.id = childtable.newkey)
AND (parenttable.id = childtable.parenttable)
AND (alternateParent.id = 5);

pq: sql: memory budget exceeded: 10240 bytes requested, 154420224 bytes in budget

to be fair, i am doing all this on a google instance. are there any suggestions on how this can be improved, besides not using the dreaded “JOIN” statement and breaking this out into separate queries?

note - even adding a unique child-key value to the SELECT statement gave the same results.

my first post listed above seemed to indicate that cockroach is not currently optimized for such an operation but may be so in the future.

my create statements:

CREATE TABLE grandParentTable
( id INT PRIMARY KEY
, grandParentTable_name VARCHAR(50) NOT NULL
);
CREATE TABLE parentTable
( id INT PRIMARY KEY
, grandParentTable INT NOT NULL REFERENCES grandParentTable(id)
, parentTable_name VARCHAR(50) NOT NULL
, INDEX (grandParentTable)
);
CREATE TABLE alternateParent
( id INT PRIMARY KEY
, alternateParent_value VARCHAR(50) NOT NULL
);
CREATE TABLE childTable
( id INT PRIMARY KEY
, parentTable INT NOT NULL REFERENCES parentTable (id)
, alternateParent INT NOT NULL REFERENCES alternateParent (id)
, childTable_name VARCHAR(50) NOT NULL
, INDEX (parentTable )
, index (alternateParent)
);

Try this:

SELECT count(*) 
FROM 
   (SELECT * FROM alternateParent WHERE id = 5) AS alternateParent
JOIN childtable ON childtable.newkey = alternateParent.id
JOIN parenttable ON parenttable.id = childtable.parenttable

or alternatively

SELECT count(*)
FROM 
   parenttable
JOIN childtable ON parenttable.id = childtable.parenttable
JOIN 
   (SELECT * FROM alternateParent WHERE id = 5) AS alternateParent
   ON alternateParent.id = childtable.newkey

I think one of the two will perform better. If not we are planning some improvements somewhat soon (https://github.com/cockroachdb/cockroach/issues/10632).

Just to clarify, your original query was using JOIN. When you write “SELECT… FROM a,b,c” this actually means “SELECT … FROM a CROSS JOIN b CROSS JOIN c”, which is actually the most expensive kind of join there is! :slight_smile: