Re: PostgreSQL 15-->18 slowdown?

> On Jun 26, 2026, at 10:40 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
>
>
> On 6/26/26 11:19 AM, Israel Brewster wrote:
>> On Jun 26, 2026, at 10:14 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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
>>>
>