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)