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 1wdApE-003wCX-27 for pgsql-general@arkaria.postgresql.org; Fri, 26 Jun 2026 17:58:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wdApD-00Bwh3-1K for pgsql-general@arkaria.postgresql.org; Fri, 26 Jun 2026 17:58:47 +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 1wdApC-00Bwgs-1I for pgsql-general@lists.postgresql.org; Fri, 26 Jun 2026 17:58:47 +0000 Received: from mail-pl1-x629.google.com ([2607:f8b0:4864:20::629]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wdAp9-00000000MuD-2pvh for pgsql-general@lists.postgresql.org; Fri, 26 Jun 2026 17:58:45 +0000 Received: by mail-pl1-x629.google.com with SMTP id d9443c01a7336-2c81ee6e4a6so1020785ad.2 for ; Fri, 26 Jun 2026 10:58:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=alaska.edu; s=google; t=1782496723; x=1783101523; darn=lists.postgresql.org; h=to:date:message-id:subject:mime-version:content-type:from:from:to :cc:subject:date:message-id:reply-to:content-type; bh=PaRCR2M+wrQo3mz6XB39wU8E/Lpy13ZzVbBUvO35a4g=; b=iWJrh7H+7WKKwaZ/gD5Q6mbxLVKGtJR89PxAs5IDFlN5/cclLlbcGTj/e2+qDE5HGL w5wWkmIq9R3yAzkk8cnWoF7euFvJ+zxh5RkxEor/86HDOmpMsZvDA4ULeUiqiJZ3HwZy j7P1hLIbR9YtWu2X5kBrTZj83P+ySSVSSwQf0RPbtXW7M+9/W9Ipg/l9iCGallsk5s8/ /V4T0lhtjbQ11GA2esm+Ite2/m/KNsdtBDAKRIqNIUbMSUw70+W5u0yvYFAvkWhTROGI FMQoNDi2Z1kNc+gDO1NG4tSQgEHgV4brBj/YWXTYDGD/Yq9aWHQGhQEUYSVy6h16AYBq 4x+Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1782496723; x=1783101523; h=to:date:message-id:subject:mime-version:content-type:from:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to :content-type; bh=PaRCR2M+wrQo3mz6XB39wU8E/Lpy13ZzVbBUvO35a4g=; b=KLV0fEHmvRxF37QpAHk0owtLSLONmetS3biUnBRAXVbBaqZWyuQ0swrIvvskw+CAGh eGSQ9OV/NG9GyXeZ2jCpdF1oja9qN/sg+JYPKwqYulH75y7PKBlvCG46WuaxDHo4dYav 4OrlkGlXExI9BD+VIOpRwxmAlVMvSjW/Tpt45U63LIvQrsFLDViyICmtz1gTR/nRqiqA 3kHzp5CdbuLBBuXkRP8nzVsr88nz96RqjSLvqg1HmLF9vSg0aOuDcnf5Iss4tjwEo0p8 0uoAvQZnnWGNNR+pz2uPIXyCGyMCX6iIzAzgmdedKgi9pRP4iL+IqGddp/k6dSslRnYc swyg== X-Gm-Message-State: AOJu0YwB6dHGdfzR396fuia8xCBVmm7NPqhemx8+gOEcCafhzHqdUDtu VLiAfQskBjOETe3yMuL/PL4+42QAMtckf5T6r06iqdFsk1MrnJ21TQkzB9JG2IndIluxSZAll3d vxdTlAOxtFXKtUivCl6rvb7Ou6AawGihGIdZDgAy5C/pmUrpusR7cn0PgK7KOlcygwD0pZezhJk LidzaX6aIbg1sOn0LMAlyDSwat0x7L9TKHXlSOxSrg/sSZVKZaVdh+qtXfeA== X-Gm-Gg: AfdE7cmWnCL1gLi8ZhqrBNah607o5wDmW8k3gXTgL0CLIXLhVVfiQAqZX7GX0JGymqf 9BT0N2IWTMYURKvVAk2s+B4IfqHfYOFlVGbutKIsfhIDI//DDVvaMKsW5D23VWA52151wiPBkxV CunMVQWrdUP3I27gFXpavppjq0fZOqCx0g5yPBj6IMfXYzrQ3LO+SkjAOH4PngoicU8D4hyqmcw ibfkuB9BV6vS3StXs7L2knAK8muIJAt882SiqC02XcaSU7z9jXKRui+yVM0F8Dl2VUDdkZS/gow j2qDRnZANFHgUma8wo7AlfkCCjR0met9T/kkROPfRzxxzcF42WEEVUPiRspOXpjBNtoSMAXlmJ5 +7VOHXGb6ca5CHa4StgTNKV9xYrvWM1U8jDkKkT8Q9xsA1xOnuqo8q57KfluNF9QCip62XJ44nE u+7Pf0d4xpeMExWx6Y4dkCFpjf9Bv0dh41X/DjUtoFJzdqYth7bm91bSguPL3giSwzneEM20K6m FlrXuui X-Received: by 2002:a17:902:e802:b0:2c8:1c1e:49fe with SMTP id d9443c01a7336-2c81c1e4bebmr16530415ad.2.1782496722370; Fri, 26 Jun 2026 10:58:42 -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-2c7f65088cesm42746885ad.80.2026.06.26.10.58.41 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 26 Jun 2026 10:58:41 -0700 (PDT) From: Israel Brewster Content-Type: multipart/alternative; boundary="Apple-Mail=_6EB837D8-CC15-46A7-B015-37B16A32A483" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.600.51.1.1\)) Subject: PostgreSQL 15-->18 slowdown? Message-Id: <4A6D9280-1409-42A8-9A1F-99E2A558E2BB@alaska.edu> Date: Fri, 26 Jun 2026 09:58:30 -0800 To: PostgreSQL General 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=_6EB837D8-CC15-46A7-B015-37B16A32A483 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii 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=20 Geophysical Institute - UAF=20 2156 Koyukuk Drive=20 Fairbanks AK 99775-7320 Work: 907-474-5172 cell: 907-328-9145 --Apple-Mail=_6EB837D8-CC15-46A7-B015-37B16A32A483 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii 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/ll= AQ) 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/K2= K9

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=_6EB837D8-CC15-46A7-B015-37B16A32A483--