Subject: PostgreSQL 15-->18 slowdown?

From: Israel Brewster <[email-masked]>
Date: Fri, 26 Jun 2026 09:58:30 -0800

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

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 26 Jun 2026 11:14:56 -0700

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.

---

From: Israel Brewster <[email-masked]>
Date: Fri, 26 Jun 2026 10:16:14 -0800

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

> On Jun 26, 2026, at 10:08 AM, Ruben Morais <[email-masked]> 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>       [email-masked] <mailto:[email-masked]>
> On Fri, Jun 26, 2026, 18:58 Israel Brewster <[email-masked] <mailto:[email-masked]>> wrote:
>>         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
>>                   WHERE tilt_data.station=stations.id <http://stations.id/>
>>                   LIMIT 1),
>>                 false) as has_tilt,
>>                 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
>> 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
>>

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 26 Jun 2026 11:16:23 -0700

On 6/26/26 11:14 AM, Adrian Klaver 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.
> 

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

Was not thinking.

---

From: Israel Brewster <[email-masked]>
Date: Fri, 26 Jun 2026 10:19:06 -0800

On Jun 26, 2026, at 10:14 AM, Adrian Klaver <[email-masked]> wrote:
>> 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.
> 
>> 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>
> 

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

---
Israel Brewster
Software Engineer

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 26 Jun 2026 11:40:09 -0700

> 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?

---

From: Tom Lane <[email-masked]>
Date: Fri, 26 Jun 2026 14:44:25 -0400

>                                    Filter: (station = stations.id)

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

			regards, tom lane

---

From: Israel Brewster <[email-masked]>
Date: Fri, 26 Jun 2026 10:55:00 -0800

> On Jun 26, 2026, at 10:40 AM, Adrian Klaver <[email-masked]> wrote:
> 
> 
> 
> What are the definitions for orientation_station_idx and new_data_station_idx?
> 

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

---

From: Israel Brewster <[email-masked]>
Date: Fri, 26 Jun 2026 10:58:09 -0800

> On Jun 26, 2026, at 10:44 AM, Tom Lane <[email-masked]> wrote:
> 

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

---

From: Israel Brewster <[email-masked]>
Date: Fri, 26 Jun 2026 11:35:55 -0800

> On Jun 26, 2026, at 9:58 AM, Israel Brewster <[email-masked]> wrote:
> 
> ...

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