Optimizing the same PREPAREd static query (without parameters)

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Optimizing the same PREPAREd static query (without parameters)

Mitar
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

Reply | Threaded
Open this post in threaded view
|

Re: Optimizing the same PREPAREd static query (without parameters)

David Rowley-3
On Mon, 7 Jan 2019 at 18:54, Mitar <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Optimizing the same PREPAREd static query (without parameters)

Mitar
Hi!

On Mon, Jan 7, 2019 at 12:09 AM David Rowley
<[hidden email]> wrote:
> On Mon, 7 Jan 2019 at 18:54, Mitar <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Optimizing the same PREPAREd static query (without parameters)

David Rowley-3
On Mon, 7 Jan 2019 at 21:40, Mitar <[hidden email]> wrote:

>
> On Mon, Jan 7, 2019 at 12:09 AM David Rowley
> <[hidden email]> wrote:
> > On Mon, 7 Jan 2019 at 18:54, Mitar <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Optimizing the same PREPAREd static query (without parameters)

Mitar
Hi!

On Mon, Jan 7, 2019 at 12:44 AM David Rowley
<[hidden email]> 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 values are uniformly distributed. For example, if statistics record three distinct column values, a generic plan assumes a column equality comparison will match 33% of processed rows. Column statistics also allow generic plans to accurately compute the 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

Reply | Threaded
Open this post in threaded view
|

Re: Optimizing the same PREPAREd static query (without parameters)

David Rowley-3
On Tue, 8 Jan 2019 at 06:45, Mitar <[hidden email]> 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