Re: [PERFORM] Poor performance using CTE
On 22/11/12 04:32, Andres Freund wrote: On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote: On 11/21/2012 09:59 AM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: If we're going to do it can we please come up with something more intuitive and much, much more documented than OFFSET 0? And if/when we do this we'll need to have big red warnings all over then release notes, since a lot of people I know will need to do some extensive remediation before moving to such a release. The probability that we would actually *remove* that behavior of OFFSET 0 is not distinguishable from zero. I'm not terribly excited about having an alternate syntax to specify an optimization fence, but even if we do create such a thing, there's no need to break the historical usage. I wasn't talking about removing it. My point was that if the optimization fence around CTEs is removed a lot of people will need to rework apps where they have used them for that purpose. And I continue to think that spelling it OFFSET 0 is horribly obscure. +1 WITH foo AS (SELECT ...) (barrier=on|off)? 9.3 introduces the syntax, defaulting to on 9.4 switches the default to off. Greetings, Andres Freund WITH foo AS (SELECT ...) (fence=on|off)? WITH foo AS (SELECT ...) (optimisation_fence=on|off)? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Poor performance using CTE
On 22/11/12 08:42, Andrew Dunstan wrote: On 11/21/2012 02:30 PM, Gavin Flower wrote: WITH FENCE foo AS (SELECT ...) default? WITHOUT FENCE foo AS (SELECT ...) :-) Nah! I prefer this, but it is too specific to 'WITH', and very unSQL standardish! Alternatively one of the following 1. WITH UNFENCED foo AS (SELECT ...) 2. WITH NO FENCE foo AS (SELECT ...) 3. WITH NOT FENCE foo AS (SELECT ...) I loke the firsat variant, but the 3rd is most SQL standardish! As Tom (I think) pointed out, we should not have a syntax tied to CTEs. cheers andrew If other SQL constructs have a optimisation fence, then the FENCE NOT FENCE syntax can be used theire as well. So what am I missing? (obviously WITHOUT FENCE would not make sense elsewhere, but I wasn't really being serious when I suggested it!) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hints (was Poor performance using CTE)
Le mercredi 21 novembre 2012 17:34:02, Craig James a écrit : On Wed, Nov 21, 2012 at 5:42 AM, Kevin Grittner kgri...@mail.com wrote: It's a tough problem. Disguising and not documenting the available optimizer hints leads to more reports on where the optimizer should be smarter, and has spurred optimizer improvements. ... Regarding the above-mentioned benefits we would stand to lose by having clear and documented hints, perhaps we could occasionally solicit input on where people are finding hints useful to get ideas on where we might want to improve the optimizer. As far as worrying about people using hints to force a plan which is sub-optimal -- isn't that getting into nanny mode a bit too much? Toward that end, the hint documentation (which is almost always viewed as HTML) could be prefaced by a strong suggestion to post performance questions in this group first, with links to the subscribe page and the how to report performance problems FAQ. The hint documentation could even be minimalistic; suggest to developers that they should post their problematic queries here before resorting to hints. That would give the experts an opportunity to provide the normal advice. The correct hint syntax would be suggested only when all other avenues failed. We have hooks in PostgreSQL. We already have at least one extension which is using that to change the planner behavior. We can have a bit more hooks and try to improve the cost estimate, this part of the code is known to be built by reports and human estimations, also the 9.2 version got heavy modifications in this area. Let the 'Hints' be inside an extension thus we are able to track them and fix the planner/costestimate issues. I don't see why PostgreSQL needs 'Hints' *in-core*. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [PERFORM] Hints (was Poor performance using CTE)
On 22/11/12 06:28, Craig James wrote: On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway m...@joeconway.com mailto:m...@joeconway.com wrote: On 11/21/2012 08:05 AM, Heikki Linnakangas wrote: Rather than telling the planner what to do or not to do, I'd much rather have hints that give the planner more information about the tables and quals involved in the query. A typical source of bad plans is when the planner gets its cost estimates wrong. So rather than telling the planner to use a nested loop join for a INNER JOIN b ON a.id http://a.id = b.id http://b.id, the user could tell the planner that there are only 10 rows that match the a.id http://a.id = b.id http://b.id qual. That gives the planner the information it needs to choose the right plan on its own. That kind of hints would be much less implementation specific and much more likely to still be useful, or at least not outright counter-productive, in a future version with a smarter planner. You could also attach that kind of hints to tables and columns, which would be more portable and nicer than decorating all queries. I like this idea, but also think that if we have a syntax to allow hints, it would be nice to have a simple way to ignore all hints (yes, I suppose I'm suggesting yet another GUC). That way after sprinkling your SQL with hints, you could easily periodically (e.g. after a Postgres upgrade) test what would happen if the hints were removed. Or a three-way choice: Allow, ignore, or generate an error. That would allow developers to identify where hints are being used. Craig Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support Or perhaps hints should have the pg version attached, so that they are automatically ignored when the pg version changed? Problem may then become people reluctant to upgrade because their hints relate to a previous version! Sigh... Even requiring registration of hints and expiring them after a limited time period would not work - as people would simply automate the process of registration application... Cheers, Gavin
Re: SOLVED - RE: [PERFORM] Poor performance using CTE
On Tue, Nov 20, 2012 at 02:24:01PM -0600, Merlin Moncure wrote: On Tue, Nov 20, 2012 at 1:53 PM, Jon Nelson jnelson+pg...@jamponi.net wrote: As can be seen by the current conversation, not everyone is convinced that CTEs ought to be an explicit optimization barrier On Tue, Nov 20, 2012 at 1:26 PM, Claudio Freire klaussfre...@gmail.com wrote: It *could* just be a lack of imagination on my part. But if it were not, then it'd be nice for it to be done automatically (since this particular CTE behavior bites enough people already). Sure. I just find it personally hard to find a good demarcation line between A: queries where pushing quals through are universally beneficial and wanted and B: queries where we are inserting an explicit materialization step to avoid planner issues, particularly where there is substantial overlap with between A and C: queries that are written with a CTE and arguably shouldn't be. Put another way, I find CTE to express: 'this then that' where joins express 'this with that'. So current behavior is not surprising at all. All that said, there could be a narrow class of low hanging cases (such as the OP's) that could be sniped...I'm just skeptical. Is thi -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
