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

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