Re: Performance degradation if query returns no rows and columnexpression is used after upgrading to 12 - Mailing list pgsql-general

Hi!

 

>It is really strange why it is too slow. Can you prepare test case? Looks like bug (maybe not Postgres's bug)

 

Testcase is below.

With  jit on it takes 3.3 sec  and with jit off 1.5 sec.

 

Andrus.

 

create temp table toode ( toode char(20), ribakood char(20),

                         nimetus char(50), markused char(50), engnimetus char(50) ) on commit drop;

insert into toode (toode) select generate_series(1,14400);

CREATE INDEX ON toode USING gin

    (to_tsvector('english'::regconfig, nimetus::text));

CREATE UNIQUE INDEX    ON toode     (ribakood )

    WHERE ribakood IS NOT NULL AND btrim(ribakood::text) <> ''::text;

CREATE INDEX ON toode (toode);

CREATE UNIQUE INDEX    ON toode     (upper(toode::text) );

create temp table dok ( dokumnr serial  primary key ) on commit drop;

insert into dok  select generate_series(1,14400);

 

create temp table rid ( dokumnr int, taitmata numeric, toode char(20)  ) on commit drop;

insert into rid  select generate_series(1,1440000);

 

CREATE INDEX rid_dokumnr_idx    ON rid     (dokumnr );

-- jit on: 3.3 sec  jit off: 1.5 sec

set jit to off;

select

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

  (select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode ) ,

(select sum(taitmata) from rid join dok using (dokumnr)  where toode=toode.toode )

 

from toode

where toode.ribakood='testmiin'::text

       or toode.nimetus ilike '%'||'testmiin'||'%' escape '!'

       or toode.toode ilike '%'||'testmiin'||'%' escape '!'

       or toode.markused ilike '%'||'testmiin'||'%' escape '!'

 

or to_tsvector('english',toode.nimetus) @@ plainto_tsquery('testmiin')

         or to_tsvector('english',toode.engnimetus) @@

         plainto_tsquery('testmiin')