Re: Pet Peeves?

The biggest peeve I still have to fight is attached to the old "why aren't
there any optimizer hints?" tree.  PostgreSQL forces you to understand a
non-trivial amount of how the query optimizer works before you can get it
to do the right thing once you get beyond a small database, and nobody
likes doing the "why isn't it using the index?!" dance.

When you turn enable_seqscan off and it proceeds to do a seqscan anyway
when there's seemingly a perfect index right there, it's pretty
frustrating.  I spent the better part of a weekend last year fighting a
battle with a single "select * from t where type='x' and ts>='date1' and
ts<='date2", running against a giant table with an index on (type,ts) that
just stopped using that index for mysterious reasons.  You do not want to
seqscan a billion rows.

The main thing I'd like to see here is more visibility into rejected
plans, so that at least people could see those costs.  If I could have
seen whether the cost estimate on the index-based plan was going up or
down as I tweaked parameters/updated stats, at least then I could quantify
my progress toward returning to where the right plan was preferred one.
The flip side is that as it is right now, it's also hard to answer the
question "how close am I to having this plan fail?" until it already has.
I know there's been some academic work in this area as part of classes on
database internals, I'd like to see some of that turn into a production
feature.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD