From: Israel Brewster <ijbrewster@alaska.edu>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_C92A34D1-FC18-444F-8303-8F7589B95552"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.600.51.1.1\))
Subject: Re: PostgreSQL 15-->18 slowdown?
Date: Fri, 26 Jun 2026 11:35:55 -0800
References: <4A6D9280-1409-42A8-9A1F-99E2A558E2BB@alaska.edu>
To: PostgreSQL General <pgsql-general@lists.postgresql.org>
In-Reply-To: <4A6D9280-1409-42A8-9A1F-99E2A558E2BB@alaska.edu>
Message-Id: <F3DC81FB-9D10-45E2-87E1-5C152BD304A4@alaska.edu>
Archived-At: 
 <https://www.postgresql.org/message-id/F3DC81FB-9D10-45E2-87E1-5C152BD304A4%40alaska.edu>
Precedence: bulk


--Apple-Mail=_C92A34D1-FC18-444F-8303-8F7589B95552
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

> On Jun 26, 2026, at 9:58=E2=80=AFAM, Israel Brewster =
<ijbrewster@alaska.edu> wrote:
>=20
> In postgreSQL 15, I had the below query that worked quickly. Now, I =
make no claims that the query is the best possible, or even a good =
query, but it DID work, and it did so quickly enough to be un-noticable =
when running.
> ...

Some additional information that may (or may not) be helpful: in the =
tilt_data table, there are only 15 distinct stations (out of a total of =
177 stations) across 407,032,436 rows

=E2=80=A6so the query planner is correct to think that if looking for a =
single station, it will get a large number of rows=E2=80=A6at least if =
you ignore the LIMIT 1.

I did manage to find a workaround: If I change the subquery on tilt_data =
to this:

        (stations.id =3D ANY (
            SELECT tilt_data.station=20
            FROM tilt_data=20
            INNER JOIN tilt_orientation ON tilt_data.station =3D =
tilt_orientation.station
            WHERE tilt_data.station =3D stations.id
        )) as has_tilt,

things work better - still not as fast as the original query with =
sequence scan disabled, but fast enough to work with: =
https://explain.depesz.com/s/bki5

=E2=80=A6but not only does that feel ugly (to me at least), it=E2=80=99s =
still roughly 4x slower than the original query with sequence scans =
disabled=E2=80=A6 Good enough to get me back up and running, but not =
really a great solution, I don=E2=80=99t think. I might also try a =
lookup table (materialized view), but I don=E2=80=99t know how much =
better that would be, as I would have to refresh it whenever the data =
changes.=20
=E2=80=94
Israel Brewster
Software Engineer
Alaska Volcano Observatory=20
Geophysical Institute - UAF=20
2156 Koyukuk Drive=20
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145


>=20
> ---
> Israel Brewster
> Software Engineer
> Alaska Volcano Observatory=20
> Geophysical Institute - UAF=20
> 2156 Koyukuk Drive=20
> Fairbanks AK 99775-7320
> Work: 907-474-5172
> cell:  907-328-9145
>=20


--Apple-Mail=_C92A34D1-FC18-444F-8303-8F7589B95552
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html aria-label=3D"message body"><head><meta http-equiv=3D"content-type" =
content=3D"text/html; charset=3Dutf-8"></head><body =
style=3D"overflow-wrap: break-word; -webkit-nbsp-mode: space; =
line-break: after-white-space;"><div><blockquote type=3D"cite"><div>On =
Jun 26, 2026, at 9:58=E2=80=AFAM, Israel Brewster =
&lt;ijbrewster@alaska.edu&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div><meta http-equiv=3D"content-type"=
 content=3D"text/html; charset=3Dus-ascii"><div style=3D"overflow-wrap: =
break-word; -webkit-nbsp-mode: space; line-break: after-white-space;">In =
postgreSQL 15, I had the below query that worked quickly. Now, I make no =
claims that the query is the best possible, or even a good query, but it =
DID work, and it did so quickly enough to be un-noticable when =
running.<div>...</div></div></div></blockquote><div><br></div><div>Some =
additional information that may (or may not) be helpful: in the =
tilt_data table, there are only 15 distinct stations (out of a total of =
177 stations) across&nbsp;407,032,436 =
rows</div><div><div><br></div><div>=E2=80=A6so the query planner is =
correct to think that if looking for a single station, it will get a =
large number of rows=E2=80=A6at least if you ignore the LIMIT =
1.</div><div><br></div><div>I did manage to find a workaround: If I =
change the subquery on tilt_data to =
this:</div><div><br></div><div><div>&nbsp; &nbsp; &nbsp; &nbsp; =
(stations.id =3D ANY (</div><div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; =
&nbsp; SELECT tilt_data.station&nbsp;</div><div>&nbsp; &nbsp; &nbsp; =
&nbsp; &nbsp; &nbsp; FROM tilt_data&nbsp;</div><div>&nbsp; &nbsp; &nbsp; =
&nbsp; &nbsp; &nbsp; INNER JOIN tilt_orientation ON tilt_data.station =3D =
tilt_orientation.station</div><div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; =
&nbsp; WHERE tilt_data.station =3D stations.id</div><div>&nbsp; &nbsp; =
&nbsp; &nbsp; )) as has_tilt,</div></div><div><br></div><div>things work =
better - still not as fast as the original query with sequence scan =
disabled, but fast enough to work with:&nbsp;<a =
href=3D"https://explain.depesz.com/s/bki5">https://explain.depesz.com/s/bk=
i5</a></div><div><br></div><div>=E2=80=A6but not only does that feel =
ugly (to me at least), it=E2=80=99s still roughly 4x slower than the =
original query with sequence scans disabled=E2=80=A6 Good enough to get =
me back up and running, but not really a great solution, I don=E2=80=99t =
think. I might also try a lookup table (materialized view), but I =
don=E2=80=99t know how much better that would be, as I would have to =
refresh it whenever the data =
changes.&nbsp;</div></div><div>=E2=80=94</div><div>Israel =
Brewster<br>Software Engineer<br>Alaska Volcano =
Observatory&nbsp;<br>Geophysical Institute - UAF&nbsp;<br>2156 Koyukuk =
Drive&nbsp;<br>Fairbanks AK 99775-7320</div><div>Work: =
907-474-5172<br>cell: =
&nbsp;907-328-9145</div><div><br></div><div><br></div><div><br></div><bloc=
kquote type=3D"cite"><div><div style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: =
after-white-space;"><div><div><br></div><div>
<div dir=3D"auto" style=3D"word-wrap: break-word; -webkit-nbsp-mode: =
space; line-break: after-white-space;"><div style=3D"caret-color: rgb(0, =
0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; =
font-variant-caps: normal; font-weight: normal; letter-spacing: normal; =
text-align: start; text-indent: 0px; text-transform: none; white-space: =
normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; =
text-decoration: none;">---</div><div style=3D"caret-color: rgb(0, 0, =
0); font-family: Helvetica; font-size: 12px; font-style: normal; =
font-variant-caps: normal; font-weight: normal; letter-spacing: normal; =
text-align: start; text-indent: 0px; text-transform: none; white-space: =
normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; =
text-decoration: none;">Israel Brewster<br>Software Engineer<br>Alaska =
Volcano Observatory&nbsp;<br>Geophysical Institute - UAF&nbsp;<br>2156 =
Koyukuk Drive&nbsp;<br>Fairbanks AK 99775-7320</div><div =
style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: =
12px; font-style: normal; font-variant-caps: normal; font-weight: =
normal; letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none;">Work: =
907-474-5172<br>cell: &nbsp;907-328-9145</div></div>
</div>
<br></div></div></div></blockquote></div><br></body></html>=

--Apple-Mail=_C92A34D1-FC18-444F-8303-8F7589B95552--