Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12
| От | Andrus |
|---|---|
| Тема | Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12 |
| Дата | |
| Msg-id | 4BAF2D141AF643C990BC5EFED45DD3AA@dell2 обсуждение исходный текст |
| Ответ на | Re: Performance degradation if query returns no rows and columnexpression is used after upgrading to 12 (Pavel Stehule <pavel.stehule@gmail.com>) |
| Ответы |
Re: Performance degradation if query returns no rows and columnexpression is used after upgrading to 12
|
| Список | 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 по дате отправления:
Предыдущее
От: Adrian KlaverДата:
Сообщение: Re: Estimated resources for a 500 connections instance (VM)