Feedback-ID: i341740b3:Fastmail
Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.500.171.1.1\))
Subject: Re: Use of inefficient index in the presence of dead tuples
From: Alexander Staubo <alex@purefiction.net>
In-Reply-To: <580159f0fad7be030ad8632e49d1cb01e8d38acc.camel@cybertec.at>
Date: Tue, 28 May 2024 15:20:46 +0200
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <2D6F40AA-1385-48AA-9F9F-DA8AA2BF30BC@purefiction.net>
References: <DC43B9C3-7BCB-4671-A69E-B0061C710241@purefiction.net>
 <580159f0fad7be030ad8632e49d1cb01e8d38acc.camel@cybertec.at>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Archived-At: <https://www.postgresql.org/message-id/2D6F40AA-1385-48AA-9F9F-DA8AA2BF30BC%40purefiction.net>
Precedence: bulk

On 28 May 2024, at 13:02, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> ANALYZE considers only the live rows, so PostgreSQL knows that the =
query will
> return only few results.  So it chooses the smaller index rather than =
the one
> that matches the WHERE condition perfectly.
>=20
> Unfortunately, it has to wade through all the deleted rows, which is =
slow.

Sounds like the planner _should_ take the dead tuples into account. =
I=E2=80=99m surprised there are no parameters to tweak to make the =
planner understand that one index is more selective even though it is =
technically larger.

> But try to execute the query a second time, and it will be much =
faster.
> PostgreSQL marks the index entries as "dead" during the first =
execution, so the
> second execution won't have to look at the heap any more.

Of course. It=E2=80=99s still not _free_; it=E2=80=99s still trawling =
through many megabytes of dead data, and going through the shared buffer =
cache and therefore competing with other queries that need shared =
buffers.=20

> I understand your pain, but your use case is somewhat unusual.

I don=E2=80=99t think rapidly updated tables is an unusual use of =
Postgres, nor is the problem of long-running transaction preventing dead =
tuple vacuuming.

> What I would consider in your place is
> a) running an explicit VACUUM after you delete lots of rows or

The rows are deleted individually. It=E2=80=99s just that there are many =
transactions doing it concurrently.

> b) using partitioning to get rid of old data

Partitioning will generate dead tuples in the original partition when =
tuples are moved to the other partition, so I=E2=80=99m not sure how =
that would help?

I did explore a solution which is my =E2=80=9Cplan B=E2=80=9D =E2=80=94 =
adding a =E2=80=9Cdone=E2=80=9D column, then using =E2=80=9CUPDATE =E2=80=A6=
 SET done =3D true=E2=80=9D rather than 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 the 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 in one fell swoop.