Обсуждение: Possible regression in PG18 beta1

>> > * 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.

Yes, this is intentional as of 95dbd827f2edc

>> > * Changing the table from TEMP to UNLOGGED makes both versions use
>> > "Parallel Seq Scan", with PG18 being ~25% faster. (190ms vs. 150ms).

I'll be curious about tests with a normal table as well with a
sufficiently large
shared_buffers.

--
Sami Imseih
Amazon Web Services (AWS)

>> I'll be curious about tests with a normal table as well with a
>> sufficiently large shared_buffers.

> Here are results for a normal table with default shared_buffers (128 MB) and large shared_buffers (4GB):

thanks. I don't see regression for a normal table, at least for this test.

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.
However, the test as you have it is indexing all columns
on the table. If I just index on the filtered column

```
create index on t(k);

explain (analyze,buffers,costs off,timing off)
select k from t where k = 1;
```

I see similar behavior between HEAD
```
test=# explain (analyze,buffers,costs off,timing off)
select k from t where k = 1;
                          QUERY PLAN
---------------------------------------------------------------
 Index Only Scan using t_k_idx on t (actual rows=1.00 loops=1)
   Index Cond: (k = 1)
   Heap Fetches: 1
   Index Searches: 1
   Buffers: local hit=4
 Planning Time: 0.088 ms
 Execution Time: 0.059 ms
```

and 17.5
```
test=# explain (analyze,buffers,costs off,timing off)
select k from t where k = 1;
                         QUERY PLAN
------------------------------------------------------------
 Index Only Scan using t_k_idx on t (actual rows=1 loops=1)
   Index Cond: (k = 1)
   Heap Fetches: 1
   Buffers: local hit=4
 Planning Time: 0.084 ms
 Execution Time: 0.053 ms
(6 rows)

```

--
Sami

On Sat, May 17, 2025 at 12:38 PM Sadeq Dousti <msdousti@gmail.com> wrote:
> So, one mystery solved (no planner regression), still three questions:
> * Somewhat slower execution in PG18

I cannot recreate the problem.

The fastest plan for this query is a parallel sequential scan -- the
t_i_j_k_idx  index is useless. The only reason it's used for an
index-only scan when random_page_cost is reduced to 1.1 is because it
happens to have no fragmentation (meaning the index itself can be read
in physical order).

Even if I force an index-only scan, I still see no significant
performance differences when I compare REL_17_STABLE to master/18.

Here's what I see on master/18:

regression=# explain (analyze,buffers,costs off,timing off)
select * from t where k = 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: 0
   Index Searches: 1
   Buffers: shared hit=38318
 Planning Time: 0.025 ms
 Execution Time: 152.502 ms
(7 rows)

Here's the same query on current REL_17_STABLE:

regression=# explain (analyze,buffers,costs off,timing off)
select * from t where k = 1;
                           QUERY PLAN
----------------------------------------------------------------
 Index Only Scan using t_i_j_k_idx on t (actual rows=1 loops=1)
   Index Cond: (k = 1)
   Heap Fetches: 0
   Buffers: shared hit=38318
 Planning Time: 0.042 ms
 Execution Time: 155.890 ms
(6 rows)

I used a regular/logged table for this. I repeatedly executed the
query, to minimize noise (I'm showing the last execution for each of
master and REL_17_STABLE).

For what it's worth, there *are* known regressions with more
complicated cases, most of which involve multiple inequality/range
conditions on multiple high cardinality columns. This was discussed
extensively over the course of work on skip scan -- they were deemed
acceptable. But I see no reason to believe that this particular query
will run any slower on 18.

FWIW, similar queries that don't have to scan the full index (which
makes an index-only scan attractive to the planner) are much faster on
Postgres 18, compared to 17. Here's one executed on 18:

regression=# explain (analyze,buffers,costs off,timing off)
select * from t where i between 1 and 500_000 and k = 1;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Only Scan using t_i_j_k_idx on t (actual rows=1.00 loops=1)
   Index Cond: ((i >= 1) AND (i <= 500000) AND (k = 1))
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared hit=1919
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.039 ms
 Execution Time: 7.141 ms
(9 rows)

Here's the same query executed on Postgres 17:

regression=# explain (analyze,buffers,costs off,timing off)
select * from t where i between 1 and 500_000 and k = 1;
                           QUERY PLAN
----------------------------------------------------------------
 Index Only Scan using t_i_j_k_idx on t (actual rows=1 loops=1)
   Index Cond: ((i >= 1) AND (i <= 500000) AND (k = 1))
   Heap Fetches: 0
   Buffers: shared hit=1919
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.039 ms
 Execution Time: 10.771 ms
(8 rows)

--
Peter Geoghegan

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