[PERFORM] IMMUTABLE STABLE functions, daily updates
Hi, I have some functions that select data from tables which are daily or monthly updated. My functions are marked as STABLE. I am wondering if they perform better if I mark they as IMMUTABLE? Thank you, Sorin
Re: [PERFORM] IMMUTABLE STABLE functions, daily updates
On 10 November 2011 13:05, Sorin Dudui wrote: > Hi, > > > > I have some functions that select data from tables which are daily or > monthly updated. My functions are marked as STABLE. I am wondering if they > perform better if I mark they as IMMUTABLE? No. IMMUTABLE is only appropriate when there is no access to table data from within the function. An example of IMMUTABLE functions would be mathematical operations, where only the inputs and/or function constants are used to produce a result. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Subquery in a JOIN not getting restricted?
Kevin Grittner wrote: Merlin Moncure wrote: Well, this may not fit the OP's 'real' query Right, if I recall correctly, the OP said it was simplified down as far as it could be and still have the issue show. but the inner subquery is probably better written as a semi-join (WHERE EXISTS). Kevin's right. The real query involves several SQL and PL/pgsql functions (all now inlineable), custom aggregates, a union or two and a small coyote. I could post it, but that feels like "Please write my code for me". Still, if you really want to... Meanwhile, it's good for me to learn how the planner sees my queries and how I can best state them. I assume this is me not understanding something about restrictions across group-by nodes. If the query was more like select questions.id from questions join ( select sum(u.id) from users as u group by u.id ) as s on s.id = questions.user_id where questions.id = 1; would you no longer be surprised that it scanned all user rows? I.E. is the "group by" a red herring, which usually wouldn't be present without an aggregate, and the real problem is that the planner can't restrict aggregates? This comment in planagg.c may be relevant; I'm not doing min/max, but is it still true that GROUP BY always looks at all the rows, period? void preprocess_minmax_aggregates(PlannerInfo *root, List *tlist) ... /* We don't handle GROUP BY or windowing, because our current * implementations of grouping require looking at all the rows anyway, */ -- 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] IMMUTABLE STABLE functions, daily updates
On Nov 10, 2011 9:26 PM, "Thom Brown" wrote: > > On 10 November 2011 13:05, Sorin Dudui wrote: > > Hi, > > > > > > > > I have some functions that select data from tables which are daily or > > monthly updated. My functions are marked as STABLE. I am wondering if they > > perform better if I mark they as IMMUTABLE? > > No. IMMUTABLE is only appropriate when there is no access to table > data from within the function Sure it can be faster - the same way defining "fibonacci(int)" to always return 42 is faster, just incorrect. You can sometimes kinda get away with it if you are willing to reindex, drop prepared statements, reload functions, etc when the result changes. I would not recommend it.
Re: [PERFORM] Subquery in a JOIN not getting restricted?
Jay Levitt writes: > If the query was more like > select questions.id > from questions > join ( >select sum(u.id) >from users as u >group by u.id > ) as s > on s.id = questions.user_id > where questions.id = 1; > would you no longer be surprised that it scanned all user rows? Don't hold your breath waiting for that to change. To do what you're wishing for, we'd have to treat the GROUP BY subquery as if it were an inner indexscan, and push a join condition into it. That's not even possible today. It might be possible after I get done with the parameterized-path stuff I've been speculating about for a couple of years now; but I suspect that even if it is possible, we won't do it for subqueries because of the planner-performance hit we'd take from repeatedly replanning the same subquery. I'd suggest rephrasing the query to do the join underneath the GROUP BY. regards, tom lane -- 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] Subquery in a JOIN not getting restricted?
Don't hold your breath waiting for that to change. To do what you're wishing for, we'd have to treat the GROUP BY subquery as if it were an inner indexscan, and push a join condition into it. That's not even possible today. Thanks! Knowing "that's not a thing" helps; we'll just have to rephrase the query. Jay -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
