public inbox for [email protected]  

help / color / mirror / Atom feed

From: Israel Brewster <[email protected]>
To: PostgreSQL General <[email protected]>
Subject: Re: PostgreSQL 15-->18 slowdown?
Date: Fri, 26 Jun 2026 11:35:55 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

> On Jun 26, 2026, at 9:58 AM, Israel Brewster <[email protected]> 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.
> ...

Some additional information that may (or may not) be helpful: in the tilt_data table, there are only 15 distinct stations (out of a total of 177 stations) across 407,032,436 rows

…so the query planner is correct to think that if looking for a single station, it will get a large number of rows…at least if you ignore the LIMIT 1.

I did manage to find a workaround: If I change the subquery on tilt_data to this:

        (stations.id = ANY (
            SELECT tilt_data.station 
            FROM tilt_data 
            INNER JOIN tilt_orientation ON tilt_data.station = tilt_orientation.station
            WHERE tilt_data.station = stations.id
        )) as has_tilt,

things work better - still not as fast as the original query with sequence scan disabled, but fast enough to work with: https://explain.depesz.com/s/bki5

…but not only does that feel ugly (to me at least), it’s still roughly 4x slower than the original query with sequence scans disabled… Good enough to get me back up and running, but not really a great solution, I don’t think. I might also try a lookup table (materialized view), but I don’t know how much better that would be, as I would have to refresh it whenever the data changes. 
—
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)

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