Clojurians Log v2
off-topic
> Execution Time: 323.859 ms
So, I think to myself: "Alright, so I know that this SQL query is slow mostly due to multiple self-joins that builds a tree and extracts related IDs. I'll cache those IDs in a materialized view, should shave off quite some time."
> Execution Time: 34811.318 ms
The time was shaved on.
But, one :materialized in a CTE `later:
> Execution Time: 443.648 ms
Ugh, RDBMS are so advanced but can be so dumb sometimes.
Still gotta figure out why the cache-using query is still slower than the one without a cache.
It might be slower because [...]Initially it was slower because in relatively modern PostgreSQL versions CTEs are not materialized. The SQL itself gets basically inlined - not the data. So the same "cached" query gets executed for every single row in the "main" table.
But then it was slower due to some issues in indexes. Got solved by a new index over (a, b) in addition to already existing indexes over (a) and (b).
> What does 'plan' say?
A plethora of things. :) But in the end I got it down to 200 ms, with the help of https://explain.tensor.ru/.
A plethora of things.hehe :) And for this reason, I am glad for SQLite's expert mode. SQLite is itself an AI that tells me what I should do to make my DB work better. And it's almost always right.
It might be slower because the self-joins can somehow be cached into memory, and that would cause it to not hit additional pages; also, be aware that SQL might already be materializing these IDs by itself
What does 'plan' say? Perhaps some index is being ignored and temp index is being constructed on the fly?