Re: [PERFORM] fast read of binary data

On Mo, 2012-11-12 at 12:18 +0100, Albe Laurenz wrote: > Eildert Groeneveld wrote: > > I am currently implementing using a compressed binary storage scheme > > genotyping data. These are basically vectors of binary data which may be > > megabytes in size. > > > > Our current implementation uses the

Re: [PERFORM] Hints (was Poor performance using CTE)

On Wed, Nov 21, 2012 at 8:05 AM, Heikki Linnakangas wrote: > On 21.11.2012 15:42, Kevin Grittner wrote: >> >> Better, IMV, would be to identify what sorts of hints people actually >> find useful, and use that as the basis for TODO items for optimizer >> improvement as well as inventing clear ways

Re: [PERFORM] Poor performance using CTE

On 11/22/2012 08:38 AM, Gavin Flower wrote: > I suspect most people are blissfully unaware of CTE's being fenced, or > at least not really sure what it means. So I suspect NOT FENCE would > be the better default. It's also probably more standard, and a better fit with what other DBs do. Pg would s

Re: [PERFORM] Poor performance using CTE

On 11/22/2012 03:30 AM, Gavin Flower wrote: > On 22/11/12 04:56, Heikki Linnakangas wrote: >> On 21.11.2012 17:42, Gavin Flower wrote: >>> On 22/11/12 04:32, Andres Freund wrote: On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote: > I wasn't talking about removing it. My point was that if

Re: [PERFORM] Poor performance using CTE

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

Re: [PERFORM] Hints - experiences from other rdbms

> > 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

Re: [PERFORM] Hints (was Poor performance using CTE)

Craig James 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 p

Re: [PERFORM] Hints (was Poor performance using CTE)

Craig James writes: > On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway wrote: >> 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 >>

Re: [PERFORM] Hints (was Poor performance using CTE)

On 11/21/2012 09:28 AM, Craig James wrote: > > > On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway > 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 th

Re: [PERFORM] Hints (was Poor performance using CTE)

On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway 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

Re: [PERFORM] Hints (was Poor performance using CTE)

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 esti

Re: [PERFORM] Poor performance using CTE

Claudio Freire writes: > collapse_cte_limit? The join collapse limits address a completely different problem (ie, explosion of planning time with too many relations), and are pretty much useless as a model for this. As multiple people told you already, optimization fences are typically wanted fo

Re: [PERFORM] Poor performance using CTE

On Wed, Nov 21, 2012 at 1:35 PM, Andrew Dunstan wrote: Why syntax? What about a guc? collapse_cte_limit? >>> >>> Because there are very good reasons to want to current behaviour. A guc >>> is a global either/or so I don't see it helping much. >> >> set collapse_cte_limit=8; >> with

Re: [PERFORM] Hints (was Poor performance using CTE)

On Wed, Nov 21, 2012 at 5:42 AM, Kevin Grittner 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

Re: [PERFORM] Poor performance using CTE

On 11/21/2012 11:32 AM, Claudio Freire wrote: On Wed, Nov 21, 2012 at 1:24 PM, Andres Freund wrote: On 2012-11-21 13:16:25 -0300, Claudio Freire wrote: On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund wrote: +1 WITH foo AS (SELECT ...) (barrier=on|off)? 9.3 introduces the syntax, defaultin

Re: [PERFORM] Poor performance using CTE

On 2012-11-21 13:32:45 -0300, Claudio Freire wrote: > On Wed, Nov 21, 2012 at 1:24 PM, Andres Freund wrote: > > On 2012-11-21 13:16:25 -0300, Claudio Freire wrote: > >> On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund wrote: > >> > +1 > >> > > >> > WITH foo AS (SELECT ...) (barrier=on|off)? > >> >

Re: [PERFORM] Poor performance using CTE

On Wed, Nov 21, 2012 at 1:24 PM, Andres Freund wrote: > On 2012-11-21 13:16:25 -0300, Claudio Freire wrote: >> On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund wrote: >> > +1 >> > >> > WITH foo AS (SELECT ...) (barrier=on|off)? >> > >> > 9.3 introduces the syntax, defaulting to on >> > 9.4 switche

Re: [PERFORM] Poor performance using CTE

On 2012-11-21 13:16:25 -0300, Claudio Freire wrote: > On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund wrote: > > +1 > > > > WITH foo AS (SELECT ...) (barrier=on|off)? > > > > 9.3 introduces the syntax, defaulting to on > > 9.4 switches the default to off. > > Why syntax? What about a guc? > > coll

Re: [PERFORM] Poor performance using CTE

On Wed, Nov 21, 2012 at 12:32 PM, Andres Freund wrote: > +1 > > WITH foo AS (SELECT ...) (barrier=on|off)? > > 9.3 introduces the syntax, defaulting to on > 9.4 switches the default to off. Why syntax? What about a guc? collapse_cte_limit? -- Sent via pgsql-performance mailing list (pgsql-per

Re: [PERFORM] Hints (was Poor performance using CTE)

On 21.11.2012 15:42, Kevin Grittner wrote: Better, IMV, would be to identify what sorts of hints people actually find useful, and use that as the basis for TODO items for optimizer improvement as well as inventing clear ways to specify the desired coercion. I liked the suggestion that a CTE which

Re: [PERFORM] Poor performance using CTE

On 21.11.2012 17:42, Gavin Flower wrote: On 22/11/12 04:32, Andres Freund wrote: On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote: 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 us

Re: [PERFORM] Poor performance using CTE

On 21 November 2012 15:21, Andrew Dunstan wrote: > And I continue to think that spelling it "OFFSET 0" is horribly obscure. I'm not sure that it's any more obscure than the very idea of an optimisation fence. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Supp

Re: [PERFORM] Poor performance using CTE

On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote: > > On 11/21/2012 09:59 AM, Tom Lane wrote: > >Andrew Dunstan 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 ha

Re: [PERFORM] Poor performance using CTE

On 11/21/2012 09:59 AM, Tom Lane wrote: Andrew Dunstan 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 peop

Re: [PERFORM] Poor performance using CTE

Andrew Dunstan 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 ex

Re: [PERFORM] Poor performance using CTE

On 11/21/2012 08:04 AM, Heikki Linnakangas wrote: On 21.11.2012 01:53, Tom Lane wrote: I think the more interesting question is what cases wouldn't be covered by such a rule. Typically you need to use OFFSET 0 in situations where the planner has guessed wrong about costs or rowcounts, and I th

Re: [PERFORM] Hints (was Poor performance using CTE)

Craig Ringer wrote: > On 11/21/2012 09:35 AM, Craig James wrote: >> Why not make an explicit hint syntax and document it? I've still >> don't understand why "hint" is a dirty word in Postgres. There are >> a half-dozen or so ways in common use to circumvent or correct >> sub-optimal plans. > > The

Re: [PERFORM] Hints (was Poor performance using CTE)

On 11/20/2012 08:15 PM, Craig Ringer wrote: I think it's time to admit that and get the syntax in place for CTEs so there's room to optimize them later, rather than cementing CTEs-as-fences in forever as a Pg quirk. I know I'm just some schmo, but I'd vote for this. I'm certainly guilty of us

Re: [PERFORM] Poor performance using CTE

On 21 November 2012 13:04, Heikki Linnakangas wrote: > Yes, I strongly feel that we should. Writing a query using WITH often makes > it more readable. It would be a shame if people have to refrain from using > it, because the planner treats it as an optimization fence. +1 -- Peter Geoghegan

Re: [PERFORM] Poor performance using CTE

On 21.11.2012 01:53, Tom Lane wrote: I think the more interesting question is what cases wouldn't be covered by such a rule. Typically you need to use OFFSET 0 in situations where the planner has guessed wrong about costs or rowcounts, and I think people are likely using WITH for that as well.