[Dzugaru]

Well, I don't know how QuestDB works, and I couldn't find anything in the original benchmark, but probably they already have some sort of (geo)index in place? It's really strange to search geo-data by scanning the whole surface of the Earth. The point that Clickhouse outperforms this by just sorting on one axis (and even not using any fancy 2D indices) is reasonable.

[olluk]

No, there are no indexes in QuestDB in the article. None. Zero. That's bold mistake in the ClickHouse article. Should be named Yes, QuestDb is Faster.

[bastawhiz]

Why does the lack of indexes matter? Especially when the size on disk is so much higher? Defining a sensible index isn't an unreasonable or daunting task, and minimal effort in CH got a 4x speedup over QuestDB. "It's faster if you invest literally zero time making it efficient" doesn't offer any practical benefit to anyone.

If it was demonstrated that Quest did a better job overall in the majority of cases where an optimization would have been missed, that's one thing. But this feels awfully nitpicky.

[jerrinot]

The article is not _just adding an index_. They are embedding one of the search fields in a table _primary key_. That likely means the whole physical table layout is tailored for that single specific query.

While it can help to win this very benchmark it's questionable whether it's usable in practice. Chances are an analytical database serves queries of various shapes. If you only need to run a single query over and over again then you might be better off with a stream processing engine anyway.

[bastawhiz]

The primary key is, in effect, an index. Specializing on the latitude field of a table of geographic data seems like an incredibly small thing to nitpick.

[Dzugaru]

Yeah, I've read more carefully and it seems they're doing full scan.

[tomhallett]

I was curious to hear more details about this statement - "while QuestDB utilizes its full indexing strategy to read just a tiny fraction of the actual data". Did QuestDB create indexes in their QuestDB benchmark but just not mention it? Are there geoindexes which are automatically enabled which do help (but are of less value in the general sense from Clickhouse' perspective)?

[twoodfin]

I don't know how QuestDB is implemented in any detail, but this statement struck me as confused. My understanding is that for this query, QuestDB is performing a full scan of the relevant columns, and the point of the blog post was how fast their JIT engine for filtering makes this.