Re: Possible regression in PG18 beta1 - Mailing list pgsql-hackers

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