On 08/12/15 08:34, Evgeniy Shishkin wrote:
On 07 Dec 2015, at 22:27, Gavin Flower <gavinflo...@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.na...@bluetreble.com
<mailto:jim.na...@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 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
