This is built on PostgreSQL, which has EXPLAIN. I think EXPLAIN is crucial, since it can absolutely kill your application if it isn’t using indices. I was saddened to see that the new fancy Haskell DB lib Rel8 has this problem.
If an SQL replacement could be statically checked to never do “sequence scan”, that would be nice. Is that possible? Does EdgeDB provide guarantees like this?
PostgreSQL will sometimes do a sequence scan even if an index exists, if the table statistics indicate that the I/O cost of scanning the entire table would be less than the I/O cost of scanning the entire index then reading all the matching rows from the table. Sometimes, it’s even correct!
Since the property “uses a sequence scan” depends on the data in the database, not just the schema, it’s not possible to statically verify that a given query does not use a sequence scan by only examining the schema.
There’s various situations for which sequential scans are suited, for different reasons. Sometimes sequential scans are faster. Sometimes you don’t want to build an index because you anyways end up sequentially scanning through only a hand full tuples, after having gotten rid of most. The index might be too big compared to any gain to make any sense. It often depends on the data you’re dealing with and on what you are trying to achieve.
All right. But if the priority were to make it easier to avoid bad-performing queries, is there nothing that can be done? If I, in the schema, could assert “this table never grows large”, or if I during a query could state “this subquery would match only a small number of rows”, couldn’t that be used to make a “safer” (better worst case performance) query language?
Hmm, seems like a staging URL…? Similar content also appears to available at https://www.edgedb.com/docs/tutorial/queries.
That’s the link I put, but lobsters changed it when I hit post…
Probably because the page says that this is the canonical URL:
<link rel="canonical" href="https://website-atgsmhega-edgedb.vercel.app/docs/tutorial/queries"/>
Ah, indeed. I messaged them on Twitter about this issue.