Re: Slow query in table where many rows were deleted. VACUUM FULL fixes it

On Wed, 31 Jan 2024 at 09:09, Philip Semanchuk
<philip@americanefficient.com> wrote:
> So in your case those 5m rows that you deleted were probably still clogging up your table until you ran VACUUM FULL.

It seems more likely to me that the VACUUM removed the rows and just
left empty pages in the table.  Since there's no index on expires_at,
the only way to answer that query is to Seq Scan and Seq Scan will
need to process those empty pages.  While that processing is very fast
if the page's item pointers array is empty, it could still be slow if
the page needs to be read from disk. Laurenz's request for the explain
(analyze, buffers) output with track_io_timing on will help confirm
this.

If it is just reading empty pages that's causing this issue then
adding that missing index would improve the situation after running
just plain VACUUM each time there's a bulk delete.

David