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 1sBx6q-002EnU-Gv for pgsql-general@arkaria.postgresql.org; Tue, 28 May 2024 13:43:25 +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 1sBx6q-009hwP-JW for pgsql-general@arkaria.postgresql.org; Tue, 28 May 2024 13:43:24 +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 1sBx6q-009hwH-6l for pgsql-general@lists.postgresql.org; Tue, 28 May 2024 13:43:24 +0000 Received: from mail-oo1-xc36.google.com ([2607:f8b0:4864:20::c36]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sBx6n-001BMs-Ie for pgsql-general@postgresql.org; Tue, 28 May 2024 13:43:23 +0000 Received: by mail-oo1-xc36.google.com with SMTP id 006d021491bc7-5b9a35a0901so330831eaf.0 for ; Tue, 28 May 2024 06:43:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1716903800; x=1717508600; 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=tQo2Ug9vr7aD/36pFgJLpIz7FYnf3hGc/JHVjfX0Jxs=; b=lCBbwLSdpVlEc7PGfC51ETj7EoelgtJx5rqVzoTPDWxPYmprULINMxPcZUQoPZx6VK iYi5Va0T6I1MnWR3a4DuHzeArPlam7t6DIGtfkh40oDmDIj4hTW1up8yIkIq16OwfkHQ mDZtJGZf0kbP2kg4FGpT6Ctx54DN9YajN55hgXluBayw3pPmxXx1nxkUl8pw43YJOOEf bUazclzRT1drBlvpltcnSQFmnie6ELn5TNkjr6WPWD+ilbBeDlxCBALuy5mcC+m4DXH7 Fj9LouwD0ZdzCvYOttFuRDxOlhQvqVEEN8lH71LXpuhVNhO1mGX3LsbFiwD0xg7ijsVe xUDg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1716903800; x=1717508600; 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=tQo2Ug9vr7aD/36pFgJLpIz7FYnf3hGc/JHVjfX0Jxs=; b=uGduXaiOQCCWfsF8jF0IIevTZq72iWrTuoc5iU6qqw/gQ14TxhN5Kpb0dDlQTfxHuO nQNe0KVICIZXG+FbBnqxMpexhMRP2b0Ai1ibzNqmEtfNnh1kuJ18GoBgkue2SQHGmCUZ /kOS2/zd+IhkZbCBzJ3/L4HaWHJtpo8RHMIFUt4baYtTda+OSrmYpWvRIT3BD1rNLvA8 O6UYRa6AV13zzVbJ35dxcfHZ14A/Qc/wytkGGjTiq8J3N05qLOrR8dDzMXEx+4BIGC4S yPI07Xs1/TpLA/Qn/rUkfv/QQTtAd1qT3ai7ZRpyPDnJip554ZQe2bHZEKREgnQ1dWmj 2/HQ== X-Forwarded-Encrypted: i=1; AJvYcCVJ6bckXnsQADTB+NUKA8dHRbwapgn4pdZ9rIYCCG+9t7fq1DrjiLGxR8zoFP67nzSQvN+N1jL0YOhuz4y2rRlaGMgLg2kpNH72VEFq X-Gm-Message-State: AOJu0Yyp5I8UcqqownRhRSax+yNQwY90QR9oTk9cVywDO12SY6U13lQT DDRPYHevbwx3Ly395tR2LizxCSblwJmtlfGbt/6wc224SUZIlHx8RnpYJjO4SlveviNNzVkl30e mKbeDXuE5el5zyd0uPZJPEuxxGu4= X-Google-Smtp-Source: AGHT+IGJWcRHxaP4ST0KrRCAYbHyLNFe8gqyWccCGu5TQuuXKOJBenZ86ZKYoD2CLJ0UE3I0BVd9fnRjEEzKssui++o= X-Received: by 2002:a05:6820:80d:b0:5b9:8d0a:1568 with SMTP id 006d021491bc7-5b98d0a1a7dmr9384645eaf.2.1716903799653; Tue, 28 May 2024 06:43:19 -0700 (PDT) MIME-Version: 1.0 References: <580159f0fad7be030ad8632e49d1cb01e8d38acc.camel@cybertec.at> <2D6F40AA-1385-48AA-9F9F-DA8AA2BF30BC@purefiction.net> In-Reply-To: <2D6F40AA-1385-48AA-9F9F-DA8AA2BF30BC@purefiction.net> From: "David G. Johnston" Date: Tue, 28 May 2024 07:43:07 -0600 Message-ID: Subject: Re: Use of inefficient index in the presence of dead tuples To: Alexander Staubo Cc: Laurenz Albe , pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="000000000000178e44061983d187" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000178e44061983d187 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, May 28, 2024, 07:21 Alexander Staubo wrote: > > > I did explore a solution which is my =E2=80=9Cplan B=E2=80=9D =E2=80=94 a= dding a =E2=80=9Cdone=E2=80=9D column, > then using =E2=80=9CUPDATE =E2=80=A6 SET done =3D true=E2=80=9D rather th= an deleting the rows. This > causes dead tuples, of course, but then adding a new index with a =E2=80= =9C=E2=80=A6 WHERE > NOT done=E2=80=9D filter fixes the problem by forcing the query to use th= e right > index. However, with this solution, rows will still have to be deleted > *sometime*, so this just delays the problem. But it would allow a =E2=80= =9Cbatch > cleanup=E2=80=9D: =E2=80=9CDELETE =E2=80=A6 WHERE done; VACUUM=E2=80=9D i= n one fell swoop. > If you incorporate partitions into this, the final removal of the soft deleted rows becomes and truncate or a drop instead of a delete. David J. --000000000000178e44061983d187 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Tue, May 28, 2024, 07:21 Alexander Staubo <alex@purefiction.net> wrote:


I did explore a solution which is my =E2=80=9Cplan B=E2=80=9D =E2=80=94 add= ing a =E2=80=9Cdone=E2=80=9D column, then using =E2=80=9CUPDATE =E2=80=A6 S= ET done =3D true=E2=80=9D rather than deleting the rows. This causes dead t= uples, of course, but then adding a new index with a =E2=80=9C=E2=80=A6 WHE= RE NOT done=E2=80=9D filter fixes the problem by forcing the query to use t= he right index. However, with this solution, rows will still have to be del= eted *sometime*, so this just delays the problem. But it would allow a =E2= =80=9Cbatch cleanup=E2=80=9D: =E2=80=9CDELETE =E2=80=A6 WHERE done; VACUUM= =E2=80=9D in one fell swoop.
=
If you incorporate partitions into this, the fi= nal removal of the soft deleted rows becomes and truncate or a drop instead= of a delete.

David J.


--000000000000178e44061983d187--