explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a571

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Aggregate (cost=105,028.56..105,028.57 rows=1 width=8) (actual rows= loops=)

2.          

CTE rr_inheritance_pc0

3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=7,710.11..9,157.95 rows=30,481 width=196) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

WindowAgg (cost=7,710.11..8,548.33 rows=30,481 width=188) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Sort (cost=7,710.11..7,786.31 rows=30,481 width=180) (actual rows= loops=)

  • Sort Key: r.pxinsid, r.pyclass, r.pyclassname, i.pzrulesetindex, r.pyrulesetversion DESC
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.98..5,439.94 rows=30,481 width=180) (actual rows= loops=)

  • -> Index Only Scan using pr_sys_ruleset_index_idx1 on pr_sys_ruleset_index i (cost=0.55..10.77.
  • Index Cond: (pzrulesetlisthash = '753454fcd79e9e1301c6f7e1c42710d8'::text)
7. 0.000 0.000 ↓ 0.0

Index Scan using pr4_rule_vw_idx5 on pr4_rule_vw r (cost=0.42..42.33 rows=42 width=186) (actual rows= loops=)

  • Index Cond: (((pyruleset)::text = (i.pzrulesetname)::text) AND (pzclasstype = '0'::numeric.
  • Filter: ((pycircumstancetype IS NULL) AND (pyrulestarts IS NULL) AND (pyruleends IS NULL) .
8.          

CTE bc_inheritance_pc0

9. 0.000 0.000 ↓ 0.0

Subquery Scan on tmp (cost=3,246.79..3,252.11 rows=1 width=516) (actual rows= loops=)

  • Filter: (((tmp.pyruleavailable)::text <> 'Blocked'::text) AND (tmp."finalRank" = 1))
10. 0.000 0.000 ↓ 0.0

WindowAgg (cost=3,246.79..3,249.83 rows=152 width=590) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Sort (cost=3,246.79..3,247.17 rows=152 width=582) (actual rows= loops=)

  • Sort Key: second.group_idx, second.rank_idx
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=688.48..3,241.28 rows=152 width=582) (actual rows= loops=)

  • Hash Cond: ((second.group_idx = first.group_idx) AND ((second.pyclassname)::text = (first.pyclas.
  • Join Filter: (COALESCE(first.rank_idx, '0'::bigint) <= second.rank_idx)
  • Filter: (first.rank_idx IS NULL)
13. 0.000 0.000 ↓ 0.0

CTE Scan on rr_inheritance_pc0 second (cost=0.00..609.62 rows=30,481 width=1,002) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash (cost=685.82..685.82 rows=152 width=436) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

CTE Scan on rr_inheritance_pc0 first (cost=0.00..685.82 rows=152 width=436) (actual rows= loops=)

  • Filter: ((pyruleavailable)::text = 'Withdrawn'::text)
16.          

CTE rr_wo_inheritance_pc0

17. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1,038.08..1,177.10 rows=3,271 width=196) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

WindowAgg (cost=1,038.08..1,119.86 rows=3,271 width=188) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Sort (cost=1,038.08..1,046.26 rows=3,271 width=180) (actual rows= loops=)

  • Sort Key: r_1.pxinsid, r_1.pyclass, i_1.pzrulesetindex, r_1.pyrulesetversion DESC
20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.98..847.13 rows=3,271 width=180) (actual rows= loops=)

  • -> Index Only Scan using pr_sys_ruleset_index_idx1 on pr_sys_ruleset_index i_1 (cost=0.55..10..
  • Index Cond: (pzrulesetlisthash = '753454fcd79e9e1301c6f7e1c42710d8'::text)
21. 0.000 0.000 ↓ 0.0

Index Scan using pr4_rule_vw_idx5 on pr4_rule_vw r_1 (cost=0.42..6.54 rows=5 width=186) (actual rows= loops=)

  • Index Cond: (((pyruleset)::text = (i_1.pzrulesetname)::text) AND (pzclasstype = '1'::numer.
  • Filter: ((pycircumstancetype IS NULL) AND (pyrulestarts IS NULL) AND (pyruleends IS NULL) .
22.          

CTE bc_wo_inheritance_pc0

23. 0.000 0.000 ↓ 0.0

Subquery Scan on tmp_1 (cost=172.30..172.86 rows=1 width=516) (actual rows= loops=)

  • Filter: (((tmp_1.pyruleavailable)::text <> 'Blocked'::text) AND (tmp_1."finalRank" = 1))
24. 0.000 0.000 ↓ 0.0

WindowAgg (cost=172.30..172.62 rows=16 width=590) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Sort (cost=172.30..172.34 rows=16 width=582) (actual rows= loops=)

  • Sort Key: second_1.group_idx, second_1.rank_idx
26. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=73.84..171.98 rows=16 width=582) (actual rows= loops=)

  • Hash Cond: ((second_1.group_idx = first_1.group_idx) AND ((second_1.pyruleset)::text = (first_1..
  • Join Filter: (COALESCE(first_1.rank_idx, '0'::bigint) <= second_1.rank_idx)
  • Filter: (first_1.rank_idx IS NULL)
27. 0.000 0.000 ↓ 0.0

CTE Scan on rr_wo_inheritance_pc0 second_1 (cost=0.00..65.42 rows=3,271 width=856) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash (cost=73.60..73.60 rows=16 width=290) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

CTE Scan on rr_wo_inheritance_pc0 first_1 (cost=0.00..73.60 rows=16 width=290) (actual rows= loops=)

  • Filter: ((pyruleavailable)::text = 'Withdrawn'::text)
30.          

CTE non_rr_rules_pc0

31. 0.000 0.000 ↓ 0.0

Index Scan using pr4_rule_vw_idx6 on pr4_rule_vw r_2 (cost=0.42..37,554.68 rows=35,366 width=85) (actual rows= loops=)

  • Index Cond: (pzclasstype = '2'::numeric)
  • Filter: ((pycircumstancetype IS NULL) AND (pyrulestarts IS NULL) AND (pyruleends IS NULL) AND ((COALESCE(pyruleava.
32.          

CTE resolved_rules_pc0

33. 0.000 0.000 ↓ 0.0

Append (cost=0.00..884.20 rows=35,368 width=516) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

CTE Scan on bc_inheritance_pc0 (cost=0.00..0.02 rows=1 width=516) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

CTE Scan on bc_wo_inheritance_pc0 (cost=0.00..0.02 rows=1 width=516) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

CTE Scan on non_rr_rules_pc0 (cost=0.00..707.32 rows=35,366 width=516) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,000.00..52,829.66 rows=1 width=85) (actual rows= loops=)

  • Join Filter: (("PC0".pzinskey)::text = (resolved_rules_pc0.pzinskey)::text)
38. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..51,680.20 rows=1 width=85) (actual rows= loops=)

  • Workers Planned: 2
39. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on pr4_rule_vw "PC0" (cost=0.00..50,680.10 rows=1 width=85) (actual rows= loops=)

  • Filter: ((pxcommitdatetime > '2020-09-04 08:53:24.233'::timestamp without time zone) AND (pxcommitdateti.
40. 0.000 0.000 ↓ 0.0

CTE Scan on resolved_rules_pc0 (cost=0.00..707.36 rows=35,368 width=516) (actual rows= loops=)