Re: [PATCH] Equivalence Class Filters

On 08/12/15 08:34, Evgeniy Shishkin wrote:
>> On 07 Dec 2015, at 22:27, Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:
>>
>> On 08/12/15 05:27, David G. Johnston wrote:
>>> On Mon, Dec 7, 2015 at 8:35 AM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com <mailto:Jim(dot)Nasby(at)bluetreble(dot)com>>wrote:
>>>
>>> On 12/6/15 10:38 AM, Tom Lane wrote:
>>>
>>> I said "in most cases". You can find example cases to support
>>> almost any
>>> weird planner optimization no matter how expensive and
>>> single-purpose;
>>> but that is the wrong way to think about it. What you have to
>>> think about
>>> is average cases, and in particular, not putting a drag on
>>> planning time
>>> in cases where no benefit ensues. We're not committing any
>>> patches that
>>> give one uncommon case an 1100X speedup by penalizing every
>>> other query 10%,
>>> or even 1%; especially not when there may be other ways to fix it.
>>>
>>>
>>> This is a problem that seriously hurts Postgres in data
>>> warehousing applications. We can't keep ignoring optimizations
>>> that provide even as little as 10% execution improvements for 10x
>>> worse planner performance, because in a warehouse it's next to
>>> impossible for planning time to matter.
>>>
>>> Obviously it'd be great if there was a fast, easy way to figure
>>> out whether a query would be expensive enough to go the whole 9
>>> yards on planning it but at this point I suspect a simple GUC
>>> would be a big improvement.
>>>
>>>
>>> Something like "enable_equivalencefilters" but that defaults to false unlike every one existing "enable_*" GUC?
>>>
>>> ​It would be a lot more user-friendly to have something along the lines of "planner_mode (text)" with labels like "star, transactional, bulk_load, etc..." because I suspect there are other things we'd want to add if we start identifying queries by their type/usage and optimize accordingly. Having the knobs available is necessary but putting on a façade would make the user more straight-forward for the common cases.
>>>
>>> David J.
>>>
>> How about:
>>
>> planning_time_base 10 # Default effort, may be increased or decreased as required - must be at least 1
>> planning_time_XXXX 0 # By default, planner makes no (or minimal) effort to optimise for feature XXXX
>>
>> So for some people, adjusting planning_time_base may be sufficient - but for more specialised cases, people can tell the planner to consider expending more effort.
>>
> Mysql have now 19 optimizer_switch parameters
> https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html
I notice that they are either on or off, I suspect that it is better to
have some sort of measure of how much extra effort the planner should make.

>
> Please don't do that.
I think having some might be useful - though in most situations, having
a general indicator to the planner might be sufficient.

From reading the thread, I have the impression that for some extreme
workloads, them some extra twiddling would be useful even though for
most people it simply be an unnecessary complication.

In over twenty years I've never needed such knobs, but I might get a
project next year where they might be useful. So I agree that for most
situations, such extra stuff is not needed - but I'd like additional
options available if I ever needed them.

>
> I'd rather like some sort of pg_stat_statements, which would track execution and planning time.
> On new query, we can lookup if query can benefit from more planning time.
> But i don't know how costly this can be.
>
>> Cheers,
>> Gavin
>>
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers