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 1sLntq-00Gf4v-HV for pgsql-hackers@arkaria.postgresql.org; Mon, 24 Jun 2024 17:54:42 +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 1sLnto-00AlRX-RQ for pgsql-hackers@arkaria.postgresql.org; Mon, 24 Jun 2024 17:54:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sLnto-00AlRP-Hw for pgsql-hackers@lists.postgresql.org; Mon, 24 Jun 2024 17:54:40 +0000 Received: from mail-wm1-x330.google.com ([2a00:1450:4864:20::330]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sLntm-002vKV-FO for pgsql-hackers@postgresql.org; Mon, 24 Jun 2024 17:54:39 +0000 Received: by mail-wm1-x330.google.com with SMTP id 5b1f17b1804b1-421757d217aso50829005e9.3 for ; Mon, 24 Jun 2024 10:54:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=bowt-ie.20230601.gappssmtp.com; s=20230601; t=1719251677; x=1719856477; darn=postgresql.org; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=tNkyBy3id4L9dRP+BTlCGdg29S1OkeHsXkc7vuYlf4A=; b=t0RKXUpBpcvkbzOXYv5T+2/4Eix3eTg7F8VXv1fPCygr4wL2MGXNDJbUemO8ZBfrWn 10tLXQlCDJy4+0dAwL5NMQoZCqNdsaZPRoDx1khtB6BuZ9vquUXPtkw5zYxc6P5L37T6 qCTJ6mfE6QozP1wHau77cv/dc9uzAAIsw921mdKA/R8UzwT7iUWLNtsFlrXmglUUrpQd cE8z6KIaYOKvIbdVal+ULM3tKAEOdh0aiKRS/mp0k7TsjfeiSWqZUlERR3YPoaGJtrUw 6vKvkUNfIzfBkCEtm7tXdwzVdnMXaIQlwqB+g6arWBVHRpF7lIPxQsG1Wk6d91wUQ+iD PIJg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1719251677; x=1719856477; h=content-transfer-encoding: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=tNkyBy3id4L9dRP+BTlCGdg29S1OkeHsXkc7vuYlf4A=; b=TCAKwDeYvO1UVab6i4DOKsHgVbax6HhD68JZ64HLVpzktOZiTirx0zwyEx/nb61s6X e3Fppm4LFcJO+vNpKDEQK8LJtzVIXrtkIIPUp1wSyvWfd/gDYOhXZrmgntENZvyQKtzw 2oIDivAgDDVu/Xnqb3pUSrzB5yuNDdvLGOvF9zR7BTjaLONPQUKcW5ykouM106luH7W5 pg6fwB2K1+GEbJNNqtMZHT8To0CREX7FanKovjc+qdIvFbTQ4WyPge1TAFSgSFUQy7Pd xC5rHQfvgmaRd/qeFmqpFv0d+vcLKjBqn+z80LDmpltBhYL83Pu9LvXSTjATDCH2Lno+ okew== X-Forwarded-Encrypted: i=1; AJvYcCWeRtIdTTjSamwVKjrfi3zOgh5MImuvHvJnXTezjSHW8m+UkPW6stsaD8Wg14Yf/rgVtxeBu1CxEWZYMZZLiuPLD1dv3vqDc/HvyMev X-Gm-Message-State: AOJu0YwbWiDjTGqnz+jLw4XMpdEKp2gExiEYuOi+SB4FjfBeOxMKZBdR Bno+CQFLgY4VLbHUaZzUb1Y7a//PXM8k/gikvAUaTK3f1/qOJFfhnRroV01vhxJgzF5Smlm4r6J B41t8yrgU9Jre54/x1DqKPJrEEIPbcyALKJc5SQ== X-Google-Smtp-Source: AGHT+IHFIzS1ywHzJ0vhn/aqbNXwJOdeKQMLZcR77dXQeTOID/PqFTfO2zCAbRk7298TOhdDeWMxLkjqCbwzLNX7Usc= X-Received: by 2002:adf:e745:0:b0:362:12d4:5ea8 with SMTP id ffacd0b85a97d-366e965dc53mr4108673f8f.66.1719251676937; Mon, 24 Jun 2024 10:54:36 -0700 (PDT) MIME-Version: 1.0 References: <567ED6CA.2040504@sigaev.ru> <74e3c8bc-dccd-45e5-ad2d-6a6e96fc5864@postgrespro.ru> <6d27d752-db0b-4cac-9843-6ba3dd7a1e94@postgrespro.ru> <6850c306-4e9d-40b7-8096-1f3c7d29cd9e@postgrespro.ru> <0f50882c-e639-4856-aab6-6ccfec848164@postgrespro.ru> In-Reply-To: From: Peter Geoghegan Date: Mon, 24 Jun 2024 13:54:10 -0400 Message-ID: Subject: Re: POC, WIP: OR-clause support for indexes To: Robert Haas Cc: Alena Rybakina , Alexander Korotkov , jian he , pgsql-hackers@postgresql.org, Marcos Pegoraro , teodor@sigaev.ru, Andrei Lepikhov , Tomas Vondra , Peter Eisentraut , Ranier Vilela Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, Jun 24, 2024 at 1:46=E2=80=AFPM Peter Geoghegan wrote: > BTW, did you know MySQL has long supported the latter? It has a <=3D> > operator, which is basically a non-standard spelling of IS NOT > DISTINCT FROM. Importantly, it is indexable, whereas right now > Postgres doesn't support indexing IS NOT DISTINCT FROM. If you're > interested in working on this problem within the scope of this patch, > or some follow-up patch, I can take care of the nbtree side of things. To be clear, I meant that we could easily support "where mycol =3D 5 OR mycol IS NULL" and have nbtree handle that efficiently, by making it a SAOP internally. Separately, we could also make IS NOT DISTINCT FROM indexable, though that probably wouldn't need any work in nbtree. --=20 Peter Geoghegan