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

On Sat, May 17, 2025 at 2:00 PM Sadeq Dousti <msdousti@gmail.com> wrote:
>>
>> thanks. I don't see regression for a normal table, at least for this test.
>
>
> No, there isn't. I just added them as per your request ;)
>
>
>> In terms of your original test, I tried it out on my Ubuntu machine
>>
>> and with your test as-is, I see 2.8 seconds on 17.5 and 3.3 seconds
>>
>> on HEAD if the plan performs a seq scan without parallelism.
>
>
> Which is unexpected, no?

For the temp table test, it seems like I can account mostly all of the
extra time to the fact that checksums are enabled by default in 18,
due to 04bec894a04c

I ran the below script which runs the select 100 times against
the temp table on HEAD

```
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);

SELECT 'select * from t where k = 1;' FROM generate_series(1, 100)
\gexec
```

and looked at perf top at the time, which shows
pg_checksum_block at the top using a cluster that was created
with initdb without any flags.

```
  12.12%  postgres   [.] pg_checksum_block
  11.97%  postgres   [.] ExecInterpExpr
  10.57%  postgres   [.] slot_deform_heap_tuple_internal
   5.90%  postgres   [.] fetch_att
   4.18%  postgres   [.] heapgettup_pagemode
```

and explain analyze for a single execution
```
test=# EXPLAIN (ANALYZE, timing on) select * from t where k = 1;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..179080.00 rows=1 width=12) (actual
time=0.065..3375.125 rows=1.00 loops=1)
   Filter: (k = 1)
   Rows Removed by Filter: 9999999
   Buffers: local read=54080
 Planning Time: 0.090 ms
 Execution Time: 3375.149 ms
(6 rows)
```

Now, with initdb and --no-data-checksums
```
  13.32%  postgres   [.] ExecInterpExpr
  12.44%  postgres   [.] slot_deform_heap_tuple_internal
   6.64%  postgres   [.] fetch_att
   4.70%  postgres   [.] heapgettup_pagemode
   4.22%  postgres   [.] slot_deform_heap_tuple
   3.75%  postgres   [.] TupleDescCompactAttr
```
and explain for a single execution
```
test=# EXPLAIN (ANALYZE, timing on) select * from t where k = 1;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..179080.00 rows=1 width=12) (actual
time=0.043..2939.101 rows=1.00 loops=1)
   Filter: (k = 1)
   Rows Removed by Filter: 9999999
   Buffers: local read=54080
 Planning Time: 0.087 ms
 Execution Time: 2939.125 ms
(6 rows)
```
v18 with --no-data-checksums gives me close performance to v17

Can you try the same test ( with --no-data-checksums) on you mac
and see if that makes a difference?

[0] states "Only data pages are protected by checksums; internal data
structures and temporary files are not."

Is what is observed with temp files being protected by checksums correct?

[0] https://www.postgresql.org/docs/current/checksums.html

--
Sami