Re: WIP: cross column correlation ...

2011/2/23 PostgreSQL - Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>:
> i thought there was an agreement that we don't want planner hints?

Well, I want them. I think some other people do, too. Whether those
people are more numerous than than the people who don't want them, and
how much that matters either way, is another question. I don't want
to have to use them very often, but I like to have an out when I get
desperate.

> as tom pointed out - many broken queries come out of some query generator where even the design to make the design is broken by design.
> personally i like query generators as long as other people use them ... telling people that this is the wrong way to go is actually financing my holiday next week ... ;).  in general - hibernate and stuff like that is a no-go.
>
> personally i like the type of planner hints oleg and teodor came up with - i think we should do more of those hooks they are using but hiding it in some syntax is not a good idea.
> it does not change the query and it still gives a lot of room to toy around. it looks like a compromise.
>
> however, oleg's contrib module does not fix the core problem of cross column statistics because a hint is usually static but you want flexible selectivity.

IIRC, what Teodor and Oleg did was a contrib module that excluded a
certain index from consideration based on a GUC. That to me is a
little more hacky than just wiring the selectivity estimate. You're
going to need to set that just before each query that needs it, and
reset it afterwards, so it's actually worse than just decorating the
queries, IMHO. Also, I haven't run into any actual problems in the
field that would be solved by this approach, though I am sure others
have. IME, most bad query plans are caused by either incorrect
estimates of selectivity, or wrongheaded notions about what's likely
to be cached. If we could find a way, automated or manual, of
providing the planner some better information about the facts of life
in those areas, I think we'd be way better off. I'm open to ideas
about what the best way to do that is.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company