Thread

1. pgv18: simple table scan take more time than pgv14

   James Pang <jamespang886@gmail.com> — 2025-06-20T01:46:06Z

   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
   

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

   David G. Johnston <david.g.johnston@gmail.com> — 2025-06-20T02:13:42Z

   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.
   

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

   Andres Freund <andres@anarazel.de> — 2025-06-20T15:02:23Z

   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
   
   
   
   

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

   Devrim GÜNDÜZ <devrim@gunduz.org> — 2025-06-21T11:13:54Z

   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
   

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

   James Pang <jamespang886@gmail.com> — 2025-06-23T12:28:21Z

   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寫道：
   
   > 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
   >