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...
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! QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=26599.87..856768.44 rows=4348 width=311) (actual time=931.755..931.773 rows=1 loops=1)
Join Filter: (c_2.id = c.id)
-> Nested Loop Left Join (cost=2.82..96.05 rows=1 width=244) (actual time=844.751..844.757 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.66 rows=1 width=236) (actual time=844.743..844.749 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=842.795..842.796 rows=1 loops=1)
Index Cond: (id = 650)
-> GroupAggregate (cost=2.54..92.14 rows=1 width=9) (actual time=1.943..1.948 rows=1 loops=1)
Group Key: c_1.id
-> Nested Loop Left Join (cost=2.54..91.81 rows=1 width=233) (actual time=0.174..1.939 rows=20 loops=1)
-> Nested Loop Left Join (cost=2.26..91.51 rows=1 width=233) (actual time=0.171..1.917 rows=20 loops=1)
-> Nested Loop Left Join (cost=1.97..23.17 rows=1 width=225) (actual time=0.063..0.197 rows=20 loops=1)
-> Nested Loop Left Join (cost=1.70..21.59 rows=1 width=213) (actual time=0.059..0.173 rows=20 loops=1)
-> Nested Loop Left Join (cost=1.42..21.29 rows=1 width=208) (actual time=0.057..0.154 rows=20 loops=1)
-> Nested Loop Left Join (cost=1.13..18.88 rows=1 width=192) (actual time=0.053..0.121 rows=20 loops=1)
-> Nested Loop Left Join (cost=0.86..17.67 rows=1 width=131) (actual time=0.048..0.096 rows=20 loops=1)
-> Nested Loop Left Join (cost=0.57..15.17 rows=1 width=26) (actual time=0.037..0.049 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.020..0.020 rows=1 loops=1)
Index Cond: (id = 650)
-> Index Scan using index_class_prop_class on class_prop cp (cost=0.29..12.52 rows=12 width=25) (actual time=0.014..0.021 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.001..0.001 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.073..0.086 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.006..0.006 rows=2 loops=1)
-> Hash Right Join (cost=26597.05..26675.98 rows=4348 width=40) (actual time=9.805..9.815 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.007..0.255 rows=363 loops=1)
-> Seq Scan on "group" rg_1 (cost=0.00..6.12 rows=170 width=93) (actual time=0.006..0.043 rows=170 loops=1)
-> Hash Join (cost=7.83..58.54 rows=722 width=93) (actual time=0.012..0.031 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.001..0.006 rows=73 loops=5)
Filter: (NOT cycle)
-> Hash (cost=5.70..5.70 rows=170 width=42) (actual time=0.030..0.030 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.012 rows=170 loops=1)
CTE rclass
-> Recursive Union (cost=0.28..19894.27 rows=222309 width=93) (actual time=0.008..6.109 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.008..0.990 rows=4359 loops=1)
Heap Fetches: 0
-> Hash Join (cost=167.46..1532.42 rows=21795 width=93) (actual time=0.141..0.620 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.108 rows=1644 loops=6)
Filter: (NOT cycle)
-> Hash (cost=112.97..112.97 rows=4359 width=40) (actual time=0.824..0.825 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.004..0.378 rows=4359 loops=1)
Heap Fetches: 0
-> Unique (cost=5136.72..5142.28 rows=1087 width=44) (actual time=9.076..9.078 rows=1 loops=1)
-> Sort (cost=5136.72..5139.50 rows=1112 width=44) (actual time=9.075..9.077 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=9.067..9.068 rows=1 loops=1)
-> Sort (cost=5058.21..5060.99 rows=1112 width=44) (actual time=9.063..9.063 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.072..9.061 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=0.720..0.721 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=0.705..0.719 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=0.681..0.698 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.464..0.606 rows=363 loops=1)
-> Sort (cost=622.66..641.13 rows=7390 width=44) (actual time=0.458..0.470 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.008..0.379 rows=363 loops=1)
-> Hash (cost=2.50..2.50 rows=1 width=16) (actual time=0.008..0.008 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.003..0.003 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=14.407..14.805 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.026..1.876 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=14.486..15.060 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=14.077..14.461 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.212..0.212 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.010..0.173 rows=1438 loops=1)
Index Cond: (id_parent = c.id)
Heap Fetches: 0
Planning Time: 5.296 ms
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 ms
(116 rows) QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------
Nested Loop Left Join (cost=28461.60..1152050.97 rows=5450 width=311) (actual time=40.546..40.568 rows=1 loops=1)
Join Filter: (c_2.id = c.id)
-> Nested Loop Left Join (cost=2.82..96.01 rows=1 width=244) (actual time=2.765..2.772 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.65 rows=1 width=236) (actual time=2.745..2.751 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.030..0.032 rows=1 loops=1)
Index Cond: (id = 650)
-> GroupAggregate (cost=2.54..92.12 rows=1 width=9) (actual time=2.712..2.716 rows=1 loops=1)
Group Key: c_1.id
-> Nested Loop Left Join (cost=2.54..91.79 rows=1 width=233) (actual time=0.330..2.701 rows=20 loops=1)
-> Nested Loop Left Join (cost=2.26..91.47 rows=1 width=233) (actual time=0.317..2.649 rows=20 loops=1)
-> Nested Loop Left Join (cost=1.97..23.15 rows=1 width=225) (actual time=0.078..0.303 rows=20 loops=1)
-> Nested Loop Left Join (cost=1.70..21.57 rows=1 width=213) (actual time=0.070..0.271 rows=20 loops=1)
-> Nested Loop Left Join (cost=1.42..21.27 rows=1 width=208) (actual time=0.063..0.241 rows=20 loops=1)
-> Nested Loop Left Join (cost=1.13..18.86 rows=1 width=192) (actual time=0.058..0.186 rows=20 loops=1)
-> Nested Loop Left Join (cost=0.86..17.65 rows=1 width=131) (actual time=0.049..0.152 rows=20 loops=1)
-> Nested Loop Left Join (cost=0.57..15.15 rows=1 width=26) (actual time=0.030..0.059 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.003..0.003 rows=1 loops=1)
Index Cond: (id = 650)
-> Index Scan using index_class_prop_class on class_prop cp (cost=0.29..12.50 rows=12 width=25) (actual time=0.026..0.050 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.004..0.004 rows=1
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.31 rows=1 width=16) (actual time=0.101..0.117 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.32 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=20)
Index Cond: (id = ov.id_class_val)
Heap Fetches: 3
-> Seq Scan on conception con (cost=0.00..1.16 rows=16 width=16) (actual time=0.018..0.018 rows=2 loops=1)
-> Hash Right Join (cost=28458.77..28552.91 rows=5450 width=40) (actual time=17.497..17.509 rows=1 loops=1)
Hash Cond: (rc.id_path = c_2.id)
CTE rgroup
-> Recursive Union (cost=0.00..959.64 rows=10506 width=93) (actual time=0.024..0.497 rows=363 loops=1)
-> Seq Scan on "group" rg_1 (cost=0.00..7.58 rows=206 width=93) (actual time=0.022..0.111 rows=170 loops=1)
-> Hash Join (cost=9.64..74.19 rows=1030 width=93) (actual time=0.021..0.062 rows=39 loops=5)
Hash Cond: (rgroup_1.id_parent = rgc.id)
-> WorkTable Scan on rgroup rgroup_1 (cost=0.00..41.20 rows=1030 width=84) (actual time=0.001..0.009 rows=73 loops=5)
Filter: (NOT cycle)
-> Hash (cost=7.06..7.06 rows=206 width=42) (actual time=0.055..0.056 rows=170 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 21kB
-> Seq Scan on "group" rgc (cost=0.00..7.06 rows=206 width=42) (actual time=0.007..0.027 rows=170 loops=1)
CTE rclass
-> Recursive Union (cost=0.00..21146.17 rows=222972 width=93) (actual time=0.015..12.254 rows=9866 loops=1)
-> Seq Scan on class rc_1 (cost=0.00..232.65 rows=4372 width=93) (actual time=0.014..2.373 rows=4359 loops=1)
-> Hash Join (cost=276.37..1645.41 rows=21860 width=93) (actual time=0.212..1.301 rows=918 loops=6)
Hash Cond: (rclass_1.id_parent = rcc.id)
-> WorkTable Scan on rclass rclass_1 (cost=0.00..874.40 rows=21860 width=84) (actual time=0.000..0.233 rows=1644 loops=6)
Filter: (NOT cycle)
-> Hash (cost=221.72..221.72 rows=4372 width=40) (actual time=1.233..1.233 rows=4359 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 373kB
-> Seq Scan on class rcc (cost=0.00..221.72 rows=4372 width=40) (actual time=0.010..0.603 rows=4359 loops=1)
-> Unique (cost=5152.04..5157.61 rows=1090 width=44) (actual time=16.146..16.148 rows=1 loops=1)
-> Sort (cost=5152.04..5154.83 rows=1115 width=44) (actual time=16.146..16.147 rows=1 loops=1)
Sort Key: (first_value(rc.spath) OVER (?))
Sort Method: quicksort Memory: 25kB
-> WindowAgg (cost=5073.30..5095.60 rows=1115 width=44) (actual time=16.120..16.122 rows=1 loops=1)
-> Sort (cost=5073.30..5076.09 rows=1115 width=44) (actual time=16.115..16.116 rows=1 loops=1)
Sort Key: rc.level DESC
Sort Method: quicksort Memory: 25kB
-> CTE Scan on rclass rc (cost=0.00..5016.87 rows=1115 width=44) (actual time=3.976..16.106 rows=1 loops=1)
Filter: (id_path = 650)
Rows Removed by Filter: 9865
-> Hash (cost=1200.86..1200.86 rows=5 width=40) (actual time=1.331..1.333 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Right Join (cost=1177.03..1200.86 rows=5 width=40) (actual time=1.296..1.324 rows=1 loops=1)
Hash Cond: (rg.id_path = c_2.id_group)
-> HashAggregate (cost=1174.51..1185.02 rows=1051 width=44) (actual time=1.265..1.292 rows=170 loops=1)
Group Key: rg.id_path, first_value(rg.spath) OVER (?)
-> WindowAgg (cost=911.86..1121.98 rows=10506 width=44) (actual time=0.856..1.133 rows=363 loops=1)
-> Sort (cost=911.86..938.13 rows=10506 width=44) (actual time=0.851..0.867 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..210.12 rows=10506 width=44) (actual time=0.026..0.692 rows=363 loops=1)
-> Hash (cost=2.50..2.50 rows=1 width=16) (actual time=0.012..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.006..0.006 rows=1 loops=1)
Index Cond: (id = 650)
SubPlan 1
-> Index Only Scan using index_class_tree_full on class cc (cost=0.28..93.38 rows=12 width=0) (never executed)
Index Cond: (id_parent = c.id)
Heap Fetches: 0
SubPlan 2
-> Seq Scan on class cc_1 (cost=0.00..221.72 rows=4372 width=8) (actual time=0.022..0.555 rows=4359 loops=1)
SubPlan 3
-> Index Only Scan using index_object_class_snapshot on object co (cost=0.29..2.51 rows=1 width=0) (never executed)
Index Cond: ((id_class = c.id) AND (timestamp_class = c."timestamp"))
Heap Fetches: 0
SubPlan 4
-> Seq Scan on object co_1 (cost=0.00..1608.28 rows=31328 width=16) (actual time=0.022..10.509 rows=31326 loops=1)
SubPlan 5
-> Index Scan using index_class_tree_full on class cc_2 (cost=0.28..93.38 rows=11 width=0) (never executed)
Index Cond: (id_parent = c.id)
Filter: (NOT on_abstraction)
SubPlan 6
-> Seq Scan on class cc_3 (cost=0.00..221.72 rows=3889 width=8) (actual time=0.022..0.816 rows=3873 loops=1)
Filter: (NOT on_abstraction)
Rows Removed by Filter: 486
SubPlan 7
-> Index Scan using index_class_tree_full on class cc_4 (cost=0.28..93.38 rows=1 width=0) (never executed)
Index Cond: (id_parent = c.id)
Filter: on_abstraction
SubPlan 8
-> Seq Scan on class cc_5 (cost=0.00..221.72 rows=483 width=8) (actual time=0.008..0.525 rows=486 loops=1)
Filter: on_abstraction
Rows Removed by Filter: 3873
SubPlan 9
-> Aggregate (cost=93.41..93.42 rows=1 width=8) (actual time=0.618..0.619 rows=1 loops=1)
-> Index Only Scan using index_class_tree_full on class cc_6 (cost=0.28..93.38 rows=12 width=0) (actual time=0.018..0.556 rows=1438 loops=1)
Index Cond: (id_parent = c.id)
Heap Fetches: 1438
Planning Time: 22.607 ms
Execution Time: 44.123 ms
(123 rows)
