Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kzqJj-0004ah-19 for pgsql-bugs@arkaria.postgresql.org; Thu, 14 Jan 2021 00:16:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kzqJg-0005CS-LM for pgsql-bugs@arkaria.postgresql.org; Thu, 14 Jan 2021 00:16:44 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kzqJg-0005CJ-FA for pgsql-bugs@lists.postgresql.org; Thu, 14 Jan 2021 00:16:44 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kzqJd-0007cb-Ng for pgsql-bugs@lists.postgresql.org; Thu, 14 Jan 2021 00:16:43 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 10E0Gdht1343669; Wed, 13 Jan 2021 19:16:39 -0500 From: Tom Lane To: jacobmitash@gmail.com cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16824: Planner chooses poor path on query with Merge Join and pagination In-reply-to: <16824-cac06100763db5ae@postgresql.org> References: <16824-cac06100763db5ae@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Wed, 13 Jan 2021 22:13:55 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1343667.1610583399.1@sss.pgh.pa.us> Date: Wed, 13 Jan 2021 19:16:39 -0500 Message-ID: <1343668.1610583399@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form writes: > My understanding of this ID pagination is that it should be very quick as it > needs only find the ID in the index, and then scan the next 50 entries. Well, that's what it's doing, so far as the "sub s" scan is concerned. Your beef is with the subsequent join. > 1. Why is the Merge Join performing so slowly? > It seems to be because the planner doesn't recognize that it can apply the > subscription_id index condition on the inner table. If I explicitly tell it: > "AND si.subscription_id > '7ca1...'", then it applies an index condition and > is almost instant. We don't attempt to infer derived inequalities. Given "a = b AND b = c", the planner will deduce "a = c". However, given "a = b AND b > c", we do not deduce "a > c". This is an empirical decision based on the frequency with which such a deduction would help versus the planner cycles that would be spent looking for such cases. > 2. Why does the planner believe that Merge Join (as-is) is optimal here? The cost is estimated to be slightly lower. It might be right, so far as the time to run the join to completion (without a LIMIT) is concerned. Large nestloop joins tend to suck :-(. But the reason that it then makes the wrong choice with the LIMIT applied, fundamentally, is that the fraction of the total cost that will actually be incurred with the LIMIT present is nonlinear, and it doesn't know that. Doing better is a research problem. In short, there's nothing here that I'd call a bug that we're likely to fix anytime soon. In the meantime, if you can improve matters by manually injecting the extra inequality, that seems like the thing to do. regards, tom lane