Re: Possible regression in PG18 beta1

Hi,

You're right, with settings, it revealed that PG17 had random_page_cost=1.1
configured.

Adding that to PG18 sets the plan to index only; however, PG18 is still
somewhat (~15%) slower:

============ PG17.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 read=38318 written=424
Settings: random_page_cost = '1.1'
Planning:
Buffers: shared hit=29, local read=1 written=1
Planning Time: 0.098 ms
Execution Time: 137.209 ms
(9 rows)

============ PG18 Beta 1 =================
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 read=38318 written=443
Settings: random_page_cost = '1.1'
Planning:
Buffers: shared hit=30, local read=1
Planning Time: 0.097 ms
Execution Time: 160.595 ms
(10 rows)

So, one mystery solved (no planner regression), still three questions:
* Somewhat slower execution in PG18
* Planner differences in TEMP vs. UNLOGGED
* Actual rows with decimal (1.00) vs. integer (1)

Best Regards,
Sadeq Dousti

On Sat, May 17, 2025 at 6:18 PM Christophe Courtois <
christophe(dot)courtois(at)dalibo(dot)com> wrote:

> Hi,
>
> - I cannot reproduce your regression, I have always a Seq Scan (PG 13,
> 17, 18, default config, last versions).
>
> I suggest that you add a SETTINGS clause in EXPLAIN and \d+ and \di+
> before, just in case.
>
> - I see the difference between TEMP and UNLOGGED too (since PG13),
> this is funny but I have no explanation. Something to do with the access
> to shared buffers, I suppose.
>
> Yours,
>
> Le 17/05/2025 à 17:45, Sadeq Dousti a écrit :
> > 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
>
> --
> _________ ____
> | || | Christophe Courtois
> | ||__ | Consultant DALIBO
> | | | | 43, rue du Faubourg Montmartre
> | - | / / 75009 Paris
> |___| |___| \/ www.dalibo.com
>
>