Thread
-
PostgreSQL 15-->18 slowdown?
Israel Brewster <ijbrewster@alaska.edu> — 2026-06-26T17:58:30Z
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 -
Re: PostgreSQL 15-->18 slowdown?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-06-26T18:14:56Z
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. > --- > 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 >
-
Re: PostgreSQL 15-->18 slowdown?
Israel Brewster <ijbrewster@alaska.edu> — 2026-06-26T18:16:14Z
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 <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 >> -
Re: PostgreSQL 15-->18 slowdown?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-06-26T18:16:23Z
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. >> >> 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 >> > > >
-
Re: PostgreSQL 15-->18 slowdown?
Israel Brewster <ijbrewster@alaska.edu> — 2026-06-26T18:19:06Z
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 > > >> --- >> 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 >
-
Re: PostgreSQL 15-->18 slowdown?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-06-26T18:40:09Z
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? >>> --- >>> 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 >> >
-
Re: PostgreSQL 15-->18 slowdown?
Tom Lane <tgl@sss.pgh.pa.us> — 2026-06-26T18:44:25Z
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
-
Re: PostgreSQL 15-->18 slowdown?
Israel Brewster <ijbrewster@alaska.edu> — 2026-06-26T18:55:00Z
> 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 > > >>>> --- >>>> 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 >>> > -
Re: PostgreSQL 15-->18 slowdown?
Israel Brewster <ijbrewster@alaska.edu> — 2026-06-26T18:58:09Z
> 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 > > regards, tom lane
-
Re: PostgreSQL 15-->18 slowdown?
Israel Brewster <ijbrewster@alaska.edu> — 2026-06-26T19:35:55Z
> 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 > > --- > 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 >