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 1pprzB-0007xo-Sk for pgsql-general@arkaria.postgresql.org; Fri, 21 Apr 2023 14:43:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pprz9-0001mU-Gg for pgsql-general@arkaria.postgresql.org; Fri, 21 Apr 2023 14:43:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pprz9-0001mK-5R for pgsql-general@lists.postgresql.org; Fri, 21 Apr 2023 14:43:39 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pprz6-004Fuz-9s for pgsql-general@lists.postgresql.org; Fri, 21 Apr 2023 14:43:37 +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 33LEhYmk3341125; Fri, 21 Apr 2023 10:43:34 -0400 From: Tom Lane To: Isaiah Langford cc: "pgsql-general@lists.postgresql.org" Subject: Re: Why does the planner reduce the planned rows when filtering single values in an array In-reply-to: References: Comments: In-reply-to Isaiah Langford message dated "Fri, 21 Apr 2023 02:41:13 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3341123.1682088214.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 21 Apr 2023 10:43:34 -0400 Message-ID: <3341124.1682088214@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Isaiah Langford writes: > Why is the planner expecting the number of rows to be reduced by the joi= n filter? Is there any way I can correct the planner here? I think you're running into a couple of issues here: * ANALYZE fails to record any useful stats for a single-row table. It can't form a histogram with only one entry, but it also fails to put the value into the MCV list, because there's a heuristic that an MCV must appear more than once. Possibly we could think harder about what to do with such cases, but at the moment the planner has no knowledge that the only value in single_value_table is "1". * Even if it did know that, the logic in scalararraysel() is quite inadequate for the case of "variable =3D ANY(variable)". It looks like that's only been fleshed out for cases where one side or the other is a constant. Lot of unfinished work here :-( regards, tom lane