Requirements on Optimizers [was ANSWERS to "What's wrong with XQuery" question]

[Martin Probst]

> Part of the answer, I think, is to make performance less reliant on good
> optimization. In XSLT, the key() function goes a long way towards this:
> by giving programmers a tool to control when indexes are built and used,
> performance of many join constructs becomes much more predictable
> [...]
> I've always felt that the anathema felt
> in the database query community towards such constructs is misplaced -
> alhough it's great when optimizers are good enough that they aren't needed,
> I've seen programmers tearing their hair out trying to second-guess the
> optimizer, and in such cases it's not clear we're doing programmers a
> service.

This is indeed somewhat funny. A huge part of the value proposition of
databases is that you write your application logic independent of
storage and lookup considerations. After an application is developed
to be correct, someone knowledgeable is supposed to tune the database
a bit, build some indexes, and everything is fine.

At the same time, the reality often seems to be that many teams
struggle with database performance a lot, because the assumption that
you can do optimizations independently and after application
development doesn't hold all that often. Code that does something in
an O(n) or even O(n**2) fashion tends to be actually incorrect in many
settings, not just a bit slower.

I've seen people actually write tests for their database queries to
make sure they get a reasonable execution plan, but that is extremely
hard to do (assertions on a query plan).

It might be nice to have language constructs saying "guarantee to me
that you do this in O(something), otherwise fail".

Martin