MIME-Version: 1.0
References: <87bjjv1v96.fsf@gmail.com>
In-Reply-To: <87bjjv1v96.fsf@gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Fri, 19 Dec 2025 09:20:30 +1300
Message-ID: <CAApHDvpH1-GfwvAEKGb1WniOFK-f66E_Ex9fD-AqotDaq=OyXQ@mail.gmail.com>
Subject: Re: Dealing with SeqScans when Time-based Partitions Cut Over
To: Matthew Planchard <msplanchard@gmail.com>
Cc: pgsql-general@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvpH1-GfwvAEKGb1WniOFK-f66E_Ex9fD-AqotDaq%3DOyXQ%40mail.gmail.com>
Precedence: bulk

On Fri, 19 Dec 2025 at 07:48, Matthew Planchard <msplanchard@gmail.com> wrote:
> * Because the planner's latest knowledge of the partition was based on
>   its state prior to the cutover, it assumes the partition is empty and
>   creates plans that use sequential scans

If the table/partition was just created and not analyzed, the planner
shouldn't consider it empty. By default, an empty table is assumed to
have 10 pages. (Check what pg_class.relpages is set to. -1 means it's
not been analyzed, 0 would mean analyzed/vacuumed when empty)

create table a (a int primary key, b int not null);
create index on a(b);
explain select * from a where a = 1;

 Index Scan using a_pkey on a  (cost=0.15..8.17 rows=1 width=8)
   Index Cond: (a = 1)

explain select * from a where b = 1;

 Bitmap Heap Scan on a  (cost=4.24..14.91 rows=11 width=8)
   Recheck Cond: (b = 1)
   ->  Bitmap Index Scan on a_b_idx  (cost=0.00..4.24 rows=11 width=0)
         Index Cond: (b = 1)

Perhaps pg_partman thinks running an ANALYZE or VACUUM on the new
partitions is a good idea? Otherwise, if you're not doing this
yourself, I don't know why you're getting a Seq Scan. It might help if
you showed the problem query and schema.

David