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)