Re: [PATCH] Equivalence Class Filters - Mailing list pgsql-hackers

> On 07 Dec 2015, at 22:27, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
>
> On 08/12/15 05:27, David G. Johnston wrote:
>> On Mon, Dec 7, 2015 at 8:35 AM, Jim Nasby <Jim.Nasby@bluetreble.com <mailto:Jim.Nasby@bluetreble.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
identifyingqueries by their type/usage and optimize accordingly. Having the knobs available is necessary but putting on
afaç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
theplanner to consider expending more effort. 
>

Mysql have now 19 optimizer_switch parameters
https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html

Please don't do that.


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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers