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

Hi

út 7. 4. 2020 v 23:56 odesílatel Andrus <kobruleht2(at)hot(dot)ee> napsal:

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

this query is little bit strange - it has pretty big cost, and because
returns nothing, then it's pretty fast against cost.

there is 18 subqueries, but jit_above_cost is ralated just to one query.
This is probably worst case for JIT.

This query is pretty slow and expensive (and then the cost of JIT is
minimal), but when the query returns some rows, then JIT start to helps.

So maybe if you find some queries that returns some rows, then the speed
will be better with active JIT than with disabled JIT.

The situation when the query returns no rows, then JIT is significant
bottleneck - but it looks like corner case.

Regards

Pavel