From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | David Johnston <polobo(at)yahoo(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Planner hints in Postgresql |
Date: | 2014-03-17 17:49:25 |
Message-ID: | CAHyXU0w4DKKq0V+soax+OGWNf0dc5Vk7DJftdtH0cMoDfd=c4Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Mar 17, 2014 at 11:15 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> David Johnston <polobo(at)yahoo(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2014-03-17 17:52:02 | Re: First-draft release notes for next week's releases |
Previous Message | Atri Sharma | 2014-03-17 17:49:03 | Re: Planner hints in Postgresql |