Re: Possible regression in PG18 beta1

On Sat, May 17, 2025 at 2:00 PM Sadeq Dousti <msdousti(at)gmail(dot)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