Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





st 8. 4. 2020 v 15:34 odesílatel Andrus <kobruleht2@xxxxxx> 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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux