public inbox for [email protected]
help / color / mirror / Atom feedFrom: Israel Brewster <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: PostgreSQL General <[email protected]>
Subject: Re: PostgreSQL 15-->18 slowdown?
Date: Fri, 26 Jun 2026 10:55:00 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[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
>>>
>
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: PostgreSQL 15-->18 slowdown?
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox