Hypothesis: This bug looks related but a bit different from what I described in T321855. Looking at the query plan we see FastRangeCountOp and DistinctTermScanOp, this indicates that the Blazegraph optimizer is incorrectly trying to apply the Group by and count optimization. I described how it works in the query optimization guide. This should only fire when there is a single BGP in the body of the query. The optimizer fails to pick up on that there is supposed to be a join happening, and so this specific optimization should not be applied.

The following query works. The difference from query #3 is that I've added ?item to the projection.:

SELECT DISTINCT ?item ?value {
  VALUES ?item {wd:Q2 wd:Q111}.
  ?item wdt:P610 ?value.
}

The unoptimized AST is pretty similar. Looking at the query plan we see in query #3, it goes straight to a distinct term scan operation, it doesn't explicitly say it does any join operations. Inputs: 2 and outputs 13802 intermediate results. This number seems to be roughly 2 times the cardinality of the BGP at the time of writing.
In my query the optimizer seems to pick up on that we've asked ?item to be projected and runs a completely different set of operations, including joins. This results in the expected outputs.
For Ainali's query it also results in join operations, likely because the presence of multiple BGPs prevent the distinct term scan optimization from being applied.
Hypothesis: The Blazegraph optimizer is overzealously trying to apply the distinct term scan optimization, which I believe is used when you're using distinct on a single triple pattern, and fails to pick up on that a join is needed.

I have a tool that produces data relied upon by MSynAbot, which handles site-wide automatic page protections on Wikidata. The source can be found on the Toolforge NFS mount at /data/project/kvasir1/entityusage/entityusage.py and you can find the data it produces and a readme at https://tools-static.wmflabs.org/kvasir1/. It does read from the wbc_entity_usage table for every single foundation wiki having the Wikibase Client extension installed and enabled, but doesn't perform any joins. It also has a connection open to the Toolforge database to which it writes aggregated data to. It's a long running task which IIRC takes something like 3 hours to complete. It runs weekly.

This seems like something worth doing. The Arch Linux distribution made the switch a while back. The Bzip2 format predates multi-core processors by about a decade.

I'm not familiar with the proposed query language or database, is that Spark SQL running on a Hadoop cluster? And would it basically perform the task in brute-force fashion?