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

st 8. 4. 2020 v 15:34 odesílatel Andrus <kobruleht2(at)hot(dot)ee> napsal:

> Hi
>
> >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.
>
> Below is modified testcase which returns one row.
> In Debian 10 VPS with jit on it takes 2.5 sec and with jit off 0.4 s
>
> jit is still many times slower in Debian even if data is returned.
>
> In Windows 10 workstation there is no difference.
>

if I know it well, then there JIT is disabled

> >The situation when the query returns no rows, then JIT is significant
> bottleneck - but it looks like corner case.
>
> Both testcases simulate search queries in typical e-shop.
> Users can use any search term and expect that query returns fast.
>
> Modified testcase which returns one row:
>
> create temp table toode ( toode char(20) primary key, ribakood char(20),
> nimetus char(50), markused char(50), engnimetus char(50)
> ) on commit drop;
> insert into toode (toode) select generate_series(1,14400);
> insert into toode (toode,nimetus)
> select 'TEST'|| generate_series, 'This is testmiin item'
> from generate_series(1,1);
>

one row is probably too less - the overhead of JIT is fix, but benefit of
JIT is linear

> 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 temp table dok ( dokumnr serial primary key ) on commit drop;
> insert into dok select generate_series(1,10000);
>
> create temp table rid (id serial primary key,
> dokumnr int references dok, taitmata numeric, toode
> char(20) references toode ) on commit drop;
> insert into rid (dokumnr,toode)
> select generate_series % 10000+1, 1
> from generate_series(1,10000);
>
> CREATE INDEX ON rid(dokumnr );
> CREATE INDEX ON rid(toode);
> -- jit on: 2.5 sec jit off: 0.4 s
> 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')
>
> Andrus.
>