pgv18: simple table scan take more time than pgv14
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
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.
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
Hi,
On Fri, 2025-06-20 at 09:46 +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.
<snip>
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)
It's likely because v18 beta RPMs are built with --enable-debug and
--enable-cassert . See the note on yum.postgresql.org:
https://yum.postgresql.org/rpmchart/#pg18
Regards,
--
Devrim Gündüz
Open Source Solution Architect, PostgreSQL Major Contributor
BlueSky: @devrim.gunduz.org , @gunduz.org
install 18.beta1 from source ,remove enable_debug, much better now. it's
better than pgv14.11.
postgres=# select * from tt where b ~~ 'a%';
a | b
---+---
(0 rows)
Time: 67.545 ms
postgres=# select * from tt where b ~~ 'a%';
a | b
---+---
(0 rows)
Time: 50.945 ms
postgres=# select * from tt where b ~~ 'a%';
a | b
---+---
(0 rows)
Time: 48.176 ms
postgres=# select * from tt where b ~~ 'a%';
a | b
---+---
(0 rows)
Time: 49.919 ms
Thanks,
James
Devrim Gündüz <devrim@gunduz.org> 於 2025年6月21日週六 下午7:13寫道：
Show quoted text
Hi,
On Fri, 2025-06-20 at 09:46 +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.<snip>
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)It's likely because v18 beta RPMs are built with --enable-debug and
--enable-cassert . See the note on yum.postgresql.org:https://yum.postgresql.org/rpmchart/#pg18
Regards,
--
Devrim Gündüz
Open Source Solution Architect, PostgreSQL Major Contributor
BlueSky: @devrim.gunduz.org , @gunduz.org