Обсуждение: pgv18: simple table scan take more time than pgv14

Поиск
Список
Период
Сортировка

pgv18: simple table scan take more time than pgv14

От
James Pang
Дата:
same OS RHEL8, install pgv14.11 and pgv18.beta1_3, both installation by RPM from pgdg, and use similar postgresql.conf. 

14.11
postgres=# create table tt(a int primary key, b text);
CREATE TABLE
postgres=# do
postgres-# $$
postgres$# begin
postgres$#    for counter in 1..1000000 loop
postgres$# insert into tt values(counter,'jamestest');
postgres$#    end loop;
postgres$# end;
postgres$# $$;
DO

pgv14.11

postgres=# \timing on
Timing is on.
postgres=# \timing on
Timing is on.
postgres=# select * from tt where b ~~ 'a%';
 a | b
---+---
(0 rows)

Time: 61.018 ms
postgres=# select * from tt where b ~~ 'a%';
 a | b
---+---
(0 rows)

Time: 55.082 ms
postgres=# select * from tt where b ~~ 'a%';
 a | b
---+---
(0 rows)

Time: 54.716 ms

postgres=# explain select * from tt where b ~~ 'a%';
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on tt  (cost=0.00..17906.00 rows=1 width=14)
   Filter: (b ~~ 'a%'::text)
(2 rows)

Time: 0.447 ms


18.beta1
postgres=# \timing on
Timing is on.
postgres=# select * from tt where b ~~ 'a%';
 a | b
---+---
(0 rows)

Time: 78.532 ms
postgres=# select * from tt where b ~~ 'a%'
postgres-# ;
 a | b
---+---
(0 rows)

Time: 83.516 ms
postgres=# select * from tt where b ~~ 'a%';
 a | b
---+---
(0 rows)

Time: 77.117 ms

psql (18beta1)
Type "help" for help.

postgres=# explain select * from tt where b ~~ 'a%';
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on tt  (cost=0.00..17906.00 rows=1 width=14)
   Filter: (b ~~ 'a%'::text)
(2 rows)

Regards,

James

Re: pgv18: simple table scan take more time than pgv14

От
"David G. Johnston"
Дата:
On Thursday, June 19, 2025, James Pang <jamespang886@gmail.com> wrote:
same OS RHEL8, install pgv14.11 and pgv18.beta1_3, both installation by RPM from pgdg, and use similar postgresql.conf. 

You will need to show encoding and locale information for the databases under test.
 

postgres$#    for counter in 1..1000000 loop
postgres$# insert into tt values(counter,'jamestest');


Using generate_series is a lot easier and faster.
 
Also, filtering using the operator “~~” doesn’t constitute a “simple” table scan.

David J.


Re: pgv18: simple table scan take more time than pgv14

От
Andres Freund
Дата:
Hi,

On 2025-06-20 09:46:06 +0800, James Pang wrote:
> same OS RHEL8, install pgv14.11 and pgv18.beta1_3, both installation by RPM
> from pgdg, and use similar postgresql.conf.
> 

> 14.11

> Time: 55.082 ms
> postgres=# select * from tt where b ~~ 'a%';
>  a | b
> ---+---
> (0 rows)
> 
> Time: 54.716 ms

> 18.beta1
> postgres=# select * from tt where b ~~ 'a%';
>  a | b
> ---+---
> (0 rows)
> 
> Time: 77.117 ms

I tried but failed to reproduce this.  In fact, I see both 17 and 18 being
faster than 14.

Have you checked if 14 and 18 actually use the same collation?

Greetings,

Andres Freund