Ok, I will check.
Now I have even more questions. This behavior appeared, it was not there in the first place. jit-compilation down my long aggregation queries by about half on average. How do I adjust this correctly?
#jit_provider = 'llvmjit' # JIT library to use
#jit_above_cost = 100000 # perform JIT compilation if available
# and query more expensive than this;
# -1 disables
#jit_inline_above_cost = 500000 # inline small functions if query is
# more expensive than this; -1 disables
#jit_optimize_above_cost = 500000 # use expensive JIT optimizations if
# query is more expensive than this;
# -1 disables
jit = off # allow JIT compilation
# and query more expensive than this;
# -1 disables
#jit_inline_above_cost = 500000 # inline small functions if query is
# more expensive than this; -1 disables
#jit_optimize_above_cost = 500000 # use expensive JIT optimizations if
# query is more expensive than this;
# -1 disables
jit = off # allow JIT compilation
--
Regards, Dmitry!сб, 11 дек. 2021 г. в 09:12, Imre Samu <pella.samu@xxxxxxxxx>:
Hi Dmitry,pg12:> Execution Time: 44.123 mspg14:> JIT:
> Functions: 167
> Options: Inlining true, Optimization true, Expressions true, Deforming true
> Timing: Generation 9.468 ms, Inlining 55.237 ms, Optimization 507.548 ms, Emission 347.932 ms, Total 920.185 ms
> Execution Time: 963.258 msplease check the JIT settings ;and test with: jit = off;regards,ImreДмитрий Иванов <firstdismay@xxxxxxxxx> ezt írta (időpont: 2021. dec. 11., Szo, 2:01):Afternoon. I was able to make the necessary changes to my base needed to migrate win_pg12 to debian pg14.
But there is a new problem, which was not there at the initial stage so I checked:
win_pg12:
-> Index Scan using index_class_tree_full on class c (cost=0.28..2.50 rows=1 width=235) (actual time=0.030..0.032 rows=1 loops=1)
Index Cond: (id = 650)
debian_pg14:
-> Index Scan using index_class_tree_full on class c (cost=0.28..2.50 rows=1 width=235) (actual time=842.795..842.796 rows=1 loops=1)
Index Cond: (id = 650)
I'm wondering where to dig next...--Best regards, Dmitry!
Uchet=# EXPLAIN ANALYZE SELECT * FROM bpd.vclass WHERE "id" = 650;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=26599.87..856768.45 rows=4348 width=311) (actual time=38.159..38.182 rows=1 loops=1)
Join Filter: (c_2.id = c.id)
-> Nested Loop Left Join (cost=2.82..96.06 rows=1 width=244) (actual time=3.612..3.621 rows=1 loops=1)
Join Filter: (con.id = c.id_con)
Rows Removed by Join Filter: 1
-> Nested Loop Left Join (cost=2.82..94.68 rows=1 width=236) (actual time=3.606..3.614 rows=1 loops=1)
Join Filter: (c_1.id = c.id)
-> Index Scan using index_class_tree_full on class c (cost=0.28..2.50 rows=1 width=235) (actual time=0.014..0.015 rows=1 loops=1)
Index Cond: (id = 650)
-> GroupAggregate (cost=2.54..92.15 rows=1 width=9) (actual time=3.590..3.595 rows=1 loops=1)
Group Key: c_1.id
-> Nested Loop Left Join (cost=2.54..91.82 rows=1 width=233) (actual time=0.270..3.576 rows=20 loops=1)
-> Nested Loop Left Join (cost=2.26..91.52 rows=1 width=233) (actual time=0.262..3.545 rows=20 loops=1)
-> Nested Loop Left Join (cost=1.97..23.18 rows=1 width=225) (actual time=0.057..0.252 rows=20 loops=1)
-> Nested Loop Left Join (cost=1.70..21.60 rows=1 width=213) (actual time=0.050..0.209 rows=20 loops=1)
-> Nested Loop Left Join (cost=1.42..21.30 rows=1 width=208) (actual time=0.045..0.185 rows=20 loops=1)
-> Nested Loop Left Join (cost=1.13..18.89 rows=1 width=192) (actual time=0.035..0.134 rows=20 loops=1)
-> Nested Loop Left Join (cost=0.86..17.68 rows=1 width=131) (actual time=0.028..0.095 rows=20 loops=1)
-> Nested Loop Left Join (cost=0.57..15.18 rows=1 width=26) (actual time=0.019..0.037 rows=20 loops=1)
Join Filter: (c_1.id = cp.id_class)
-> Index Scan using index_class_tree_full on class c_1 (cost=0.28..2.50 rows=1 width=9) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (id = 650)
-> Index Scan using index_class_prop_class on class_prop cp (cost=0.29..12.53 rows=12 width=25) (actual time=0.009..0.017 rows=20 loops=1)
Index Cond: (id_class = 650)
-> Index Scan using class_prop_small_val_pkey on class_prop_user_small_val sv (cost=0.29..2.51 rows=1 width=113) (actual time=0.002..0.002 rows=0 loops=20)
Index Cond: (id_class_prop = cp.id)
-> Index Scan using class_prop_big_val_pkey on class_prop_user_big_val bv (cost=0.27..1.21 rows=1 width=69) (actual time=0.001..0.001 rows=0 loops=20)
Index Cond: (id_class_prop = cp.id)
-> Index Scan using class_prop_enum_val_pkey on class_prop_enum_val ev (cost=0.29..2.41 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=20)
Index Cond: (id_class_prop = cp.id)
-> Index Scan using prop_enum_val_pkey on prop_enum_val pev (cost=0.28..0.30 rows=1 width=21) (actual time=0.001..0.001 rows=0 loops=20)
Index Cond: (id = ev.id_prop_enum_val)
-> Index Scan using index_class_prop_link_val_class_prop on class_prop_link_val lv (cost=0.28..1.58 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=20)
Index Cond: (id_class_prop = cp.id)
-> Index Scan using unique_id_class_id_class_prop on class_prop_obj_val_class ov (cost=0.29..68.33 rows=1 width=16) (actual time=0.140..0.164 rows=0 loops=20)
Index Cond: (id_class_prop = cp.id)
-> Index Only Scan using class_pkey on class class_val (cost=0.28..0.30 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=20)
Index Cond: (id = ov.id_class_val)
Heap Fetches: 0
-> Seq Scan on conception con (cost=0.00..1.17 rows=17 width=16) (actual time=0.003..0.004 rows=2 loops=1)
-> Hash Right Join (cost=26597.05..26675.98 rows=4348 width=40) (actual time=19.932..19.945 rows=1 loops=1)
Hash Cond: (rc.id_path = c_2.id)
CTE rgroup
-> Recursive Union (cost=0.00..739.33 rows=7390 width=93) (actual time=0.010..0.502 rows=363 loops=1)
-> Seq Scan on "group" rg_1 (cost=0.00..6.12 rows=170 width=93) (actual time=0.009..0.083 rows=170 loops=1)
-> Hash Join (cost=7.83..58.54 rows=722 width=93) (actual time=0.022..0.063 rows=39 loops=5)
Hash Cond: (rgroup_1.id_parent = rgc.id)
-> WorkTable Scan on rgroup rgroup_1 (cost=0.00..34.00 rows=850 width=84) (actual time=0.000..0.010 rows=73 loops=5)
Filter: (NOT cycle)
-> Hash (cost=5.70..5.70 rows=170 width=42) (actual time=0.067..0.068 rows=170 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 21kB
-> Seq Scan on "group" rgc (cost=0.00..5.70 rows=170 width=42) (actual time=0.002..0.027 rows=170 loops=1)
CTE rclass
-> Recursive Union (cost=0.28..19894.27 rows=222309 width=93) (actual time=0.025..12.400 rows=9866 loops=1)
-> Index Only Scan using index_class_tree_full on class rc_1 (cost=0.28..123.87 rows=4359 width=93) (actual time=0.024..1.778 rows=4359 loops=1)
Heap Fetches: 0
-> Hash Join (cost=167.46..1532.42 rows=21795 width=93) (actual time=0.310..1.322 rows=918 loops=6)
Hash Cond: (rclass_1.id_parent = rcc.id)
-> WorkTable Scan on rclass rclass_1 (cost=0.00..871.80 rows=21795 width=84) (actual time=0.000..0.223 rows=1644 loops=6)
Filter: (NOT cycle)
-> Hash (cost=112.97..112.97 rows=4359 width=40) (actual time=1.830..1.831 rows=4359 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 373kB
-> Index Only Scan using index_class_tree_full on class rcc (cost=0.28..112.97 rows=4359 width=40) (actual time=0.009..0.797 rows=4359 loops=1)
Heap Fetches: 0
-> Unique (cost=5136.72..5142.28 rows=1087 width=44) (actual time=18.489..18.491 rows=1 loops=1)
-> Sort (cost=5136.72..5139.50 rows=1112 width=44) (actual time=18.489..18.490 rows=1 loops=1)
Sort Key: (first_value(rc.spath) OVER (?))
Sort Method: quicksort Memory: 25kB
-> WindowAgg (cost=5058.21..5080.45 rows=1112 width=44) (actual time=18.480..18.481 rows=1 loops=1)
-> Sort (cost=5058.21..5060.99 rows=1112 width=44) (actual time=18.476..18.476 rows=1 loops=1)
Sort Key: rc.level DESC
Sort Method: quicksort Memory: 25kB
-> CTE Scan on rclass rc (cost=0.00..5001.95 rows=1112 width=44) (actual time=0.135..18.467 rows=1 loops=1)
Filter: (id_path = 650)
Rows Removed by Filter: 9865
-> Hash (cost=826.68..826.68 rows=4 width=40) (actual time=1.423..1.425 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Right Join (cost=809.92..826.68 rows=4 width=40) (actual time=1.396..1.422 rows=1 loops=1)
Hash Cond: (rg.id_path = c_2.id_group)
-> HashAggregate (cost=807.41..814.80 rows=739 width=44) (actual time=1.346..1.379 rows=170 loops=1)
Group Key: rg.id_path, first_value(rg.spath) OVER (?)
Batches: 1 Memory Usage: 73kB
-> WindowAgg (cost=622.66..770.46 rows=7390 width=44) (actual time=0.910..1.194 rows=363 loops=1)
-> Sort (cost=622.66..641.13 rows=7390 width=44) (actual time=0.903..0.925 rows=363 loops=1)
Sort Key: rg.id_path, rg.level DESC
Sort Method: quicksort Memory: 71kB
-> CTE Scan on rgroup rg (cost=0.00..147.80 rows=7390 width=44) (actual time=0.012..0.756 rows=363 loops=1)
-> Hash (cost=2.50..2.50 rows=1 width=16) (actual time=0.011..0.012 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Index Scan using index_class_tree_full on class c_2 (cost=0.28..2.50 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=1)
Index Cond: (id = 650)
SubPlan 2
-> Index Only Scan using index_class_tree_full on class cc (cost=0.28..112.97 rows=4359 width=8) (actual time=0.011..0.724 rows=4359 loops=1)
Heap Fetches: 0
SubPlan 4
-> Index Only Scan using index_object_class_snapshot on object co (cost=0.29..518.01 rows=31288 width=16) (actual time=0.011..3.496 rows=31288 loops=1)
Heap Fetches: 0
SubPlan 6
-> Seq Scan on class cc_1 (cost=0.00..215.59 rows=3873 width=8) (actual time=0.007..1.078 rows=3873 loops=1)
Filter: (NOT on_abstraction)
Rows Removed by Filter: 486
SubPlan 8
-> Seq Scan on class cc_2 (cost=0.00..215.59 rows=486 width=8) (actual time=0.003..0.574 rows=486 loops=1)
Filter: on_abstraction
Rows Removed by Filter: 3873
SubPlan 9
-> Aggregate (cost=80.43..80.44 rows=1 width=8) (actual time=0.385..0.386 rows=1 loops=1)
-> Index Only Scan using index_class_tree_full on class cc_3 (cost=0.28..80.40 rows=12 width=0) (actual time=0.014..0.297 rows=1438 loops=1)
Index Cond: (id_parent = c.id)
Heap Fetches: 0
Planning Time: 5.082 ms
Execution Time: 39.071 ms
(112 rows)
