surprising performance issue

[campoy]

I just ran this query on top of github.com/golang/go:

  SELECT
  	LANGUAGE(t.tree_entry_name, b.blob_content) as lang,
	t.tree_entry_name as name,
       b.blob_content as code
  FROM refs r 
       JOIN commits c ON r.commit_hash = c.commit_hash
       JOIN commit_trees ct ON c.commit_hash = ct.commit_hash
       JOIN tree_entries t ON ct.tree_hash = t.tree_hash
       JOIN blobs b ON t.blob_hash = b.blob_hash

This finishes in 0.65s 🎉

Unfortunately this other request takes forever:

  SELECT
	t.tree_entry_name as name,
        b.blob_content as code
  FROM refs r 
       JOIN commits c ON r.commit_hash = c.commit_hash
       JOIN commit_trees ct ON c.commit_hash = ct.commit_hash
       JOIN tree_entries t ON ct.tree_hash = t.tree_hash
       JOIN blobs b ON t.blob_hash = b.blob_hash
   WHERE LANGUAGE(t.tree_entry_name, b.blob_content) = 'go'

Trying to see whether I could find a workaround I wrote this second query:

SELECT name, code
FROM 
(
  SELECT
	LANGUAGE(t.tree_entry_name, b.blob_content) = 'go' as lang,
	t.tree_entry_name as name,    
        b.blob_content as code
  FROM refs r 
       JOIN commits c ON r.commit_hash = c.commit_hash
       JOIN commit_trees ct ON c.commit_hash = ct.commit_hash
       JOIN tree_entries t ON ct.tree_hash = t.tree_hash
       JOIN blobs b ON t.blob_hash = b.blob_hash
) as blobs
WHERE lang = 'go'

Both of these requests take too long for me to wait.

[ajnavarro]

@campoy could you send the result of the queries but adding at the beginning EXPLAIN FORMAT=tree please? It will give us really valuable info about query performance.

[jfontan]

With current release these queries take around 1 minute in my machine. Have you been using indexes to go that fast?

The last two queries return 0 rows as the string returned by LANGUAGE is Go.

[kuba--]

SELECT  	t.tree_entry_name AS NAME,  b.blob_content AS CODE
FROM refs r 
       JOIN commits c ON r.commit_hash = c.commit_hash
       JOIN commit_trees ct ON c.commit_hash = ct.commit_hash
       JOIN tree_entries t ON ct.tree_hash = t.tree_hash
       JOIN blobs b ON t.blob_hash = b.blob_hash
WHERE LANGUAGE(t.tree_entry_name, b.blob_content) = 'Go';

[kuba--]

There is no index involved, because so far we cannot create an index across multiple columns from different tables and LANGUAGE(t.tree_entry_name, b.blob_content) will require that.

[erizocosmico]

I have executed all three queries and the results in terms of time are quite similar:

• First query: 1069809 rows in 1m34.779148952s
• Second query: 760122 rows in 1m26.175958486s
• Third query: 760122 rows in 1m36.166300878s

Most likely, what happened is that you saw no output because every single row was being filtered due to LANGUAGE(..., ...) = 'go'. It should be Go instead of go, which is the correct enry result.

I think for the sake of user friendliness we should either output lowercased language names or specify in the documentation the correct names for all languages. WDYT @ajnavarro?

Correct second query:

  SELECT
	t.tree_entry_name as name,
        b.blob_content as code
  FROM refs r 
       JOIN commits c ON r.commit_hash = c.commit_hash
       JOIN commit_trees ct ON c.commit_hash = ct.commit_hash
       JOIN tree_entries t ON ct.tree_hash = t.tree_hash
       JOIN blobs b ON t.blob_hash = b.blob_hash
   WHERE LANGUAGE(t.tree_entry_name, b.blob_content) = 'go'

Correct third query:

SELECT name, code
FROM 
(
  SELECT
	LANGUAGE(t.tree_entry_name, b.blob_content) as lang,
	t.tree_entry_name as name,    
        b.blob_content as code
  FROM refs r 
       JOIN commits c ON r.commit_hash = c.commit_hash
       JOIN commit_trees ct ON c.commit_hash = ct.commit_hash
       JOIN tree_entries t ON ct.tree_hash = t.tree_hash
       JOIN blobs b ON t.blob_hash = b.blob_hash
) as blobs
WHERE lang = 'Go'

That said, if what you were aiming for was getting all Go code from the repo, this is a more concise and less expensive query to to that:

SELECT tree_entry_name as name, blob_content as code
FROM tree_entries NATURAL JOIN blobs
WHERE LANGUAGE(tree_entry_name, blob_content) = 'Go'

[kuba--]

personally I think LANGUAGE should return all lowercases. There is no point why we need to capitalise lang's name.

[erizocosmico]

@kuba-- totally agree

[ajnavarro]

@erizocosmico @kuba-- I cannot think in any other downside than old queries that are using correct Enry strings will fail. Maybe is totally OK. WDYT @smola ?

[smola]

UAST might break if you change LANGUAGE to lowercase. You should check that. Other than that, it should be ok.

[smola]

Another option is adding support for LOWER and UPPER

[erizocosmico]

@smola UAST already lowercases everything, afaik. But lower and upper + adding to the docs the list of languages returned by enry sounds good too.

[smola]

👍 to adding a list of languages by enry. bblfsh has the same issue. We could document that list in src-d/enry README.md and link it from both projects.
See bblfsh/documentation#191

[ajnavarro]

Opening new issue to handle the real problem: #658