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
