Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12

[Andrus]

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')