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