From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Pavlos Kallis <pkallis@yourhero.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query in table where many rows were deleted. VACUUM FULL fixes it
Date: Tue, 30 Jan 2024 16:37:09 +0100
Message-ID: <40701055b4405ad3bf64467517401c8a44e41973.camel@cybertec.at> (raw)
In-Reply-To: <CABzFZgHm0=9ibE-6-85+Fy+BxcTLDOob4VrzJ0+eOqQXSze7gw@mail.gmail.com>
References: <CABzFZgHm0=9ibE-6-85+Fy+BxcTLDOob4VrzJ0+eOqQXSze7gw@mail.gmail.com>
On Tue, 2024-01-30 at 11:40 +0200, Pavlos Kallis wrote:
> I have the following table:
>
> CREATE TABLE IF NOT EXISTS public.shortened_url
> (
> id character varying(12) COLLATE pg_catalog."default" NOT NULL,
> created_at timestamp without time zone,
> expires_at timestamp without time zone,
> url text COLLATE pg_catalog."default" NOT NULL,
> CONSTRAINT shortened_url_pkey PRIMARY KEY (id)
> )
>
> The table contains only the following index on PRIMARY KEY:
>
> CREATE UNIQUE INDEX IF NOT EXISTS shortened_url_pkey
> ON public.shortened_url USING btree
> (id COLLATE pg_catalog."default" ASC NULLS LAST)
> TABLESPACE pg_default;
>
> This table has approximately 5 million rows of expired URLs (expires_at < now()), and 5 thousand rows of non-expired URLs (expires_at > now())
>
> I deleted all expired URLs with this query:
>
> DELETE FROM shortened_url WHERE expires_at < now().
>
> Then, I tried to query the table for expired URLs:
>
> SELECT * FROM shortened_url WHERE expires_at < now();
>
> This query was very slow. It took around 1-2 minutes to run, while it had to fetch only 5000 rows (the non-expired URLs, since the other ones were deleted).
>
> After that, I tried to run VACUUM ANALYZE and REINDEX to the table.
> The query was still slow.
>
> Finally, I ran VACUUM FULL and re-executed the query. Only then, it started running fast (1-2 seconds).
>
> Do you have observed a similar behavior with VACUUM ANALYZE / VACUUM FULL and why this can happen?
> Is this because data is compacted after VACUUM FULL and sequential disk reads are faster?
> Shouldn't VACUUM ANALYZE reclaim the disk space and make the query run fast?
> Is this because RDS might do some magic? Is it something I am missing?
There are too many unknowns here. Please enable "track_io_timing" and send us
the output of EXPLAIN (ANALYZE, BUFFERS) for the slow statements.
One theory could be that there was a long running transaction or something else
that prevented VACUUM from cleaning up. For that, the output of
"VACUUM (VERBOSE) shortened_url" would be interesting.
> Additional details
> PostgreSQL version: 14.7 on db.t3.micro RDS
> PG configuration: Default of RDS
We can only speak about real PostgreSQL...
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, pkallis@yourhero.com
Subject: Re: Slow query in table where many rows were deleted. VACUUM FULL fixes it
In-Reply-To: <40701055b4405ad3bf64467517401c8a44e41973.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox