Content-Type: multipart/alternative;
 boundary="------------l1stdQmTL7yHjYa5I9A3Bsb0"
Message-ID: <27b2975b-c078-45cf-815d-eddbedbda153@cornfield.me.uk>
Date: Mon, 7 Apr 2025 15:48:48 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
To: pgsql-general@postgresql.org
Content-Language: en-GB
From: Simon Windsor <simon.windsor@cornfield.me.uk>
Subject: Slow timestamp query after upgrading from Pg13 to Pg16
Archived-At: <https://www.postgresql.org/message-id/27b2975b-c078-45cf-815d-eddbedbda153%40cornfield.me.uk>
Precedence: bulk

This is a multi-part message in MIME format.
--------------l1stdQmTL7yHjYa5I9A3Bsb0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hello

After upgrading a Db from Pg13 (Centos) to Pg16(Ubuntu) some queries on 
a simple, large table (200M rows) are very slow

id integer not null
status char(4) not null
status_tstamp timestamp
<... other columns ...>

If I create indexes on

  * status, status_tstamp
  * status_tstamp when status_timestamp is not null

Queries for a set date range for a given status are good and perform 
similarly to Pg13

Queries for a set date range are VERY slow compared to Pg13. A few other 
queries on tables with indexes like

CREATE INDEX table_col ON table (col) WHERE col IS NOT NULL;

also appear to be slow.

Any ideas, observations or comments would be appreciated

Simon

-- 
Simon Windsor

Eml:simon.windsor@cornfield.me.uk
Mob: 0755 197 9733


“There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.”

--------------l1stdQmTL7yHjYa5I9A3Bsb0
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hello</p>
    <p>After upgrading a Db from Pg13 (Centos) to Pg16(Ubuntu) some
      queries on a simple, large table (200M rows) are very slow</p>
    <font face="monospace">id integer not null</font><br>
    <font face="monospace">status char(4) not null</font><br>
    <font face="monospace">status_tstamp timestamp</font><br>
    <font face="monospace">&lt;... other columns ...&gt;</font><br>
    <br>
    If I create indexes on
    <ul>
      <li>status, status_tstamp</li>
      <li>status_tstamp when status_timestamp is not null</li>
    </ul>
    <p>Queries for a set date range for a given status are good and
      perform similarly to Pg13</p>
    <p>Queries for a set date range are VERY slow compared to Pg13. A
      few other queries on tables with indexes like</p>
    <p>CREATE INDEX table_col ON table (col) WHERE col IS NOT NULL;</p>
    <p>also appear to be slow.</p>
    <p>Any ideas, observations or comments would be appreciated</p>
    <p>Simon <br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Simon Windsor

Eml: <a class="moz-txt-link-abbreviated" href="mailto:simon.windsor@cornfield.me.uk">simon.windsor@cornfield.me.uk</a>
Mob: 0755 197 9733


“There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.”</pre>
  </body>
</html>

--------------l1stdQmTL7yHjYa5I9A3Bsb0--