On Mon, Mar 17, 2014 at 11:15 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Johnston <pol...@yahoo.com> writes: >> Need to discuss the general "why" before any meaningful help on the "how" is >> going to be considered by hackers. > > Possibly worth noting is that in past discussions, we've concluded that > the most sensible type of hint would not be "use this plan" at all, but > "here's what to assume about the selectivity of this WHERE clause". > That seems considerably less likely to break than any attempt to directly > specify plan details.
Yeah -- the most common case I see is outlier culling where several repeated low non-deterministic selectivity quals stack reducing the row count estimate to 1. For example: SELECT * FROM foo WHERE length(bar) <= 1000 AND length(bar) >= 2; The user may have special knowledge that the above is very (or very un-) selective that is difficult or not cost effective to gather in the general case. IIRC in the archives (heh) there is a special workaround using indexes and some discussion regarding how a hypothetical feature involving user input selectivity estimates might look. I don't think that discussion is complete: the syntax for user input selectivity is an unsolved problem. There's a big difference between saying to the planner, "Use plan X" vs "Here's some information describing the data supporting choosing plan X intelligently". The latter allows for better plans in the face of varied/changing data, integrates with the planner in natural way, and encourages users to understand how the planner works. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers