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

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

> Hi!
>
> Query returns no rows but its execution time in Postgres 12 depends on the
> column expression.
>
> Query with column expression
>
> coalesce( (select sum(taitmata) from rid join dok using (dokumnr)
> where toode=toode.toode and doktyyp='T' and not dok.taidetud and
> dok.kinnitatud and
> taitmata is not null),0)
>
> takes 666 ms :
>
> explain analyze select
> coalesce( (select sum(taitmata) from rid join dok using (dokumnr)
> where toode=toode.toode and doktyyp='T' and not dok.taidetud and
> dok.kinnitatud and
> taitmata is not null),0)
> 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')
>
> "Gather (cost=1000.00..505930.82 rows=153 width=32) (actual
> time=661.419..661.476 rows=0 loops=1)"
> " Workers Planned: 1"
> " Workers Launched: 1"
> " -> Parallel Seq Scan on toode (cost=0.00..10015.31 rows=90 width=21)
> (actual time=574.922..574.922 rows=0 loops=2)"
> " Filter: (((ribakood)::text = 'testmiin'::text) OR (nimetus ~~*
> '%testmiin%'::text) OR (toode ~~* '%testmiin%'::text) OR
> (markused ~~* '%testmiin%'::text) OR (to_tsvector('english'::regconfig,
> (nimetus)::text) @@ plainto_tsquery('testmiin'::text)) OR
> (to_tsvector('english'::regconfig, (engnimetus)::text) @@
> plainto_tsquery('testmiin'::text)))"
> " Rows Removed by Filter: 7202"
> " SubPlan 1"
> " -> Aggregate (cost=3234.63..3234.64 rows=1 width=32) (never
> executed)"
> " -> Nested Loop (cost=11.26..3234.52 rows=43 width=3) (never
> executed)"
> " -> Bitmap Heap Scan on rid (cost=10.84..1191.72
> rows=270 width=7) (never executed)"
> " Recheck Cond: (toode = toode.toode)"
> " Filter: (taitmata IS NOT NULL)"
> " -> Bitmap Index Scan on rid_toode_pattern_idx
> (cost=0.00..10.77 rows=312 width=0) (never executed)"
> " Index Cond: (toode = toode.toode)"
> " -> Index Scan using dok_pkey on dok (cost=0.42..7.57
> rows=1 width=4) (never executed)"
> " Index Cond: (dokumnr = rid.dokumnr)"
> " Filter: ((NOT taidetud) AND kinnitatud AND (doktyyp
> = 'T'::bpchar))"
> "Planning Time: 2.102 ms"
> "JIT:"
> " Functions: 24"
> " Options: Inlining true, Optimization true, Expressions true, Deforming
> true"
> " Timing: Generation 5.592 ms, Inlining 95.077 ms, Optimization 463.742
> ms, Emission 277.062 ms, Total 841.473 ms"
> "Execution Time: 666.007 ms"
>
>
on your query there is too slow JIT. Is strange how much. So the best way
is disable JIT probably

set jit to off;

or same field in postgresql.conf

Regards

Pavel

> Same query with sime column expression
>
> 1
>
> run 3.6 times faster:
>
>
> explain analyze select 1
> 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')
>
> "Gather (cost=1000.00..11030.61 rows=153 width=4) (actual
> time=182.414..185.648 rows=0 loops=1)"
> " Workers Planned: 1"
> " Workers Launched: 1"
> " -> Parallel Seq Scan on toode (cost=0.00..10015.31 rows=90 width=4)
> (actual time=155.338..155.339 rows=0 loops=2)"
> " Filter: (((ribakood)::text = 'testmiin'::text) OR (nimetus ~~*
> '%testmiin%'::text) OR (toode ~~* '%testmiin%'::text) OR
> (markused ~~* '%testmiin%'::text) OR (to_tsvector('english'::regconfig,
> (nimetus)::text) @@ plainto_tsquery('testmiin'::text)) OR
> (to_tsvector('english'::regconfig, (engnimetus)::text) @@
> plainto_tsquery('testmiin'::text)))"
> " Rows Removed by Filter: 7202"
> "Planning Time: 1.729 ms"
> "Execution Time: 185.674 ms"
>
> If there are more column expressions, perfomance difference is bigger.
> rid table used in column expression contains 1.8 million of rows.
> Performance degradation probably occured if upgraded from Postgres 9.1 to
> Postgres 12
>
> Since no data is returned query perfomance should be same.
> How to fix it ?
>
> Andrus.
>
>
>