Re: PostgreSQL 15-->18 slowdown?

> 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
>>> 
>