Thread: Optimizing the same PREPAREd static query (without parameters)
Hi! If I have a PREPAREd query without parameters (static) and I EXECUTE it repeatedly in the same session, does PostgreSQL learn/optimize anything across those runs? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
On Mon, 7 Jan 2019 at 18:54, Mitar <mmitar@gmail.com> wrote: > If I have a PREPAREd query without parameters (static) and I EXECUTE > it repeatedly in the same session, does PostgreSQL learn/optimize > anything across those runs? Yes, it will generate the query plan on the first invocation of EXECUTE and use that plan for all subsequent EXECUTEs for the session until you DEALLOCATE the prepared query or DISCARD PLANS/ALL; -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Hi! On Mon, Jan 7, 2019 at 12:09 AM David Rowley <david.rowley@2ndquadrant.com> wrote: > On Mon, 7 Jan 2019 at 18:54, Mitar <mmitar@gmail.com> wrote: > > If I have a PREPAREd query without parameters (static) and I EXECUTE > > it repeatedly in the same session, does PostgreSQL learn/optimize > > anything across those runs? > > Yes, it will generate the query plan on the first invocation of > EXECUTE and use that plan for all subsequent EXECUTEs for the session > until you DEALLOCATE the prepared query or DISCARD PLANS/ALL; So this sounds more like no? So the same plan is used, so PostgreSQL is not further optimizing the plan based on any statistics or anything? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
On Mon, 7 Jan 2019 at 21:40, Mitar <mmitar@gmail.com> wrote: > > On Mon, Jan 7, 2019 at 12:09 AM David Rowley > <david.rowley@2ndquadrant.com> wrote: > > On Mon, 7 Jan 2019 at 18:54, Mitar <mmitar@gmail.com> wrote: > > > If I have a PREPAREd query without parameters (static) and I EXECUTE > > > it repeatedly in the same session, does PostgreSQL learn/optimize > > > anything across those runs? > > > > Yes, it will generate the query plan on the first invocation of > > EXECUTE and use that plan for all subsequent EXECUTEs for the session > > until you DEALLOCATE the prepared query or DISCARD PLANS/ALL; > > So this sounds more like no? So the same plan is used, so PostgreSQL > is not further optimizing the plan based on any statistics or > anything? You asked if it learned anything. I mentioned that it learns the query plan, so I'd have said "Yes". If you're asking if it caches the result and foregoes scanning the underlying tables, then that's a "No". Else what further optimising did you have in mind? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Hi! On Mon, Jan 7, 2019 at 12:44 AM David Rowley <david.rowley@2ndquadrant.com> wrote: > If you're asking if it caches the result and foregoes scanning the > underlying tables, then that's a "No". Else what further optimising > did you have in mind? For example, it could learn better statistics. In documentation [1] it is written: > A generic plan assumes that each value supplied to EXECUTE is one of the column's distinct values and that column valuesare uniformly distributed. For example, if statistics record three distinct column values, a generic plan assumes acolumn equality comparison will match 33% of processed rows. Column statistics also allow generic plans to accurately computethe selectivity of unique columns. So it could learn that the values used are not distinct values, or that column values are not uniformly distributed? And maybe decide to change the plan? So it makes a plan, runs it, determines that the plan was not as good as expected, I run it again, it decides to try another plan. It is better, it decides to switch to it and keep it. [1] https://www.postgresql.org/docs/devel/sql-prepare.html Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
On Tue, 8 Jan 2019 at 06:45, Mitar <mmitar@gmail.com> wrote: > So it could learn that the values used are not distinct values, or > that column values are not uniformly distributed? And maybe decide to > change the plan? So it makes a plan, runs it, determines that the plan > was not as good as expected, I run it again, it decides to try another > plan. It is better, it decides to switch to it and keep it. Sounds like machine learning in the query planner. Nothing like this exists in core, but there have been projects in the past to do this, for example, https://axleproject.eu/2015/07/17/augmenting-the-postgresql-planner-with-machine-learning/ Perhaps there are others that have worked on similar things, however, I don't recall any conversations on these postgresql.org mailing lists though. Maybe it's worth trying searching the archives? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services