Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u8JeO-000sXq-5z for pgsql-sql@arkaria.postgresql.org; Fri, 25 Apr 2025 14:03:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u8JeM-00Bf0U-FK for pgsql-sql@arkaria.postgresql.org; Fri, 25 Apr 2025 14:03:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u8JeM-00Bezx-6K for pgsql-sql@lists.postgresql.org; Fri, 25 Apr 2025 14:03:31 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1u8JeJ-0020vC-0u for pgsql-sql@lists.postgresql.org; Fri, 25 Apr 2025 14:03:30 +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 53PE3OIs1954270; Fri, 25 Apr 2025 10:03:25 -0400 From: Tom Lane To: Igor Kustov cc: "pgsql-sql@lists.postgresql.org" Subject: Re: default cardinality with non-existent value In-reply-to: <58011745572979@mail.yandex.ru> References: <58011745572979@mail.yandex.ru> Comments: In-reply-to Igor Kustov message dated "Fri, 25 Apr 2025 12:35:39 +0300" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1954268.1745589804.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 25 Apr 2025 10:03:24 -0400 Message-ID: <1954269.1745589804@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Igor Kustov writes: > I noticed that the optimizer expects one row when there is a > condition on the field for which optimizer does not know the value > based on statistics, Actually, in this example it's probably estimating a selectivity fraction of exactly zero (plus or minus roundoff error), but later that gets clamped to the minimum allowed rowcount estimate of one row. See var_eq_const's handling of the its-not-any-of-the-MCVs case: https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob;f=3Dsrc/bac= kend/utils/adt/selfuncs.c;h=3Da96b1b9c0bc69e30865221c5e24e37c594f16d21;hb=3D= HEAD#l413 The clamp-to-one-row bit is done by clamp_row_est: https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob;f=3Dsrc/bac= kend/optimizer/path/costsize.c;h=3D60b0fcfb6be542552903f00643de82ac1e91cb8= 0;hb=3DHEAD#l5348 regards, tom lane