Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wdBhu-003wjX-0f for pgsql-general@arkaria.postgresql.org; Fri, 26 Jun 2026 18:55:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wdBhs-00CU6D-27 for pgsql-general@arkaria.postgresql.org; Fri, 26 Jun 2026 18:55:16 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wdBhs-00CU5v-09 for pgsql-general@lists.postgresql.org; Fri, 26 Jun 2026 18:55:16 +0000 Received: from mail-pj1-x102c.google.com ([2607:f8b0:4864:20::102c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wdBhp-00000000NKI-2vTi for pgsql-general@lists.postgresql.org; Fri, 26 Jun 2026 18:55:14 +0000 Received: by mail-pj1-x102c.google.com with SMTP id 98e67ed59e1d1-37e18a1927aso68231a91.0 for ; Fri, 26 Jun 2026 11:55:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=alaska.edu; s=google; t=1782500112; x=1783104912; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:content-type :message-id:from:from:to:cc:subject:date:message-id:reply-to :content-type; bh=yjcdUl8Gavn74Y8vg9QFMnN01xvOWbg+B5Jsu0pM7vg=; b=RbwiO8VeReqMNK9osWf1sfYGwoNCKesoYha5zVyY8ZKDbzcz3TjRGWAcJouOVDUdqw AT4Rai95ov+Ze6DbVGPkkBnDEqSbg4G+Sj8iisjhjCg0vTy0JyDWXxg1Dsf88Opxusbl iWqOAl048HV2wWl5wmySxhO8Ep0zupjZiqh9IX6obgnQbGTV4/bK+ratlCHOr3c8IXsv aR6fFXASczpfytJUtP3YwrRElINfBYYW0+cxxy7uhfitiEkflT2Bg42uRTyZyvq/8I1Z fFrvhadyNFbpzRA5vmph0rCbBb06E83K2p3iG+dOQC1Wiu8K64E9QOagfJzxt5fne4SF mxHQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1782500112; x=1783104912; h=references:to:cc:in-reply-to:date:subject:mime-version:content-type :message-id:from:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to:content-type; bh=yjcdUl8Gavn74Y8vg9QFMnN01xvOWbg+B5Jsu0pM7vg=; b=g17S0pSsisK1dn2PcvaE42ZQraajoJ8Z5FdpT8ddqPgEqP+6rAbX3aN/X1RT2O9mF9 M+QnRr7+keDOS7p7nHw8m4FLlbUIAzvPymt7IKK3FIowcvlaliiRkHogDjUEKQWfdZKe QW8WRcsX8h/bD5dxa0+9RMN5XYgVCYSCCpKeW4z1n/vFEXIUt3au3FlKgWfM5eOtB6eE jVuk+vmx1KGUNv1KE1TVA8aIUvPut6yHJtHQw89Mln4U50DKwcMTgHFYGDpnhyp738Q5 YF0oVH1upyzrggLLRk36KQ24bIuN6SC33HdlKPXqsHwsGUr6ZbZ7viBk93WgSRcnbT4W 1OSQ== X-Gm-Message-State: AOJu0YzRTgFjj8RiLcSI8XrbcP7QLYIEPRXmlmVap8iHb66UMRrY2XWf Igfp1CShlABijeJ5nA304Ed0rc7rCommGhmyy/ytnzFLgu1vtqtMoC8FJttJrdJ+Ew== X-Gm-Gg: AfdE7cmEBo/iuWZ3QVr9CraCIdRl0e1kuzd2kOwEZRz5lRYSc4W2sGejTzCeAb4B09U 9nl9baUU/grj+89BeT2EexzbVAhrwulW0JBcbV5UMuNTe4H8ccISIyHRgsQj3no4prD1fpXyRBd B58Wp2EpjMKuAcdKT4SOg8iHSFvBzR4oPrC2kdNfYOhcGTwMm23JZSxD+Yrck0Bq30QoBCIhE0I 3YHYObzyqfsptRrQqbz+SqjEspPxPAOKaV/SJXP49m6aksve7UnxFhvkMkDE2Iw/TZ5PAv0qqBp 3oCy9swE2ZxEZXlPune5a6zJISkoqq2A3qK862pFq0EJZ3abKg2uXpkrsvG7AylrIgTtgaUeD+U XBmSN3RKCbN1dBi2NuaE8A0AVpCYTlsbjJ778oOUIl8Ssy8LKXbtQAZA91iwRSp9OJXbWLkO3RB 5FheuCUWvKUcY+VclLXt809CYlUaKxPBsrHUUpYn4w5Tf50OwDJSfVJ0tAaHwgZS9DKTLdiVeCO vGj2jkB X-Received: by 2002:a17:902:f552:b0:2c4:397:dd7a with SMTP id d9443c01a7336-2c7fc72b94dmr38474715ad.4.1782500112400; Fri, 26 Jun 2026 11:55:12 -0700 (PDT) Received: from smtpclient.apple (63-140-92-92-radius.dynamic.acsalaska.net. [63.140.92.92]) by smtp.gmail.com with ESMTPSA id d9443c01a7336-2c9878acad1sm6589765ad.42.2026.06.26.11.55.11 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 26 Jun 2026 11:55:11 -0700 (PDT) From: Israel Brewster Message-Id: <37720F53-8B8E-4CC3-ACCB-C13EFF9D0EDC@alaska.edu> Content-Type: multipart/alternative; boundary="Apple-Mail=_76EAAB3D-0BF5-450A-9BD2-BD4C6BCF5782" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.600.51.1.1\)) Subject: Re: PostgreSQL 15-->18 slowdown? Date: Fri, 26 Jun 2026 10:55:00 -0800 In-Reply-To: <13dd73fa-7cfc-417c-9b16-5c16142a2058@aklaver.com> Cc: PostgreSQL General To: Adrian Klaver References: <4A6D9280-1409-42A8-9A1F-99E2A558E2BB@alaska.edu> <2ce465f0-f7f3-4c6a-9f68-b53439416336@aklaver.com> <9FB992C2-0A1D-4BFE-8066-77F130471B9B@alaska.edu> <13dd73fa-7cfc-417c-9b16-5c16142a2058@aklaver.com> X-Mailer: Apple Mail (2.3864.600.51.1.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_76EAAB3D-0BF5-450A-9BD2-BD4C6BCF5782 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jun 26, 2026, at 10:40=E2=80=AFAM, Adrian Klaver = wrote: >=20 >=20 >=20 > On 6/26/26 11:19 AM, Israel Brewster wrote: >> On Jun 26, 2026, at 10:14=E2=80=AFAM, Adrian Klaver = wrote: >>>=20 >=20 >>> This one does not show the actual query. >> Right, sorry. It=E2=80=99s the same query as in the first one though. = Only difference is sequence scan off. >=20 > What are the definitions for orientation_station_idx and = new_data_station_idx? >=20 > What are the data types for the columns they are pointing at? So for orientation_station_idx, that=E2=80=99s on the tilt_orientation = table: geodesy=3D# \d tilt_orientation Table "public.tilt_orientation" Column | Type | Collation | Nullable | = Default =20 = ---------------+--------------------------+-----------+----------+--------= ------------ id | uuid | | not null | = uuid_generate_v1() station | uuid | | not null |=20 seton | timestamp with time zone | | |=20 x_orientation | numeric | | |=20 y_orientation | numeric | | |=20 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=3D#=20 so it=E2=80=99s a UUID (yeah, I went through a phase where I was using = UUID=E2=80=99s as primary keys=E2=80=A6in retrospect, probably not the = best idea...) And for new_data_station_idx, that=E2=80=99s on the tilt_data table: geodesy=3D# \d tilt_data Table "public.tilt_data" Column | Type | Collation | Nullable | = Default =20 = -------------+--------------------------+-----------+----------+----------= ---------- id | uuid | | not null | = uuid_generate_v1() station | uuid | | not null |=20 read_time | timestamp with time zone | | not null |=20 tilt_x | numeric | | |=20 tilt_y | numeric | | |=20 temperature | numeric | | |=20 rot_x | numeric(6,3) | | |=20 rot_y | numeric(6,3) | | |=20 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=3D#=20 =E2=80=A6so again, a UUID. And note that tablespace =E2=80=9Cshared = drive=E2=80=9D is not actually a shared drive (anymore) - everything has = been moved to a local NVMe drive. =E2=80=94 Israel Brewster Software Engineer Alaska Volcano Observatory=20 Geophysical Institute - UAF=20 2156 Koyukuk Drive=20 Fairbanks AK 99775-7320 Work: 907-474-5172 cell: 907-328-9145 >=20 >=20 >>>> --- >>>> 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 >>>=20 >=20 --Apple-Mail=_76EAAB3D-0BF5-450A-9BD2-BD4C6BCF5782 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
On = Jun 26, 2026, at 10:40=E2=80=AFAM, Adrian Klaver = <adrian.klaver@aklaver.com> wrote:



On 6/26/26 11:19 = AM, Israel Brewster wrote:
On Jun 26, 2026, = at 10:14=E2=80=AFAM, Adrian Klaver <adrian.klaver@aklaver.com> = wrote:


This one does not show the = actual query.
Right, sorry. It=E2=80=99s 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=E2=80=99s on the tilt_orientation = table:

geodesy=3D# = \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=3D# 

so = it=E2=80=99s a UUID (yeah, I went through a phase where I was using = UUID=E2=80=99s as primary keys=E2=80=A6in retrospect, probably not the = best idea...)

And for new_data_station_idx, = that=E2=80=99s on the tilt_data = table:

geodesy=3D# \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=3D# 

=E2=80=A6so again, a UUID. And note that tablespace =E2=80=9Cshar= ed drive=E2=80=9D is not actually a shared drive (anymore) - everything = has been moved to a local NVMe = drive.

=E2=80=94
Israe= l 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



= --Apple-Mail=_76EAAB3D-0BF5-450A-9BD2-BD4C6BCF5782--