[winrid]

Yes, I'm talking about skip-scan. I know pretty much all DBs can use compound index predicates for querying :)

I can think of several cases where it would have been nice to have, sometimes "good enough" for a particular read pattern is better than additional write overhead etc.

[dspillett]

One arguement against implementing nice to have features in a SQL or similar engine is that it adds complexity to the query planner: to try make it bright enough to know when to use it without using it when it is significantly detrimental, without ballooning there time needed to make a good enough¹ choice, without making a less good choice often enough that the overall efficiency of the choices the planner makes is lowered.

Of course an obvious counter is to implement it as an optional feature only used when explicitly requested by a query hint, with the counter counter being "is it worth the implementation effort given how rarely it will get used in that case?".

Oracle's developers were seated by the benefits, other database developers have not been.

There are ways to "emulate" the behaviour using CTEs to gain the expected benefit in at least some cases, but I've not experimented with this myself so can't speak for how well it works in practise, or if the extra legwork makes queries too convoluted to be easily maintained (or understood easily at all be future maintainers).

----

[1] noting that query planners do not strive to produce the best query plan, that is a Turing complete problem in many cases, instead trying to make a good enough choice in a reasonable amount of time

[winrid]

Yep :) although it's much more expensive to get a query plan wrong... increasing plan time, even just 1ms to 2ms, will also significantly hurt throughout!

I maintain a couple fairly large db deployments totalling around 100 machines, and it's always fun to see how query planner jitter affects things on a larger scale.