Possible regression in PG18 beta1

From: Sadeq Dousti <msdousti(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Possible regression in PG18 beta1
Date: 2025-05-17 15:45:07
Message-ID: CADE6Lvh62KopfpyScY-sMvLQE6Y5BBfAnt2wV-nsvZLetF+j6g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Dear all,

I was testing PG18 beta 1 new features (noticeably, AIO and index skip
scan), and I came up with this example:

===========
drop table if exists t;

create TEMP table t(i,j,k)
as select n,n,n
from generate_series(1,10_000_000) as n;

analyze t;

create index on t(i,j,k);

explain (analyze,buffers,costs off,timing off)
select * from t where k = 1;
===========

On my laptop (MacBook Air M3), Postgres 17.5 runs the query over 2x faster
than 18 beta1. The former uses index-only scan, while the latter uses seq
scan:

=== PG 17.5 =====
QUERY PLAN
----------------------------------------------------------------
Index Only Scan using t_i_j_k_idx on t (actual rows=1 loops=1)
Index Cond: (k = 1)
Heap Fetches: 1
Buffers: local hit=1 read=38317
Planning Time: 0.315 ms
Execution Time: 242.711 ms
(6 rows)

=== PG 18 beta 1 =====
QUERY PLAN
------------------------------------------
Seq Scan on t (actual rows=1.00 loops=1)
Filter: (k = 1)
Rows Removed by Filter: 9999999
Buffers: local hit=1 read=54079
Planning Time: 0.115 ms
Execution Time: 520.465 ms
(6 rows)

If I turn off enable_seqscan on the latter, it will use the index-only
scan, with the query time similar to PG17 (yet a tad slower):

=== PG 18 beta 1, with enable_seqscan disabled =====
QUERY PLAN
-------------------------------------------------------------------
Index Only Scan using t_i_j_k_idx on t (actual rows=1.00 loops=1)
Index Cond: (k = 1)
Heap Fetches: 1
Index Searches: 1
Buffers: local hit=1 read=38317
Planning Time: 0.200 ms
Execution Time: 281.742 ms
(7 rows)

* Also, I noticed "explain analyze" reports actual rows as a decimal number
(1.00) as opposed to an integer (1); not sure if that's intentional.

* Changing the table from TEMP to UNLOGGED makes both versions use
"Parallel Seq Scan", with PG18 being ~25% faster. (190ms vs. 150ms).

Best Regards,
Sadeq Dousti

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Aya Iwata (Fujitsu) 2025-05-17 16:31:30 Re: [WIP]Vertical Clustered Index (columnar store extension) - take2
Previous Message Andrew Jackson 2025-05-17 13:41:43 Re: Add Option To Check All Addresses For Matching target_session_attr