Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1j6guf-000244-4q for pgsql-bugs@arkaria.postgresql.org; Tue, 25 Feb 2020 20:34:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j6gud-0004J5-Um for pgsql-bugs@arkaria.postgresql.org; Tue, 25 Feb 2020 20:34:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j6gud-0004GL-FB for pgsql-bugs@lists.postgresql.org; Tue, 25 Feb 2020 20:34:39 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1j6guW-0006OG-NR for pgsql-bugs@postgresql.org; Tue, 25 Feb 2020 20:34:38 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 01PKYS6R008618; Tue, 25 Feb 2020 15:34:28 -0500 From: Tom Lane To: Alexey Bashtanov cc: pgsql-bugs@postgresql.org Subject: Re: planner weirdness: a join uses nestloop with checking condition when there are two subplan-or-hashed subqueries In-reply-to: References: Comments: In-reply-to Alexey Bashtanov message dated "Tue, 25 Feb 2020 14:32:17 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <8616.1582662868.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Tue, 25 Feb 2020 15:34:28 -0500 Message-ID: <8617.1582662868@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Alexey Bashtanov writes: > Planner seems to make a weird choice of join algorithm -- O(M*N) = > nestloop -- under certain circumstances. In the particular case you've got here, the estimated cost of the "foo" scan is so large that it swamps everything else: Nested Loop Left Join (cost=3D0.00..53543550.45 rows=3D1135 width=3D8) (= actual time=3D714.969..1407.137 rows=3D1051 loops=3D1) ... -> Seq Scan on foo (cost=3D0.00..53045460.77 rows=3D149 width=3D4) (a= ctual time=3D711.364..714.312 rows=3D21 loops=3D1) ... -> Materialize (cost=3D0.00..4100.92 rows=3D175728 width=3D8) (actual= time=3D0.003..19.522 rows=3D175728 loops=3D21) -> Seq Scan on qux (cost=3D0.00..2535.28 rows=3D175728 width=3D= 8) (actual time=3D0.017..19.754 rows=3D175728 loops=3D1) Yeah, an indexscan on qux would probably be a little cheaper, but it could not make as much as 1% difference to the total cost, because the "foo" scan is accounting for more than 99% already. I don't recall the exact rules, but the planner stops worrying about cost differences less than 1%, in order to save planning time by cutting the number of cases considered. So it's probably just discarding the indexed-join alternative as not being meaningfully better. What seems actually out of line with reality is the cost estimate for the "foo" scan, which probably is down to the fact that cost_qual_eval_walker is not very bright about what to do with the AlternativeSubPlan constructs. It looks like it's assuming that the non-hashed alternatives will be chosen, which they aren't (if they were, this estimate might not be so far out of line). But we can't just switch it to make the other assumption, because that would skew the results for other cases. regards, tom lane