From: Tom Lane <tgl@sss.pgh.pa.us>
To: Arne Roland <A.Roland@index.de>
cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Disabling options lowers the estimated cost of a query
In-reply-to: <1581042da8044e71ada2d6e3a51bf7bb@index.de>
References: <1581042da8044e71ada2d6e3a51bf7bb@index.de>
Comments: In-reply-to Arne Roland <A.Roland@index.de>
	message dated "Thu, 25 Feb 2021 23:07:08 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <219911.1614308418.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 25 Feb 2021 22:00:18 -0500
Message-ID: <219912.1614308418@sss.pgh.pa.us>
Precedence: bulk

Arne Roland <A.Roland@index.de> writes:
> I want to examine the exhaustive search and not the geqo here. I'd expec=
t the exhaustive search to give the plan with the lowest cost, but apparen=
tly it doesn't. I have found a few dozen different querys where that isn't=
 the case. I attached one straight forward example. For the join of two pa=
rtitions a row first approach would have been reasonable.

Hmm.  While the search should be exhaustive, there are pretty aggressive
pruning heuristics (mostly in and around add_path()) that can cause us to
drop paths that don't seem to be enough better than other alternatives.
I suspect that the seqscan plan may have beaten out the other one at
some earlier stage that didn't think that the startup-cost advantage
was sufficient reason to keep it.

It's also possible that you've found a bug.  I notice that both
plans are using incremental sort, which has been, um, rather buggy.
Hard to tell without a concrete test case to poke at.

			regards, tom lane