explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZZI4

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 886.780 ↑ 2.5 2 1

Limit (cost=821.12..821.13 rows=5 width=692) (actual time=886.780..886.780 rows=2 loops=1)

  • Execution Time: 895.366 ms +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2.          

CTE base

3. 886.632 886.632 ↑ 7.7 3 1

CTE Scan on resource r_1 (cost=730.70..731.16 rows=23 width=168) (actual time=786.570..886.632 rows=3 loops=1)

4.          

CTE filterRecord

5. 14.038 95.241 ↓ 840.2 18,484 1

Nested Loop (cost=47.02..70.93 rows=22 width=1,444) (actual time=39.402..95.241 rows=18,484 loops=1)

6. 9.485 44.235 ↓ 840.2 18,484 1

HashAggregate (cost=46.61..46.83 rows=22 width=8) (actual time=39.388..44.235 rows=18,484 loops=1)

  • Group Key: rh.id
7. 1.913 34.750 ↓ 840.2 18,484 1

Nested Loop (cost=0.83..46.55 rows=22 width=8) (actual time=0.113..34.750 rows=18,484 loops=1)

8. 0.017 0.017 ↑ 1.0 1 1

Index Scan using akas_aka_key on akas (cost=0.41..2.63 rows=1 width=8) (actual time=0.015..0.017 rows=1 loops=1)

  • Index Cond: (aka = '166539720203265'::text)
9. 32.820 32.820 ↓ 840.2 18,484 1

Index Scan using resources_path_idx on resources rh (cost=0.41..43.70 rows=22 width=118) (actual time=0.089..32.820 rows=18,484 loops=1)

  • Index Cond: (((('*.'::text || (akas.resource_id)::text) || '.*'::text))::lquery ~ path)
10. 36.968 36.968 ↑ 1.0 1 18,484

Index Scan using resources_pkey on resources (cost=0.41..1.10 rows=1 width=1,444) (actual time=0.002..0.002 rows=1 loops=18,484)

  • Index Cond: (id = rh.id)
11.          

CTE targets

12. 3.919 168.577 ↓ 840.2 18,484 1

Nested Loop (cost=0.14..23.77 rows=22 width=88) (actual time=39.414..168.577 rows=18,484 loops=1)

13. 146.174 146.174 ↓ 840.2 18,484 1

CTE Scan on "filterRecord" (cost=0.00..0.44 rows=22 width=80) (actual time=39.407..146.174 rows=18,484 loops=1)

14. 18.484 18.484 ↑ 1.0 1 18,484

Index Scan using resource_types_pkey on resource_types rt (cost=0.14..1.06 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=18,484)

  • Index Cond: (id = "filterRecord".resource_type_id)
15.          

CTE pivots

16. 1.921 196.819 ↓ 840.2 18,484 1

Nested Loop (cost=0.14..23.77 rows=22 width=468) (actual time=39.421..196.819 rows=18,484 loops=1)

17. 176.414 176.414 ↓ 840.2 18,484 1

CTE Scan on targets (cost=0.00..0.44 rows=22 width=16) (actual time=39.415..176.414 rows=18,484 loops=1)

18. 18.484 18.484 ↑ 1.0 1 18,484

Index Scan using resource_types_pkey on resource_types t (cost=0.14..1.06 rows=1 width=460) (actual time=0.001..0.001 rows=1 loops=18,484)

  • Index Cond: (id = targets.resource_type_id)
19.          

CTE lca

20. 0.000 185.127 ↑ 1.0 1 1

Unique (cost=0.44..0.45 rows=1 width=32) (actual time=185.127..185.127 rows=1 loops=1)

21.          

Initplan (for Unique)

22. 181.799 181.799 ↓ 840.2 18,484 1

CTE Scan on pivots (cost=0.00..0.44 rows=22 width=32) (actual time=0.001..181.799 rows=18,484 loops=1)

23. 185.125 185.125 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=185.125..185.125 rows=1 loops=1)

24.          

CTE nodes

25. 59.818 244.658 ↓ 840.2 18,484 1

CTE Scan on pivots pivots_1 (cost=0.00..2.04 rows=22 width=360) (actual time=224.562..244.658 rows=18,484 loops=1)

26.          

SubPlan (for CTE Scan)

27. 184.840 184.840 ↑ 1.0 1 18,484

CTE Scan on lca lca_1 (cost=0.03..0.07 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=18,484)

  • Filter: (pivots_1.path <@ lca)
28.          

Initplan (for CTE Scan)

29. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=0.02..0.03 rows=1 width=8) (never executed)

30. 0.000 0.000 ↓ 0.0 0

CTE Scan on lca (cost=0.00..0.02 rows=1 width=0) (never executed)

31.          

CTE resource_temp

32. 89.473 537.833 ↑ 7.7 3 1

GroupAggregate (cost=185.19..191.00 rows=23 width=192) (actual time=437.795..537.833 rows=3 loops=1)

  • Group Key: t_1.lca
  • Group Key: ()
33. 46.811 448.360 ↓ 675.6 93,231 1

Sort (cost=185.19..185.54 rows=138 width=52) (actual time=434.972..448.360 rows=93,231 loops=1)

  • Sort Key: t_1.lca
  • Sort Method: external merge Disk: 5,656kB
34. 20.166 401.549 ↓ 675.6 93,231 1

Nested Loop Left Join (cost=0.42..180.29 rows=138 width=52) (actual time=224.582..401.549 rows=93,231 loops=1)

35. 270.479 270.479 ↓ 840.2 18,484 1

CTE Scan on nodes t_1 (cost=0.00..0.44 rows=22 width=40) (actual time=224.565..270.479 rows=18,484 loops=1)

36. 110.904 110.904 ↑ 1.2 5 18,484

Index Scan using controls_resource_id_idx on controls c (cost=0.42..8.11 rows=6 width=20) (actual time=0.003..0.006 rows=5 loops=18,484)

  • Index Cond: (resource_id = t_1.resource_id)
37.          

CTE resource_updated

38. 3.304 348.760 ↑ 7.7 3 1

GroupAggregate (cost=0.93..406.37 rows=23 width=40) (actual time=32.253..348.760 rows=3 loops=1)

  • Group Key: t_2.lca
  • Group Key: ()
39. 6.165 12.744 ↓ 840.2 18,484 1

Sort (cost=0.93..0.99 rows=22 width=40) (actual time=10.796..12.744 rows=18,484 loops=1)

  • Sort Key: t_2.lca
  • Sort Method: quicksort Memory: 2,213kB
40. 6.579 6.579 ↓ 840.2 18,484 1

CTE Scan on nodes t_2 (cost=0.00..0.44 rows=22 width=40) (actual time=0.021..6.579 rows=18,484 loops=1)

41.          

SubPlan (for GroupAggregate)

42. 18.484 332.712 ↑ 1.0 1 18,484

Limit (cost=18.41..18.41 rows=1 width=12) (actual time=0.018..0.018 rows=1 loops=18,484)

43. 36.968 314.228 ↑ 18.0 1 18,484

Sort (cost=18.41..18.45 rows=18 width=12) (actual time=0.017..0.017 rows=1 loops=18,484)

  • Sort Key: rh_1.version_open_timestamp DESC
  • Sort Method: quicksort Memory: 25kB
44. 277.260 277.260 ↑ 1.1 17 18,484

Index Scan using resources_history_id_idx on resources_history rh_1 (cost=0.43..18.32 rows=18 width=12) (actual time=0.003..0.015 rows=17 loops=18,484)

  • Index Cond: (id = t_2.resource_id)
45.          

CTE resource

46. 537.850 886.627 ↑ 7.7 3 1

CTE Scan on resource_temp r (cost=0.00..12.36 rows=23 width=168) (actual time=786.569..886.627 rows=3 loops=1)

47.          

SubPlan (for CTE Scan)

48. 348.777 348.777 ↑ 1.0 1 3

CTE Scan on resource_updated ru (cost=0.00..0.52 rows=1 width=8) (actual time=10.754..116.259 rows=1 loops=3)

  • Filter: (COALESCE(lca, '0'::ltree) = COALESCE(r.lca, '0'::ltree))
  • Rows Removed by Filter: 2
49.          

CTE final

50. 0.072 0.120 ↑ 7.7 3 1

Nested Loop Left Join (cost=0.55..88.38 rows=23 width=1,164) (actual time=0.076..0.120 rows=3 loops=1)

51. 0.007 0.033 ↑ 7.7 3 1

Nested Loop Left Join (cost=0.14..28.45 rows=23 width=176) (actual time=0.023..0.033 rows=3 loops=1)

52. 0.002 0.002 ↑ 7.7 3 1

CTE Scan on base base_1 (cost=0.00..0.46 rows=23 width=168) (actual time=0.001..0.002 rows=3 loops=1)

53. 0.024 0.024 ↑ 1.0 1 3

Index Scan using resource_types_path_idx on resource_types rt_1 (cost=0.14..1.21 rows=1 width=79) (actual time=0.008..0.008 rows=1 loops=3)

  • Index Cond: (path = base_1.base_id)
54. 0.015 0.015 ↑ 1.0 1 3

Index Scan using resources_pkey on resources r_2 (cost=0.41..2.59 rows=1 width=1,444) (actual time=0.005..0.005 rows=1 loops=3)

  • Index Cond: (rt_1.id = id)
55. 0.008 886.779 ↑ 11.5 2 1

Sort (cost=1.59..1.65 rows=23 width=692) (actual time=886.779..886.779 rows=2 loops=1)

  • Sort Key: final.total DESC NULLS LAST, final.id DESC NULLS LAST
  • Sort Method: quicksort Memory: 27kB
56. 0.004 886.771 ↑ 11.5 2 1

Nested Loop (cost=0.00..1.21 rows=23 width=692) (actual time=886.723..886.771 rows=2 loops=1)

57. 886.639 886.639 ↑ 1.0 1 1

CTE Scan on base (cost=0.00..0.52 rows=1 width=72) (actual time=886.639..886.639 rows=1 loops=1)

  • Filter: (subtotals = 'Grand Total'::text)
  • Rows Removed by Filter: 2
58. 0.128 0.128 ↑ 11.5 2 1

CTE Scan on final (cost=0.00..0.46 rows=23 width=620) (actual time=0.081..0.128 rows=2 loops=1)

  • Filter: (id IS NOT NULL)
  • Rows Removed by Filter: 1
Planning time : 1.258 ms