MIME-Version: 1.0
References: <197E1EF0-BEDF-42C0-8508-56BC6E88AA35@gmail.com>
In-Reply-To: <197E1EF0-BEDF-42C0-8508-56BC6E88AA35@gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Fri, 8 Nov 2024 11:18:21 +1300
Message-ID: 
 <CAApHDvr8c4-unAVysqMgz0mWO=PZ_WAGprq-jwND5+C_OO8SYg@mail.gmail.com>
Subject: Re: Major performance degradation with joins in 15.8 or 15.7?
To: Ed Sabol <edwardjsabol@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvr8c4-unAVysqMgz0mWO%3DPZ_WAGprq-jwND5%2BC_OO8SYg%40mail.gmail.com>
Precedence: bulk

On Fri, 8 Nov 2024 at 10:54, Ed Sabol <edwardjsabol@gmail.com> wrote:
> The good news is that, after some research and experimentation, I was abl=
e to fix this performance degradation by setting random_page_cost =3D 2.0. =
We've always used the default values for seq_page_cost and random_page_cost=
 (1.0 and 4.0, respectively, I think?). A lot of the advice I found online =
suggested these *_page_cost settings are too high?

Often people lower random_page_cost when running SSDs. The default
value of 4.0 was determined before SSDs were a thing.

> Did upgrading PostgreSQL to 15.8 cause this performance degradation? Did =
the page costs calculation change in 15.8 (or 15.7)? The PostgreSQL version=
 is the only thing that has appreciably changed with this database this who=
le year.

It's impossible to say with the given information. You didn't mention
which version you upgraded from to start with.

We'd need to see EXPLAIN ANALYZE from before and after you changed
random_page_cost.

> Or do you think the size of the table just incrementally reached some thr=
eshold that resulted in the optimizer changing its plan based on the page c=
osts?

It's possible, but it's also impossible to know without having more informa=
tion.

> All of the columns are text fields and indexed. Unfortunately, I don't ha=
ve the "EXPLAIN (ANALYZE, BUFFERS)" output from before I changed the random=
_page_cost setting. It's no longer in my scollback buffer.

You can set random_page_cost for just the session you're connected to
and try it.  SET random_page_cost =3D <old value>; before running
EXPLAIN (ANALYZE, BUFFERS).

David