Request a Demo
Home   >   mailing lists

Re: PostgreSQL 15-->18 slowdown? - Mailing list pgsql-general

From Israel Brewster
Subject Re: PostgreSQL 15-->18 slowdown?
Date
Msg-id 7F4F161B-CD44-4686-A49B-C1C466A66951@alaska.edu
Whole thread
In response to PostgreSQL 15-->18 slowdown?  (Israel Brewster <ijbrewster@alaska.edu>)
List pgsql-general
Tree view
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

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           ruben.gmorais@gmail.com

On Fri, Jun 26, 2026, 18:58 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.

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


pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: PostgreSQL 15-->18 slowdown?
Next
From: Adrian Klaver
Date:
Subject: Re: PostgreSQL 15-->18 slowdown?