Running "cl search .count" on codalab.stanford.edu times out

[epicfaace]

Running "cl search .count" on codalab.stanford.edu times out. It should instead give us the correct result.

[jzwang43]

@epicfaace I was looking at the query generated by search_bundles. Looks like it's already pretty optimized. Do you know if there's any way to improve the performance if there are a lot of bundles like on Stanford cluster?

SELECT DISTINCT count(*) AS count_1 
FROM bundle 
WHERE bundle.uuid IN (SELECT bundle_metadata.bundle_uuid 
FROM bundle_metadata 
WHERE bundle_metadata.metadata_key = :metadata_key_1 AND bundle_metadata.metadata_value >= :metadata_value_1)
 LIMIT -1 OFFSET :param_1

[epicfaace]

Some optimizations we could do:

• we could potentially get rid of the outer query? What do param_1 and metadata_key_1 and metadata_value_1 mean?
• add indexes for metadata_key and metadata_value in the bundle_metadata table, and an index for uuid in bundle

[epicfaace]

maybe this will be faster

SELECT distinct bundle_uuid 
FROM bundle_metadata 
WHERE bundle_metadata.metadata_key = :metadata_key_1 AND bundle_metadata.metadata_value >= :metadata_value_1

[pranavjain]

@jzwang43 Will start working on it next week (02/09)

[pranavjain]

Removing this from p1.

This would require experimentation, moving to p2.