On Wed, 2024-05-29 at 14:36 +0200, Alexander Staubo wrote:
> > On 29 May 2024, at 02:53, Tom Lane wrote:
> > I'm unpersuaded by the idea that ANALYZE should count dead tuples.
> > Since those are going to go away pretty soon, we would risk
> > estimating on the basis of no-longer-relevant stats an
> On 29 May 2024, at 02:53, Tom Lane wrote:
>
> Alexander Staubo writes:
>> (2) Set up schema. It's important to create the index before insertion, in
>> order to provoke a
>> situation where the indexes have dead tuples:
>> ...
>> (4) Then ensure all tuples are dead except one:
>
>>DELETE
On Wed, 29 May 2024 at 12:53, Tom Lane wrote:
> It would be interesting to see a non-artificial example that took
> into account when the last auto-vacuum and auto-analyze really
> happened, so we could see if there's any less-fragile way of
> dealing with this situation.
I think we need to find
Alexander Staubo writes:
> (2) Set up schema. It's important to create the index before insertion, in
> order to provoke a
> situation where the indexes have dead tuples:
> ...
> (4) Then ensure all tuples are dead except one:
> DELETE FROM outbox_batches;
> INSERT INTO outbox_batches (r
On Tue, May 28, 2024, 07:21 Alexander Staubo wrote:
>
>
> I did explore a solution which is my “plan B” — adding a “done” column,
> then using “UPDATE … SET done = true” rather than deleting the rows. This
> causes dead tuples, of course, but then adding a new index with a “… WHERE
> NOT done” fi
On 28 May 2024, at 13:02, Laurenz Albe 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.
>
> Unfortunately, it has to wade through all
On Tue, 2024-05-28 at 10:00 +0200, Alexander Staubo wrote:
> I am encountering an odd problem where Postgres will use the wrong index,
> particularly if the table
> has some dead tuples. The database affected is running 12.6, but I can also
> reproduce with 16.3.
>
> To reproduce:
> [create a ta
I am encountering an odd problem where Postgres will use the wrong index,
particularly if the table
has some dead tuples. The database affected is running 12.6, but I can also
reproduce with 16.3.
To reproduce:
(1) Disable autovacuum. This is just so we can induce a scenario where there
are lo
