From: Tom Lane <tgl@sss.pgh.pa.us>
To: Oliver Mattos <omattos@gmail.com>
cc: Arne Roland <A.Roland@index.de>,
        "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Query planner gaining the ability to replanning after start of
 query execution.
In-reply-to: 
 <CAER07O1r-Hj4erAUBVPJfqJ1Vt-B66q73fCfjH3hBdrYo_Y1cQ@mail.gmail.com>
References: 
 <CAER07O05V+3Sut=--F0Cagve81Rj7dm4fDdsUoS7NiuS+sU4hw@mail.gmail.com>
 <252cab3895894337a3a88275f423fe0b@index.de>
 <CAER07O1r-Hj4erAUBVPJfqJ1Vt-B66q73fCfjH3hBdrYo_Y1cQ@mail.gmail.com>
Comments: In-reply-to Oliver Mattos <omattos@gmail.com>
	message dated "Mon, 13 Nov 2017 21:51:32 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4275.1510613373.1@sss.pgh.pa.us>
Date: Mon, 13 Nov 2017 17:49:33 -0500
Message-ID: <4276.1510613373@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Oliver Mattos <omattos@gmail.com> writes:
>> Can you be more elaborate how you'd want to go about it?

> ... If another candidate plan is now lower cost, the current plan would be
> terminated[1] by setting a flag instructing each execnode to return as
> if it had reached the end of the input, although still caching the
> node selectivity values, and the new plan started from scratch.

Quite aside from the implementation difficulties you'll have, that
approach is a show-stopper right there.  You can't just restart from
scratch, because we may already have shipped rows to the client, or
for DML cases already inserted/updated/deleted rows (and even if you
could roll those back, we've possibly fired triggers with unpredictable
side effects).  Queries containing volatile functions are another no-fly
zone for this approach.

I can't see any way around that without unacceptable performance costs
(i.e. buffering all the rows until we're done) or wire-protocol breakage.

I think that a more practical way to address the class of problems
you're talking about is to teach the planner to have some notion of
worst-case as well as expected-case costs, and then incorporate some
perhaps-configurable amount of risk aversion in its choices.

			regards, tom lane

PS: please do not top-post, and do not quote the entire darn thread
in each message.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance