PostgreSQL 15-->18 slowdown?

Started by Israel Brewster5 months ago10 messagesgeneral
Jump to latest
#1Israel Brewster
ijbrewster@alaska.edu

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.

Then I upgrade to PostgreSQL 18 - and now the query never completes (as in, I get a command timeout after at least half an hour before I get a result). Looking at the EXPLAIN (https://explain.depesz.com/s/llAQ) makes it pretty obvious why: we have a sequence scan on a large table inside a nested loop - and that sequence scan is apparently not short circuiting.

I tried the obvious: REINDEX database and VACUUM ANALYZE, but neither helped. I have my default_statistics_target set to 500 at the moment.

Then I tried SET enable_seqscan = off; Lo and behold, the query ran in only 123.888 ms (fun number :-D ) - https://explain.depesz.com/s/K2K9

What did I do wrong here? Thanks!

The query in question:

SELECT
stations.id,
stations.name as name,
latitude::float as lat,
longitude::float as lon,
elevation::float alt,
type,
coalesce(sites.name, 'Unknown') as site,
sites.id as siteid,
coalesce((SELECT true
FROM tilt_data
INNER JOIN tilt_orientation
ON tilt_data.station=tilt_orientation.station
WHERE tilt_data.station=stations.id
LIMIT 1),
false) as has_tilt,
array_to_json(volcano) as stationvolcs,
coalesce(
(SELECT array_agg(s.volcano_name ORDER BY t.ord)
FROM volcano s
JOIN unnest( volcano ) WITH ORDINALITY t(id,ord)
ON (t.id=s.volcano_id)
), '{}' ) volcnames
FROM stations
LEFT JOIN sites ON stations.siteref=sites.id
ORDER BY site, name

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

#2Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Israel Brewster (#1)
Re: PostgreSQL 15-->18 slowdown?

On 6/26/26 10:58 AM, Israel Brewster wrote:

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.

Then I upgrade to PostgreSQL 18 - and now the query never completes (as
in, I get a command timeout after at least half an hour before I get a
result). Looking at the EXPLAIN (https://explain.depesz.com/s/llAQ
<https://explain.depesz.com/s/llAQ>) makes it pretty obvious why: we
have a sequence scan on a large table inside a nested loop - and that
sequence scan is apparently not short circuiting.

The link provided shows no times or rows, did you pick the correct one?

I tried the obvious: REINDEX database and VACUUM ANALYZE, but neither
helped. I have my default_statistics_target set to 500 at the moment.

Then I tried SET enable_seqscan = off; Lo and behold, the query ran in
only 123.888 ms (fun number :-D ) - https://explain.depesz.com/s/K2K9
<https://explain.depesz.com/s/K2K9>

This one does not show the actual query.

Show quoted text

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

#3Israel Brewster
ijbrewster@alaska.edu
In reply to: Israel Brewster (#1)
Re: PostgreSQL 15-->18 slowdown?

Thanks for the suggestion. Unfortunately, jit off didn’t help (see query plan below). And I do have indexes - as I mentioned, when I forced postgresql to *use* the indexes by setting enable_seqscan = off;, the query ran in ~125ms - back to the fast speeds I was used to in PostgreSQL 15.

I’ve tried a couple of re-writes (LATERAL JOIN and using EXISTS rather than LIMIT 1), but they don’t seem to help.

QUERY PLAN
------------------------------------------------------------------------------------------------------
Sort (cost=182445.18..182445.63 rows=177 width=152)
Sort Key: (COALESCE(sites.name, 'Unknown'::text)), stations.name
-> Hash Left Join (cost=1.29..182438.57 rows=177 width=152)
Hash Cond: (stations.siteref = sites.id)
-> Seq Scan on stations (cost=0.00..6.77 rows=177 width=87)
-> Hash (cost=1.13..1.13 rows=13 width=11)
-> Seq Scan on sites (cost=0.00..1.13 rows=13 width=11)
SubPlan 1
-> Limit (cost=0.00..0.48 rows=1 width=1)
-> Nested Loop (cost=0.00..13833793.79 rows=29105774 width=1)
-> Seq Scan on tilt_data (cost=0.00..13469970.40 rows=29105774 width=16)
Filter: (station = stations.id)
-> Materialize (cost=0.00..1.22 rows=1 width=16)
-> Seq Scan on tilt_orientation (cost=0.00..1.21 rows=1 width=16)
Filter: (station = stations.id)
SubPlan 2
-> Aggregate (cost=1030.18..1030.19 rows=1 width=32)
-> Sort (cost=1029.93..1030.05 rows=50 width=126)
Sort Key: t.ord
-> Merge Join (cost=25.27..1028.52 rows=50 width=126)
Merge Cond: ((s.volcano_id)::text = (t.id)::text)
-> Foreign Scan on volcano s (cost=25.00..1025.00 rows=1000 width=156)
Remote server startup cost: 25
-> Sort (cost=0.27..0.29 rows=10 width=40)
Sort Key: t.id
-> Function Scan on unnest t (cost=0.00..0.10 rows=10 width=40)
(26 rows)

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

Show quoted text

On Jun 26, 2026, at 10:08 AM, Ruben Morais <ruben.gmorais@gmail.com> wrote:

Hi,

Saw your email, and checked you have JIT enable.
Try to disable and run the query again

SET jit = off;

Or test the explain with jit off, to check the plan
EXPLAIN (jit off) SELECT ...;

Jit sometime had issues in plans in postgres.
Other solution is create indexes or rewrite the code.

Regards
Rúben Morais

(+351) 965775713 <https://pt.linkedin.com/pub/r%C3%BAben-morais/21/b44/b99> ruben.gmorais@gmail.com <mailto:ruben.gmorais@gmail.com>
On Fri, Jun 26, 2026, 18:58 Israel Brewster <ijbrewster@alaska.edu <mailto:ijbrewster@alaska.edu>> wrote:

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.

Then I upgrade to PostgreSQL 18 - and now the query never completes (as in, I get a command timeout after at least half an hour before I get a result). Looking at the EXPLAIN (https://explain.depesz.com/s/llAQ) makes it pretty obvious why: we have a sequence scan on a large table inside a nested loop - and that sequence scan is apparently not short circuiting.

I tried the obvious: REINDEX database and VACUUM ANALYZE, but neither helped. I have my default_statistics_target set to 500 at the moment.

Then I tried SET enable_seqscan = off; Lo and behold, the query ran in only 123.888 ms (fun number :-D ) - https://explain.depesz.com/s/K2K9

What did I do wrong here? Thanks!

The query in question:

SELECT
stations.id <http://stations.id/>,
stations.name <http://stations.name/> as name,
latitude::float as lat,
longitude::float as lon,
elevation::float alt,
type,
coalesce(sites.name <http://sites.name/>, 'Unknown') as site,
sites.id <http://sites.id/> as siteid,
coalesce((SELECT true
FROM tilt_data
INNER JOIN tilt_orientation
ON tilt_data.station=tilt_orientation.station
WHERE tilt_data.station=stations.id <http://stations.id/>
LIMIT 1),
false) as has_tilt,
array_to_json(volcano) as stationvolcs,
coalesce(
(SELECT array_agg(s.volcano_name ORDER BY t.ord)
FROM volcano s
JOIN unnest( volcano ) WITH ORDINALITY t(id,ord)
ON (t.id <http://t.id/>=s.volcano_id)
), '{}' ) volcnames
FROM stations
LEFT JOIN sites ON stations.siteref=sites.id <http://sites.id/>
ORDER BY site, name

---
Israel Brewster
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive <https://www.google.com/maps/search/2156+Koyukuk+Drive+Fairbanks+AK+99775-7320?entry=gmail&source=g>
Fairbanks AK 99775-7320 <https://www.google.com/maps/search/2156+Koyukuk+Drive+Fairbanks+AK+99775-7320?entry=gmail&source=g>
Work: 907-474-5172
cell: 907-328-9145

#4Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Adrian Klaver (#2)
Re: PostgreSQL 15-->18 slowdown?

On 6/26/26 11:14 AM, Adrian Klaver wrote:

On 6/26/26 10:58 AM, Israel Brewster wrote:

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.

Then I upgrade to PostgreSQL 18 - and now the query never completes
(as in, I get a command timeout after at least half an hour before I
get a result). Looking at the EXPLAIN (https://explain.depesz.com/s/
llAQ <https://explain.depesz.com/s/llAQ>) makes it pretty obvious why:
we have a sequence scan on a large table inside a nested loop - and
that sequence scan is apparently not short circuiting.

The link provided shows no times or rows, did you pick the correct one?

Yes you did: "I get a command timeout after at least half an hour"

Was not thinking.

Show quoted text

I tried the obvious: REINDEX database and VACUUM ANALYZE, but neither
helped. I have my default_statistics_target set to 500 at the moment.

Then I tried SET enable_seqscan = off; Lo and behold, the query ran in
only 123.888 ms (fun number :-D ) - https://explain.depesz.com/s/K2K9
<https://explain.depesz.com/s/K2K9>

This one does not show the actual query.

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

#5Israel Brewster
ijbrewster@alaska.edu
In reply to: Adrian Klaver (#2)
Re: PostgreSQL 15-->18 slowdown?

On Jun 26, 2026, at 10:14 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 6/26/26 10:58 AM, Israel Brewster wrote:

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.
Then I upgrade to PostgreSQL 18 - and now the query never completes (as in, I get a command timeout after at least half an hour before I get a result). Looking at the EXPLAIN (https://explain.depesz.com/s/llAQ <https://explain.depesz.com/s/llAQ>) makes it pretty obvious why: we have a sequence scan on a large table inside a nested loop - and that sequence scan is apparently not short circuiting.

The link provided shows no times or rows, did you pick the correct one?

I tried the obvious: REINDEX database and VACUUM ANALYZE, but neither helped. I have my default_statistics_target set to 500 at the moment.
Then I tried SET enable_seqscan = off; Lo and behold, the query ran in only 123.888 ms (fun number :-D ) - https://explain.depesz.com/s/K2K9 <https://explain.depesz.com/s/K2K9>

This one does not show the actual query.

Right, sorry. It’s the same query as in the first one though. Only difference is sequence scan off.

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

Show quoted text

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

#6Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Israel Brewster (#5)
Re: PostgreSQL 15-->18 slowdown?

On 6/26/26 11:19 AM, Israel Brewster wrote:

On Jun 26, 2026, at 10:14 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

This one does not show the actual query.

Right, sorry. It’s the same query as in the first one though. Only
difference is sequence scan off.

What are the definitions for orientation_station_idx and
new_data_station_idx?

What are the data types for the columns they are pointing at?

Show quoted text

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

#7Tom Lane
tgl@sss.pgh.pa.us
In reply to: Israel Brewster (#3)
Re: PostgreSQL 15-->18 slowdown?

Israel Brewster <ijbrewster@alaska.edu> writes:

SubPlan 1
-> Limit (cost=0.00..0.48 rows=1 width=1)
-> Nested Loop (cost=0.00..13833793.79 rows=29105774 width=1)
-> Seq Scan on tilt_data (cost=0.00..13469970.40 rows=29105774 width=16)
Filter: (station = stations.id)
-> Materialize (cost=0.00..1.22 rows=1 width=16)
-> Seq Scan on tilt_orientation (cost=0.00..1.21 rows=1 width=16)
Filter: (station = stations.id)

Can you show us the EXPLAIN output for this part of the plan in v15?

regards, tom lane

#8Israel Brewster
ijbrewster@alaska.edu
In reply to: Adrian Klaver (#6)
Re: PostgreSQL 15-->18 slowdown?

On Jun 26, 2026, at 10:40 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 6/26/26 11:19 AM, Israel Brewster wrote:

On Jun 26, 2026, at 10:14 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

This one does not show the actual query.

Right, sorry. It’s the same query as in the first one though. Only difference is sequence scan off.

What are the definitions for orientation_station_idx and new_data_station_idx?

What are the data types for the columns they are pointing at?

So for orientation_station_idx, that’s on the tilt_orientation table:

geodesy=# \d tilt_orientation
Table "public.tilt_orientation"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+--------------------
id | uuid | | not null | uuid_generate_v1()
station | uuid | | not null |
seton | timestamp with time zone | | |
x_orientation | numeric | | |
y_orientation | numeric | | |
Indexes:
"tilt_orientation_pkey" PRIMARY KEY, btree (id)
"date_idx" btree (seton)
"orientation_station_idx" btree (station) INCLUDE (y_orientation)
"sta_time_w_ynotnull_idx" UNIQUE, btree (station, seton) INCLUDE (y_orientation) WHERE y_orientation IS NOT NULL
"station_time_idx" UNIQUE, btree (station, seton)
Foreign-key constraints:
"tilt_orientation_station_fkey" FOREIGN KEY (station) REFERENCES stations(id)

geodesy=#

so it’s a UUID (yeah, I went through a phase where I was using UUID’s as primary keys…in retrospect, probably not the best idea...)

And for new_data_station_idx, that’s on the tilt_data table:

geodesy=# \d tilt_data
Table "public.tilt_data"
Column | Type | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+--------------------
id | uuid | | not null | uuid_generate_v1()
station | uuid | | not null |
read_time | timestamp with time zone | | not null |
tilt_x | numeric | | |
tilt_y | numeric | | |
temperature | numeric | | |
rot_x | numeric(6,3) | | |
rot_y | numeric(6,3) | | |
Indexes:
"new_tilt_pkey" PRIMARY KEY, btree (id)
"new_data_station_idx" btree (station)
"new_data_station_time_idx" UNIQUE, btree (station, read_time)
Foreign-key constraints:
"new_tilt_data_station_fkey" FOREIGN KEY (station) REFERENCES stations(id)
Disabled user triggers:
new_rotate_tilt_data BEFORE INSERT OR UPDATE ON tilt_data FOR EACH ROW WHEN (new.tilt_x IS NOT NULL AND new.tilt_y IS NOT NULL) EXECUTE FUNCTION correct_rotation()
Tablespace: "shared_drive"

geodesy=#

…so again, a UUID. And note that tablespace “shared drive” is not actually a shared drive (anymore) - everything has been moved to a local NVMe drive.

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

Show quoted text

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

#9Israel Brewster
ijbrewster@alaska.edu
In reply to: Tom Lane (#7)
Re: PostgreSQL 15-->18 slowdown?

On Jun 26, 2026, at 10:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Israel Brewster <ijbrewster@alaska.edu> writes:

SubPlan 1
-> Limit (cost=0.00..0.48 rows=1 width=1)
-> Nested Loop (cost=0.00..13833793.79 rows=29105774 width=1)
-> Seq Scan on tilt_data (cost=0.00..13469970.40 rows=29105774 width=16)
Filter: (station = stations.id)
-> Materialize (cost=0.00..1.22 rows=1 width=16)
-> Seq Scan on tilt_orientation (cost=0.00..1.21 rows=1 width=16)
Filter: (station = stations.id)

Can you show us the EXPLAIN output for this part of the plan in v15?

I wish I could - I was wanting to see that myself. Unfortunately, I upgraded to 18 using —link, so by the time I discovered this issue, I had no way to go back to 15 (other than restoring from backups or the like), at least to the best of my knowledge.

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

Show quoted text

regards, tom lane

#10Israel Brewster
ijbrewster@alaska.edu
In reply to: Israel Brewster (#1)
Re: PostgreSQL 15-->18 slowdown?

On Jun 26, 2026, at 9:58 AM, Israel Brewster <ijbrewster@alaska.edu> wrote:

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

…so the query planner is correct to think that if looking for a single station, it will get a large number of rows…at 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 = ANY (
SELECT tilt_data.station
FROM tilt_data
INNER JOIN tilt_orientation ON tilt_data.station = tilt_orientation.station
WHERE tilt_data.station = 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

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

Show quoted text

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