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 1tyD6v-003qdi-Ak for pgsql-general@arkaria.postgresql.org; Fri, 28 Mar 2025 17:03:13 +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 1tyD6t-00DINy-MM for pgsql-general@arkaria.postgresql.org; Fri, 28 Mar 2025 17:03:11 +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 1tyD6t-00DINq-9Q for pgsql-general@lists.postgresql.org; Fri, 28 Mar 2025 17:03:11 +0000 Received: from mail-oa1-x2e.google.com ([2001:4860:4864:20::2e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tyD6r-001k4Y-01 for pgsql-general@postgresql.org; Fri, 28 Mar 2025 17:03:10 +0000 Received: by mail-oa1-x2e.google.com with SMTP id 586e51a60fabf-2c77a5747e0so1317329fac.2 for ; Fri, 28 Mar 2025 10:03:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1743181387; x=1743786187; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=KFoyjvmp7GNzJUQeO5v/H8tKS3If+8fAD2JyujQ2ri8=; b=CzhVOEI6+zLVS0rhLT0Y26QBZt0r2uWIXAUek70G7N698khsDl0+AP3QU1FJ9cN6wv uwGYVX6FNtqckNmbq501qnVtObk8g0Kcs2RyHqhkfyXd1vpLYJCtM4qvSpDR4QFQ7CUl pgEwTXAGMP7txRzmxzs7acuac2eg2a8BMDuxEsJhpBh5cuFRF8BFH8Acg3t700Sh0w78 PhO2G10jtossQZ/oxpeO/egOpZbtAI6Ug2hX/Z6oMCoLlGpTlO+NxokuwU1WN7B7UWCa HUK8uUxOf2kbNkJhXtl4JjuGyItuJHMpnuGLeY7SK29/e2hkHAWF0vvrz/m1hXdKKQjN eGeg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1743181387; x=1743786187; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=KFoyjvmp7GNzJUQeO5v/H8tKS3If+8fAD2JyujQ2ri8=; b=QfAz1JAJbvVUujDG56sNBf3MmZxQfGQplEznTO0YasNctHTb2aOYBcLM3ScQDFIs6g nm+GWxCklnZ9qTHy7KaVh6lYqHCOrgrtqYk5d+H+FsGbINnMSqtuQsOvn26wzFfVKKaB EjNQHWEHHaXIEiR3Hn2voSuXb62tNcjBm0gDaKQWttTaA/8sGgewGi8CqfnEDGMLa8DI UcGsx0TxkAIClg0Gc6b1s7TPlYmN+Y4CFfIqnY8hJnZqJ5Z/QAvkxvhvoOeFBYjpLRKx EnZ/9733YlMMFAp7xjMU2eSUcScZnQHKK1X6HWyPpBIv5qdF/enptgiKMsqCYsdnfsKo 5HNw== X-Gm-Message-State: AOJu0YxETTeybzlCk4VMtPKHCQ2YZr1TIaqqew8v+xOrmKb2FHoU6yPA 6Y4DtkFhWugR5zVK7QtH67Yp3VjrbF+/UGwpr87d+iuHf+7u/1cB+6l4rWwIdggtu5AbencbufE T8nNWF+8A1eg9sUnsSDfvl1EyBP4= X-Gm-Gg: ASbGnct0tOkjymEV/HKIgrY3EwngoVGXO/dN3M6ud5Rl3riKM0VdZoBZSfGH9+RiIOU /oQP86O4VN+JLnLJjmgJ0XDHdw15xqZRYRyepzVRSmj0AZIFYkhs8CxTfDHT3E5jDnfnSiqkeA2 QcdmxDNW9owa6PSTolDsiXZIgm X-Google-Smtp-Source: AGHT+IGKESYTV649/0kdPXEstLQWxeNULWRdyE4MrAX3t1KyWJ8zEra8J7oR2QVxTRVy+tsFYyVqdnm8kS/6U2UEjL8= X-Received: by 2002:a05:6870:9726:b0:29e:559b:d694 with SMTP id 586e51a60fabf-2c84828a71dmr5511688fac.32.1743181386693; Fri, 28 Mar 2025 10:03:06 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Fri, 28 Mar 2025 10:02:29 -0700 X-Gm-Features: AQ5f1Jqq02mzSULt0lYQXFvqx_1o5EZ-yMkssjbnSWb33WDzZpG8y1Krulypkzw Message-ID: Subject: Re: BTREE index: field ordering To: Ron Johnson Cc: PostgreSQL mailing lists Content-Type: multipart/alternative; boundary="0000000000005548d606316a0bab" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005548d606316a0bab Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Mar 28, 2025 at 9:24=E2=80=AFAM Ron Johnson wrote: > > "at least for WHERE conditions that are selective" confuses me. Aren't > _all_ WHERE clauses selective? > > From earlier in the email, selectivity is a scale, the wording here implies "has a meaningful selectivity". "Fundamentally yes, but you also have to consider how selective the conditions are." - Laurenz where true - selectivity 0 where not(is_deleted) - selectivity < .01 ish in many cases Selectivity is evaluating cardinality with an eye on the frequency of the values you are actually going to be filtering on. So low cardinality booleans can be highly selective in usage if you are looking for the rare false in a sea of trues but low selectivity if looking through those trues. David J. --0000000000005548d606316a0bab Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, Mar 28, 2025 at 9:24=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.com> wr= ote:

"at least for WHERE conditions that are selective= " confuses me.=C2=A0 Aren't _all_ WHERE clauses selective?


From earlier in the = email, selectivity is a scale, the wording here implies "has a meaning= ful selectivity".

"Fundamentally yes, but = you also have to consider how selective the conditions are." - Laurenz=

where true - selectivity 0
where not(is_deleted)= - selectivity < .01 ish in many cases

Selectivity = is evaluating cardinality with an eye on the frequency of the=C2=A0values y= ou are actually going to be filtering on.=C2=A0 So low cardinality booleans= can be highly selective in usage=C2=A0if you are looking for the rare fals= e in a sea of trues but low selectivity if looking through those trues.

David J.

--0000000000005548d606316a0bab--