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 1wdB8q-003wO2-14 for pgsql-general@arkaria.postgresql.org; Fri, 26 Jun 2026 18:19:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wdB8p-00CDrG-0b for pgsql-general@arkaria.postgresql.org; Fri, 26 Jun 2026 18:19:03 +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 1wdB6N-00C8c6-1Y for pgsql-general@lists.postgresql.org; Fri, 26 Jun 2026 18:16:31 +0000 Received: from mail-pl1-x636.google.com ([2607:f8b0:4864:20::636]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wdB6L-00000000N2u-0Qtd for pgsql-general@lists.postgresql.org; Fri, 26 Jun 2026 18:16:30 +0000 Received: by mail-pl1-x636.google.com with SMTP id d9443c01a7336-2c0c32d1350so2224265ad.3 for ; Fri, 26 Jun 2026 11:16:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=alaska.edu; s=google; t=1782497788; x=1783102588; 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=PfwsQU67fNp82hfwx1pXZU59jEc7c6X9KS08TP7EOuQ=; b=CCTulEgXkUiSVmCIikPj6v4RN4HidTnSV2ZTTDCjuRqeAKh2O+uYVrggSdGbOaxqwR mtZuHt34Yl4EO3kfJaqxoK1RDEoTZCixsQFdFHl8ESsveDy2kPq41d6TWOKPZMp6oXDt y5U7m6MFRkLz+a0JwEITWUb+ccfjNTJBKhXBy65X/bzYaVoE8gqSw2BQe/9s/yoLP98Q fXqsSxbs7ncDwO7PqcudNO5RVIzTyO9ifD5+LSMZNfBKJuwa9AS4IRMr8ZT1wSE46y9i wOuW107LBmfEWRdFkEO/JPutWD6TW254P+cAzuf9I/8geQot2ZA4T4fFb6/eAjknoblj mMEw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1782497788; x=1783102588; 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=PfwsQU67fNp82hfwx1pXZU59jEc7c6X9KS08TP7EOuQ=; b=bm1W9AJTTpaMpyooGGQz6+sLZLY4miqmq1WM4EnoD0FyZPEbdLz3M5PFfzuKYH3Z5E hh2Htq32YReLz67Wtvey1fM8wBlEvhXOrUY8Tni7tFQ+RBI0bP7zVwKFGV4Dbuf2lpiG ronY+J8CjB3co66QkWWd00k05pYxPvAmLW1YMQUTLJBEAmlUmpv6H9nvuWB9woG0Ezok SBb25qXfnc1/0CbX6TQqgnOxrXNvBQVLIZjqOR7zzMju7/AT+sEZWatwDdjr5OQj4gEB R1KLgViQ5/P3v1ioj4zj1vBiE2gc/S6/5+L/EEU02AoJCiplVPdT80Xn+z8k46GEHmM2 aTQA== X-Gm-Message-State: AOJu0YzQ0y2MY0J10TIQd2nhxwEZPJ+yT4JKhrn/1GWRug4I26GKAqRX XuciFKCmsmCarpmlP9pOlxdBGUwCN+i9fllkdPi5XkClTKNN/XH2TrL7sycXIOoONhTtoTqAN+9 Bu/avGQ== X-Gm-Gg: AfdE7ckkOBFq/L9r7sfqdDIknDS4eUnPjQo11Mh9NBcqC2MN5cFMRlwKMhZi75yzeW4 +Zr48AM9hQ0yfodsrOiY97UTwqMPHVCOcueVGr8Lij1OCB2vPHupKzFFeOKLu3acx9OprdRQkPS NFoadiCpim7+TXg3BVBfe9Ru+W6GbHX9vAQFwPfV/KVOvynC1raWRKGyMbZBZBC15Y8LAcpW45M wgio4hzNVyrML37k99AY4ozrSOQOJeSkToMOxPb9fB9BCEF0CqtYDeKEYLi+QbnhxNTOiMo23AH X27uKGxr9Jzf3to/kv2SQPedID5ow+y98p683wYOfyyl6zs5yfgmz0RAH62ZY5PSmv8IVGzCO+s ZaEzQ2b06NZURbfnPipUdgiSIsqlfUHj3bWm/EBpE7MFjHlHG+0IjU7NlNqjTEj9jEQC0Bg16/U 4vPnVCmFjuBmQP0M7v5iCgrsDX7TnaGuUkdWx+ykY79A3l+Omv5i41vuZis7A2oCCdclB+rEk8E vJgIjS5 X-Received: by 2002:a05:6a00:1f11:b0:845:b58d:a554 with SMTP id d2e1a72fcca58-845b58da802mr3636461b3a.5.1782497787748; Fri, 26 Jun 2026 11:16:27 -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 d2e1a72fcca58-845ce2eaef3sm1141735b3a.44.2026.06.26.11.16.25 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 26 Jun 2026 11:16:25 -0700 (PDT) From: Israel Brewster Message-Id: <7F4F161B-CD44-4686-A49B-C1C466A66951@alaska.edu> Content-Type: multipart/alternative; boundary="Apple-Mail=_4659879C-679B-47D6-92AF-0D2DD6B5947D" 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:16:14 -0800 In-Reply-To: Cc: PostgreSQL General To: Ruben Morais References: <4A6D9280-1409-42A8-9A1F-99E2A558E2BB@alaska.edu> 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=_4659879C-679B-47D6-92AF-0D2DD6B5947D Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Thanks for the suggestion. Unfortunately, jit off didn=E2=80=99t 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 =3D = off;, the query ran in ~125ms - back to the fast speeds I was used to in = PostgreSQL 15. I=E2=80=99ve tried a couple of re-writes (LATERAL JOIN and using EXISTS = rather than LIMIT 1), but they don=E2=80=99t seem to help. QUERY PLAN = =20 = --------------------------------------------------------------------------= ---------------------------- Sort (cost=3D182445.18..182445.63 rows=3D177 width=3D152) Sort Key: (COALESCE(sites.name, 'Unknown'::text)), stations.name -> Hash Left Join (cost=3D1.29..182438.57 rows=3D177 width=3D152) Hash Cond: (stations.siteref =3D sites.id) -> Seq Scan on stations (cost=3D0.00..6.77 rows=3D177 = width=3D87) -> Hash (cost=3D1.13..1.13 rows=3D13 width=3D11) -> Seq Scan on sites (cost=3D0.00..1.13 rows=3D13 = width=3D11) SubPlan 1 -> Limit (cost=3D0.00..0.48 rows=3D1 width=3D1) -> Nested Loop (cost=3D0.00..13833793.79 = rows=3D29105774 width=3D1) -> Seq Scan on tilt_data = (cost=3D0.00..13469970.40 rows=3D29105774 width=3D16) Filter: (station =3D stations.id) -> Materialize (cost=3D0.00..1.22 rows=3D1 = width=3D16) -> Seq Scan on tilt_orientation = (cost=3D0.00..1.21 rows=3D1 width=3D16) Filter: (station =3D stations.id) SubPlan 2 -> Aggregate (cost=3D1030.18..1030.19 rows=3D1 width=3D32) -> Sort (cost=3D1029.93..1030.05 rows=3D50 width=3D126)= Sort Key: t.ord -> Merge Join (cost=3D25.27..1028.52 rows=3D50 = width=3D126) Merge Cond: ((s.volcano_id)::text =3D = (t.id)::text) -> Foreign Scan on volcano s = (cost=3D25.00..1025.00 rows=3D1000 width=3D156) Remote server startup cost: 25 -> Sort (cost=3D0.27..0.29 rows=3D10 = width=3D40) Sort Key: t.id -> Function Scan on unnest t = (cost=3D0.00..0.10 rows=3D10 width=3D40) (26 rows) --- 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 > On Jun 26, 2026, at 10:08=E2=80=AFAM, Ruben Morais = wrote: >=20 > Hi, >=20 > Saw your email, and checked you have JIT enable. > Try to disable and run the query again >=20 > SET jit =3D off; >=20 > Or test the explain with jit off, to check the plan > EXPLAIN (jit off) SELECT ...; >=20 > Jit sometime had issues in plans in postgres. > Other solution is create indexes or rewrite the code. >=20 > Regards > R=C3=BAben Morais >=20 > (+351) 965775713 = = ruben.gmorais@gmail.com > On Fri, Jun 26, 2026, 18:58 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. >>=20 >> 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. >>=20 >> I tried the obvious: REINDEX database and VACUUM ANALYZE, but neither = helped. I have my default_statistics_target set to 500 at the moment. >>=20 >> Then I tried SET enable_seqscan =3D off; Lo and behold, the query ran = in only 123.888 ms (fun number :-D ) - https://explain.depesz.com/s/K2K9 >>=20 >> What did I do wrong here? Thanks! >>=20 >> The query in question: >>=20 >> 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=3Dtilt_orientation.station >> WHERE tilt_data.station=3Dstations.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 =3Ds.volcano_id) >> ), '{}' ) volcnames >> FROM stations >> LEFT JOIN sites ON stations.siteref=3Dsites.id >> ORDER BY site, name >>=20 >>=20 >> --- >> 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 --Apple-Mail=_4659879C-679B-47D6-92AF-0D2DD6B5947D Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Thanks for the suggestion. = Unfortunately, jit off didn=E2=80=99t 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 =3D off;, the query ran in ~125ms - = back to the fast speeds I was used to in PostgreSQL = 15.

I=E2=80=99ve tried  a couple of re-writes = (LATERAL JOIN and using EXISTS rather than LIMIT 1), but they don=E2=80=99= t seem to help.

        =                     =                   QUERY = PLAN                   =                     =       =  
---------------------------------------------------------= ---------------------------------------------
 Sort =  (cost=3D182445.18..182445.63 rows=3D177 = width=3D152)
   Sort Key: (COALESCE(sites.name, = 'Unknown'::text)), stations.name
   ->  Hash = Left Join  (cost=3D1.29..182438.57 rows=3D177 = width=3D152)
         Hash Cond: = (stations.siteref =3D sites.id)
        =  ->  Seq Scan on stations  (cost=3D0.00..6.77 rows=3D177= width=3D87)
         -> =  Hash  (cost=3D1.13..1.13 rows=3D13 width=3D11)
 =              ->  Seq Scan on = sites  (cost=3D0.00..1.13 rows=3D13 width=3D11)
  =        SubPlan 1
      =      ->  Limit  (cost=3D0.00..0.48 rows=3D1 = width=3D1)
              =    ->  Nested Loop  (cost=3D0.00..13833793.79 = rows=3D29105774 width=3D1)
          =              ->  Seq Scan on = tilt_data  (cost=3D0.00..13469970.40 rows=3D29105774 = width=3D16)
              =                Filter: (station = =3D stations.id)
            =            ->  Materialize =  (cost=3D0.00..1.22 rows=3D1 width=3D16)
    =                     =      ->  Seq Scan on tilt_orientation =  (cost=3D0.00..1.21 rows=3D1 width=3D16)
    =                     =            Filter: (station =3D = stations.id)
         SubPlan = 2
           -> =  Aggregate  (cost=3D1030.18..1030.19 rows=3D1 = width=3D32)
              =    ->  Sort  (cost=3D1029.93..1030.05 rows=3D50 = width=3D126)
              =          Sort Key: t.ord
  =                     =  ->  Merge Join  (cost=3D25.27..1028.52 rows=3D50 = width=3D126)
              =                Merge Cond: = ((s.volcano_id)::text =3D (t.id)::text)
      =                     =    ->  Foreign Scan on volcano s =  (cost=3D25.00..1025.00 rows=3D1000 width=3D156)
  =                     =              Remote server startup = cost: 25
              =                ->  Sort =  (cost=3D0.27..0.29 rows=3D10 width=3D40)
    =                     =            Sort Key: t.id
  =                     =              ->  Function = Scan on unnest t  (cost=3D0.00..0.10 rows=3D10 = width=3D40)
(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=E2=80=AF= AM, Ruben Morais <ruben.gmorais@gmail.com> wrote:

Hi,

Saw your email, and checked you have JIT enable.
Try to disable and run the query again

SET jit =3D 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=C3=BAben = Morais

 (+351) 965775713  =     =      ruben.gmorais@gmail.com
<= /div>

On Fri, Jun 26, 2026, 18:58 Israel = Brewster <ijbrewster@alaska.edu> = 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 =3D = 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=3Dtilt_orientation.station
                =   WHERE tilt_data.station=3Dstations.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=3Ds.volcano_id)
            ), '{}' ) = volcnames
    FROM = stations
    LEFT JOIN = sites ON stations.siteref=3Dsites.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


= --Apple-Mail=_4659879C-679B-47D6-92AF-0D2DD6B5947D--