public inbox for [email protected]
help / color / mirror / Atom feedPostgreSQL 15-->18 slowdown?
10+ messages / 3 participants
[nested] [flat]
* PostgreSQL 15-->18 slowdown?
@ 2026-06-26 17:58 Israel Brewster <[email protected]>
2026-06-26 18:14 ` Re: PostgreSQL 15-->18 slowdown? Adrian Klaver <[email protected]>
2026-06-26 18:16 ` Re: PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
2026-06-26 19:35 ` Re: PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
0 siblings, 3 replies; 10+ messages in thread
From: Israel Brewster @ 2026-06-26 17:58 UTC (permalink / raw)
To: PostgreSQL General <[email protected]>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL 15-->18 slowdown?
2026-06-26 17:58 PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
@ 2026-06-26 18:14 ` Adrian Klaver <[email protected]>
2026-06-26 18:16 ` Re: PostgreSQL 15-->18 slowdown? Adrian Klaver <[email protected]>
2026-06-26 18:19 ` Re: PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
2 siblings, 2 replies; 10+ messages in thread
From: Adrian Klaver @ 2026-06-26 18:14 UTC (permalink / raw)
To: Israel Brewster <[email protected]>; PostgreSQL General <[email protected]>
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL 15-->18 slowdown?
2026-06-26 17:58 PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
2026-06-26 18:14 ` Re: PostgreSQL 15-->18 slowdown? Adrian Klaver <[email protected]>
@ 2026-06-26 18:16 ` Adrian Klaver <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Adrian Klaver @ 2026-06-26 18:16 UTC (permalink / raw)
To: Israel Brewster <[email protected]>; PostgreSQL General <[email protected]>
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
>>
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL 15-->18 slowdown?
2026-06-26 17:58 PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
2026-06-26 18:14 ` Re: PostgreSQL 15-->18 slowdown? Adrian Klaver <[email protected]>
@ 2026-06-26 18:19 ` Israel Brewster <[email protected]>
2026-06-26 18:40 ` Re: PostgreSQL 15-->18 slowdown? Adrian Klaver <[email protected]>
1 sibling, 1 reply; 10+ messages in thread
From: Israel Brewster @ 2026-06-26 18:19 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: PostgreSQL General <[email protected]>
On Jun 26, 2026, at 10:14 AM, Adrian Klaver <[email protected]> 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL 15-->18 slowdown?
2026-06-26 17:58 PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
2026-06-26 18:14 ` Re: PostgreSQL 15-->18 slowdown? Adrian Klaver <[email protected]>
2026-06-26 18:19 ` Re: PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
@ 2026-06-26 18:40 ` Adrian Klaver <[email protected]>
2026-06-26 18:55 ` Re: PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2026-06-26 18:40 UTC (permalink / raw)
To: Israel Brewster <[email protected]>; +Cc: PostgreSQL General <[email protected]>
On 6/26/26 11:19 AM, Israel Brewster wrote:
> On Jun 26, 2026, at 10:14 AM, Adrian Klaver <[email protected]>
> 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
>>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL 15-->18 slowdown?
2026-06-26 17:58 PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
2026-06-26 18:14 ` Re: PostgreSQL 15-->18 slowdown? Adrian Klaver <[email protected]>
2026-06-26 18:19 ` Re: PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
2026-06-26 18:40 ` Re: PostgreSQL 15-->18 slowdown? Adrian Klaver <[email protected]>
@ 2026-06-26 18:55 ` Israel Brewster <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Israel Brewster @ 2026-06-26 18:55 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: PostgreSQL General <[email protected]>
> On Jun 26, 2026, at 10:40 AM, Adrian Klaver <[email protected]> wrote:
>
>
>
> On 6/26/26 11:19 AM, Israel Brewster wrote:
>> On Jun 26, 2026, at 10:14 AM, Adrian Klaver <[email protected]> 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
>>>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL 15-->18 slowdown?
2026-06-26 17:58 PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
@ 2026-06-26 18:16 ` Israel Brewster <[email protected]>
2026-06-26 18:44 ` Re: PostgreSQL 15-->18 slowdown? Tom Lane <[email protected]>
2 siblings, 1 reply; 10+ messages in thread
From: Israel Brewster @ 2026-06-26 18:16 UTC (permalink / raw)
To: Ruben Morais <[email protected]>; +Cc: PostgreSQL General <[email protected]>
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...;
>> Fairbanks AK 99775-7320 <https://www.google.com/maps/search/2156+Koyukuk+Drive+Fairbanks+AK+99775-7320?entry=gmail&source...;
>> Work: 907-474-5172
>> cell: 907-328-9145
>>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL 15-->18 slowdown?
2026-06-26 17:58 PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
2026-06-26 18:16 ` Re: PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
@ 2026-06-26 18:44 ` Tom Lane <[email protected]>
2026-06-26 18:58 ` Re: PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2026-06-26 18:44 UTC (permalink / raw)
To: Israel Brewster <[email protected]>; +Cc: Ruben Morais <[email protected]>; PostgreSQL General <[email protected]>
Israel Brewster <[email protected]> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL 15-->18 slowdown?
2026-06-26 17:58 PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
2026-06-26 18:16 ` Re: PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
2026-06-26 18:44 ` Re: PostgreSQL 15-->18 slowdown? Tom Lane <[email protected]>
@ 2026-06-26 18:58 ` Israel Brewster <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Israel Brewster @ 2026-06-26 18:58 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Ruben Morais <[email protected]>; PostgreSQL General <[email protected]>
> On Jun 26, 2026, at 10:44 AM, Tom Lane <[email protected]> wrote:
>
> Israel Brewster <[email protected]> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL 15-->18 slowdown?
2026-06-26 17:58 PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
@ 2026-06-26 19:35 ` Israel Brewster <[email protected]>
2 siblings, 0 replies; 10+ messages in thread
From: Israel Brewster @ 2026-06-26 19:35 UTC (permalink / raw)
To: PostgreSQL General <[email protected]>
> On Jun 26, 2026, at 9:58 AM, Israel Brewster <[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.
> ...
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-06-26 19:35 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-26 17:58 PostgreSQL 15-->18 slowdown? Israel Brewster <[email protected]>
2026-06-26 18:14 ` Adrian Klaver <[email protected]>
2026-06-26 18:16 ` Adrian Klaver <[email protected]>
2026-06-26 18:19 ` Israel Brewster <[email protected]>
2026-06-26 18:40 ` Adrian Klaver <[email protected]>
2026-06-26 18:55 ` Israel Brewster <[email protected]>
2026-06-26 18:16 ` Israel Brewster <[email protected]>
2026-06-26 18:44 ` Tom Lane <[email protected]>
2026-06-26 18:58 ` Israel Brewster <[email protected]>
2026-06-26 19:35 ` Israel Brewster <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox