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

> On Jun 26, 2026, at 10:08 AM, Ruben Morais <ruben(dot)gmorais(at)gmail(dot)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(dot)gmorais(at)gmail(dot)com <mailto:ruben(dot)gmorais(at)gmail(dot)com>
> On Fri, Jun 26, 2026, 18:58 Israel Brewster <ijbrewster(at)alaska(dot)edu <mailto:ijbrewster(at)alaska(dot)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
>>