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
Date: Thu, 25 Feb 2021 22:00:18 -0500
Message-ID: <219912.1614308418@sss.pgh.pa.us> (raw)
In-Reply-To: <1581042da8044e71ada2d6e3a51bf7bb@index.de>
References: <1581042da8044e71ada2d6e3a51bf7bb@index.de>
Arne Roland <A.Roland@index.de> writes:
> I want to examine the exhaustive search and not the geqo here. I'd expect the exhaustive search to give the plan with the lowest cost, but apparently 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 partitions 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, A.Roland@index.de
Subject: Re: Disabling options lowers the estimated cost of a query
In-Reply-To: <219912.1614308418@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox