From: Tom Lane <tgl@sss.pgh.pa.us>
To: Les <nagylzs@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Slow query, possibly not using index
Date: Sun, 27 Aug 2023 09:27:05 -0400
Message-ID: <347536.1693142825@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKXe9UAH=hBXDK22KM5HW9FbSkOgBgjvtxwOwGfGoLbCeg-6CQ@mail.gmail.com>
References: <CAKXe9UAH=hBXDK22KM5HW9FbSkOgBgjvtxwOwGfGoLbCeg-6CQ@mail.gmail.com>
Les <nagylzs@gmail.com> writes:
> If I try to select a single unused block this way:
> explain analyze select id from media.block b where nrefs =0 limit 1
> then it runs for more than 10 minutes (I'm not sure how long, I cancelled
> the query after 10 minutes).
Are you sure it isn't blocked on a lock?
Another theory is that the index contains many thousands of references
to now-dead rows, and the query is vainly searching for a live entry.
Given that EXPLAIN thinks there are only about 2300 live entries,
and yet you say the index is 400MB, this seems pretty plausible.
Have you disabled autovacuum, or something like that? (REINDEX
could help here, at least till the index gets bloated again.)
You might think that even so, it shouldn't take that long ... but
indexes on UUID columns are a well known performance antipattern.
The index entry order is likely to have precisely zip to do with
the table's physical order, resulting in exceedingly-random access
to the table, which'll be horribly expensive when the table is so
much bigger than RAM. Can you replace the UUID column with a simple
serial (identity) column?
> I believe it is not actually using the index, because reading a single
> (random?) entry from an index should not run for >10 minutes.
You should believe what EXPLAIN tells you about the plan shape.
(Its rowcount estimates are only estimates, though.)
regards, tom lane
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: tgl@sss.pgh.pa.us, nagylzs@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Slow query, possibly not using index
In-Reply-To: <347536.1693142825@sss.pgh.pa.us>
* 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