Обсуждение: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
As I've come up to speed on SQL and PostgreSQL with some medium-complexity queries, I've asked a few questions about what the optimizer will do in various situations. I'm not talking about the seq-scan-vs-index type of optimizing; I mean "transforming within the relational calculus (algebra?) to an equivalent but more performant query". The same topics come up: - Flattening. I think that means "Merge the intent of the subquery into the various clauses of the parent query". - Inlining. That's "Don't run this function/subquery/view as an atomic unit; instead, push it up into the parent query so the optimizer can see it all at once." Maybe that's the same as flattening. - Predicate pushdown. That's "This subquery produces a lot of rows, but the parent query has a WHERE clause that will eliminate half of them, so don't produce the unnecessary rows." Am I right so far? Now, the big question, which I haven't seen documented anywhere: Under what circumstances can the optimizer do each of these things? For instance, I have a complex query that calculates the similarity of one user to every other user. The output is two columns, one row per user: select * from similarity(my_user_id); other_user | similarity% -----------|------------- 123 | 99 Being a novice at SQL, I first wrote it in PL/pgSQL, so I could stay in my imperative, iterative head. The query performed decently well when scanning the whole table, but when I only wanted to compare myself to a single user, I said: select * from similarity(my_user_id) as s where s.other_user = 321; And, of course, similarity() produced the whole table anyway, because predicates don't get pushed down into PL/pgSQL functions. So I went and rewrote similarity as a SQL function, but I still didn't want one big hairy SQL query. Ah ha! CTEs let you write modular subqueries, and you also avoid problems with lack of LATERAL. I'll use those. .. But of course predicates don't get pushed into CTEs, either. (Or maybe it was that they would, but only if they were inline with the predicate.. I forget now.) So you can see where I'm going. I know if I break everything into elegant, composable functions, it'll continue to perform poorly. If I write one big hairy, it'll perform great but it will be difficult to maintain, and it will be inelegant and a kitten will die. My tools are CTEs, subqueries, aliases, SQL functions, PL/pgSQL functions, and views (and other tools?) What optimizations do each of those prevent? We're on 9.0 now but will happily upgrade to 9.1 if that matters. Jay Levitt
Jay Levitt <jay.levitt@gmail.com> writes: > So you can see where I'm going. I know if I break everything into > elegant, composable functions, it'll continue to perform poorly. If I > write one big hairy, it'll perform great but it will be difficult to > maintain, and it will be inelegant and a kitten will die. My tools > are CTEs, subqueries, aliases, SQL functions, PL/pgSQL functions, and > views (and other tools?) What optimizations do each of those prevent? plpgsql functions are black boxes to the optimizer. If you can express your functions as single SQL commands, using SQL-language functions is usually a better bet than plpgsql. CTEs are also treated as optimization fences; this is not so much an optimizer limitation as to keep the semantics sane when the CTE contains a writable query. regards, tom lane
Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
От
Robert Haas
Дата:
On Wed, Nov 2, 2011 at 10:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Jay Levitt <jay.levitt@gmail.com> writes: >> So you can see where I'm going. I know if I break everything into >> elegant, composable functions, it'll continue to perform poorly. If I >> write one big hairy, it'll perform great but it will be difficult to >> maintain, and it will be inelegant and a kitten will die. My tools >> are CTEs, subqueries, aliases, SQL functions, PL/pgSQL functions, and >> views (and other tools?) What optimizations do each of those prevent? > > plpgsql functions are black boxes to the optimizer. If you can express > your functions as single SQL commands, using SQL-language functions is > usually a better bet than plpgsql. > > CTEs are also treated as optimization fences; this is not so much an > optimizer limitation as to keep the semantics sane when the CTE contains > a writable query. I wonder if we need to rethink, though. We've gotten a number of reports of problems that were caused by single-use CTEs not being equivalent - in terms of performance - to a non-CTE formulation of the same idea. It seems necessary for CTEs to behave this way when the subquery modifies data, and there are certainly situations where it could be desirable otherwise, but I'm starting to think that we shouldn't do it that way by default. Perhaps we could let people say something like WITH x AS FENCE (...) when they want the fencing behavior, and otherwise assume they don't (but give it to them anyway if there's a data-modifying operation in there). Whenever I give a talk on the query optimizer, I'm constantly telling people to take logic out of functions and inline it, avoid CTEs, and generally merge everything into one big query. But as the OP says, that is decidedly less than ideal from a code-beauty-and-maintenance point of view: people WANT to be able to use syntactic sugar and still get good performance. Allowing for the insertion of optimization fences is good and important but it needs to be user-controllable behavior. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
От
Andres Freund
Дата:
On Wednesday 02 Nov 2011 16:13:09 Robert Haas wrote: > On Wed, Nov 2, 2011 at 10:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Jay Levitt <jay.levitt@gmail.com> writes: > >> So you can see where I'm going. I know if I break everything into > >> elegant, composable functions, it'll continue to perform poorly. If I > >> write one big hairy, it'll perform great but it will be difficult to > >> maintain, and it will be inelegant and a kitten will die. My tools > >> are CTEs, subqueries, aliases, SQL functions, PL/pgSQL functions, and > >> views (and other tools?) What optimizations do each of those prevent? > > > > plpgsql functions are black boxes to the optimizer. If you can express > > your functions as single SQL commands, using SQL-language functions is > > usually a better bet than plpgsql. > > > > CTEs are also treated as optimization fences; this is not so much an > > optimizer limitation as to keep the semantics sane when the CTE contains > > a writable query. > > I wonder if we need to rethink, though. We've gotten a number of > reports of problems that were caused by single-use CTEs not being > equivalent - in terms of performance - to a non-CTE formulation of the > same idea. It seems necessary for CTEs to behave this way when the > subquery modifies data, and there are certainly situations where it > could be desirable otherwise, but I'm starting to think that we > shouldn't do it that way by default. Perhaps we could let people say > something like WITH x AS FENCE (...) when they want the fencing > behavior, and otherwise assume they don't (but give it to them anyway > if there's a data-modifying operation in there). +1. I avoid writing CTEs in many cases where they would be very useful just for that reasons. I don't even think some future inlining necessarily has to be restricted to one-use cases only... +1 for making fencing behaviour as well. Currently there is no real explicit method to specify this which is necessarily future proof (WITH, OFFSET 0)... Andres
Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
От
Claudio Freire
Дата:
On Wed, Nov 2, 2011 at 12:13 PM, Robert Haas <robertmhaas@gmail.com> wrote: > I wonder if we need to rethink, though. We've gotten a number of > reports of problems that were caused by single-use CTEs not being > equivalent - in terms of performance - to a non-CTE formulation of the > same idea. It seems necessary for CTEs to behave this way when the > subquery modifies data, and there are certainly situations where it > could be desirable otherwise, but I'm starting to think that we > shouldn't do it that way by default. Perhaps we could let people say > something like WITH x AS FENCE (...) when they want the fencing > behavior, and otherwise assume they don't (but give it to them anyway > if there's a data-modifying operation in there). Well, in my case, I got performance thanks to CTEs *being* optimization fences, letting me fiddle with query execution. And I mean, going from half-hour queries to 1-minute queries. It is certainly desirable to maintain the possibility to use fences when needed.
Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
От
Craig James
Дата:
On 11/2/11 10:22 AM, Claudio Freire wrote: > On Wed, Nov 2, 2011 at 12:13 PM, Robert Haas<robertmhaas@gmail.com> wrote: >> I wonder if we need to rethink, though. We've gotten a number of >> reports of problems that were caused by single-use CTEs not being >> equivalent - in terms of performance - to a non-CTE formulation of the >> same idea. It seems necessary for CTEs to behave this way when the >> subquery modifies data, and there are certainly situations where it >> could be desirable otherwise, but I'm starting to think that we >> shouldn't do it that way by default. Perhaps we could let people say >> something like WITH x AS FENCE (...) when they want the fencing >> behavior, and otherwise assume they don't (but give it to them anyway >> if there's a data-modifying operation in there). > Well, in my case, I got performance thanks to CTEs *being* > optimization fences, letting me fiddle with query execution. > > And I mean, going from half-hour queries to 1-minute queries. Same here. It was a case where I asked this group and was told that putting an "offset 0" fence in was probably the onlyway to solve it (once again reminding us that Postgres actually does have hints ... they're just called other things). > It is certainly desirable to maintain the possibility to use fences when needed. Indeed. Optimizer problems are usually fixed in due course, but these "fences" are invaluable when you have a dead web sitethat has to be fixed right now. Craig
Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
От
"Igor Neyman"
Дата:
> -----Original Message----- > From: Robert Haas [mailto:robertmhaas@gmail.com] > Sent: Wednesday, November 02, 2011 11:13 AM > To: Tom Lane > Cc: Jay Levitt; pgsql-performance@postgresql.org > Subject: Re: Guide to PG's capabilities for inlining, predicate > hoisting, flattening, etc? > ....... > ....... > Perhaps we could let people say > something like WITH x AS FENCE (...) when they want the fencing > behavior, and otherwise assume they don't (but give it to them anyway > if there's a data-modifying operation in there). > > .... > .... > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company Hints.... here we come :)
Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
От
Craig Ringer
Дата:
On 11/03/2011 04:22 AM, Igor Neyman wrote: > Hints.... here we come :) Pfft! No more than `VOLATILE' vs `STABLE' vs `IMMUTABLE'. It's a semantic difference, not just a performance hint. That said, I'm not actually against performance hints if done sensibly. -- Craig Ringer
Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
От
"Igor Neyman"
Дата:
> -----Original Message----- > From: Craig Ringer [mailto:ringerc@ringerc.id.au] > Sent: Thursday, November 03, 2011 5:07 AM > To: Igor Neyman > Cc: Robert Haas; Tom Lane; Jay Levitt; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Guide to PG's capabilities for inlining, > predicate hoisting, flattening, etc? > > On 11/03/2011 04:22 AM, Igor Neyman wrote: > > That said, I'm not actually against performance hints if done sensibly. > > -- > Craig Ringer > > ...sensibly As it is with any other feature... Igor Neyman
Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
От
Justin Pitts
Дата:
On Wed, Nov 2, 2011 at 11:13 AM, Robert Haas <robertmhaas@gmail.com> wrote:
[…] Perhaps we could let people saysomething like WITH x AS FENCE (...) when they want the fencing
behavior, and otherwise assume they don't (but give it to them anyway
if there's a data-modifying operation in there).
I would love to be able to test some of our CTE queries in such a scenario.
None of them do data modification. How hard would it be to patch my own build to disable the fence unilaterally for testing purposes?
Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
От
Gavin Flower
Дата:
On 03/11/11 09:22, Igor Neyman wrote: > >> -----Original Message----- >> From: Robert Haas [mailto:robertmhaas@gmail.com] >> Sent: Wednesday, November 02, 2011 11:13 AM >> To: Tom Lane >> Cc: Jay Levitt; pgsql-performance@postgresql.org >> Subject: Re: Guide to PG's capabilities for inlining, predicate >> hoisting, flattening, etc? >> ....... >> ....... >> Perhaps we could let people say >> something like WITH x AS FENCE (...) when they want the fencing >> behavior, and otherwise assume they don't (but give it to them anyway >> if there's a data-modifying operation in there). >> >> .... >> .... >> -- >> Robert Haas >> EnterpriseDB: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company > > Hints.... here we come :) > Is that a hint??? [Sorry, my perverse sense of humour kicked in] I too would like CTE's to take part in optimisation - as I don't like the mass slaughter of kittens, but I still want to pander to my speed addiction. So I think that having some sort of fence mechanism would be good. Cheers, Gavin