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 <[email protected]> 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 protected] <mailto:[email protected]>
> On Fri, Jun 26, 2026, 18:58 Israel Brewster <[email protected]
> <mailto:[email protected]>> 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
>>
