Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12 - Mailing list pgsql-general
| From | Andrus |
|---|---|
| Subject | Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12 |
| Date | |
| Msg-id | 4BAF2D141AF643C990BC5EFED45DD3AA@dell2 Whole thread Raw |
| In response to | Re: Performance degradation if query returns no rows and columnexpression is used after upgrading to 12 (Pavel Stehule <pavel.stehule@gmail.com>) |
| Responses |
Re: Performance degradation if query returns no rows and columnexpression is used after upgrading to 12
|
| 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')
pgsql-general by date: