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]

 





út 7. 4. 2020 v 18:47 odesílatel Andrus <kobruleht2@xxxxxx> 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.



[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