explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lDhQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 0.798 ↑ 1.0 1 1

Aggregate (cost=38,366.17..38,366.18 rows=1 width=8) (actual time=0.798..0.798 rows=1 loops=1)

2.          

CTE rr_inheritance_pc0

3. 0.001 0.028 ↓ 0.0 0 1

WindowAgg (cost=3.48..3.53 rows=1 width=197) (actual time=0.027..0.028 rows=0 loops=1)

4. 0.000 0.027 ↓ 0.0 0 1

WindowAgg (cost=3.48..3.51 rows=1 width=189) (actual time=0.027..0.027 rows=0 loops=1)

5. 0.004 0.027 ↓ 0.0 0 1

Sort (cost=3.48..3.48 rows=1 width=181) (actual time=0.027..0.027 rows=0 loops=1)

  • Sort Key: r.pxinsid, r.pyclass, r.pyclassname, i.pzrulesetindex, r.pyrulesetversion DESC
  • Sort Method: quicksort Memory: 25kB
6. 0.001 0.023 ↓ 0.0 0 1

Nested Loop (cost=0.98..3.47 rows=1 width=181) (actual time=0.023..0.023 rows=0 loops=1)

7. 0.022 0.022 ↓ 0.0 0 1

Index Scan using pr4_rule_vw_idx1 on pr4_rule_vw r (cost=0.42..1.89 rows=1 width=187) (actual time=0.022..0.022 rows=0 loops=1)

  • Index Cond: ((pxobjclass)::text = 'data-rule-summary'::text)
  • Filter: ((pycircumstancetype IS NULL) AND (pyrulestarts IS NULL) AND (pyruleends IS NULL) AND ((pyruleavailable)::text <>'no'::text) AND (pzclasstype = '0'::numeric))
8. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pr_sys_ruleset_index_idx1 on pr_sys_ruleset_index i (cost=0.55..1.57 rows=1 width=30) (never executed)

  • Index Cond: ((pzrulesetlisthash = '753454fcd79e9e1301c6f7e1c42710d8'::text) AND (pzrulesetname = (r.pyruleset)::text) AND(pzrulesetversionmajor = r.pzrulesetversionmajor) AND (pzrulesetversionminorpatch >= (r.pzrulesetversionminorpatch)::text))
  • Heap Fetches: 0
9.          

CTE bc_inheritance_pc0

10. 0.000 0.038 ↓ 0.0 0 1

Subquery Scan on tmp (cost=0.07..0.11 rows=1 width=516) (actual time=0.038..0.038 rows=0 loops=1)

  • Filter: (((tmp.pyruleavailable)::text <> 'blocked'::text) AND (tmp.finalrank = 1))
11. 0.001 0.038 ↓ 0.0 0 1

WindowAgg (cost=0.07..0.10 rows=1 width=590) (actual time=0.038..0.038 rows=0 loops=1)

12. 0.009 0.037 ↓ 0.0 0 1

Sort (cost=0.07..0.08 rows=1 width=582) (actual time=0.037..0.037 rows=0 loops=1)

  • Sort Key: second.group_idx, second.rank_idx
  • Sort Method: quicksort Memory: 25kB
13. 0.000 0.028 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..0.06 rows=1 width=582) (actual time=0.028..0.028 rows=0 loops=1)

  • Join Filter: ((COALESCE(first.rank_idx, '0'::bigint) <= second.rank_idx) AND (first.group_idx = second.group_idx) AND ((first.pyclassname)::text = (second.pyclassname)::text) AND ((first.pyruleset)::text = (second.pyruleset)::text))
  • Filter: (first.rank_idx IS NULL)
14. 0.028 0.028 ↓ 0.0 0 1

CTE Scan on rr_inheritance_pc0 second (cost=0.00..0.02 rows=1 width=1,002) (actual time=0.028..0.028 rows=0 loops=1)

15. 0.000 0.000 ↓ 0.0 0

CTE Scan on rr_inheritance_pc0 first (cost=0.00..0.02 rows=1 width=436) (never executed)

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

CTE rr_wo_inheritance_pc0

17. 0.000 0.018 ↓ 0.0 0 1

WindowAgg (cost=3.48..3.52 rows=1 width=197) (actual time=0.018..0.018 rows=0 loops=1)

18. 0.001 0.018 ↓ 0.0 0 1

WindowAgg (cost=3.48..3.51 rows=1 width=189) (actual time=0.018..0.018 rows=0 loops=1)

19. 0.001 0.017 ↓ 0.0 0 1

Sort (cost=3.48..3.48 rows=1 width=181) (actual time=0.017..0.017 rows=0 loops=1)

  • Sort Key: r_1.pxinsid, r_1.pyclass, i_1.pzrulesetindex, r_1.pyrulesetversion DESC
  • Sort Method: quicksort Memory: 25kB
20. 0.000 0.016 ↓ 0.0 0 1

Nested Loop (cost=0.98..3.47 rows=1 width=181) (actual time=0.016..0.016 rows=0 loops=1)

21. 0.016 0.016 ↓ 0.0 0 1

Index Scan using pr4_rule_vw_idx1 on pr4_rule_vw r_1 (cost=0.42..1.89 rows=1 width=187) (actual time=0.016..0.016 rows=0 loops=1)

  • Index Cond: ((pxobjclass)::text = 'data-rule-summary'::text)
  • Filter: ((pycircumstancetype IS NULL) AND (pyrulestarts IS NULL) AND (pyruleends IS NULL) AND ((pyruleavailable)::text <>'no'::text) AND (pzclasstype = '1'::numeric))
22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pr_sys_ruleset_index_idx1 on pr_sys_ruleset_index i_1 (cost=0.55..1.57 rows=1 width=30) (never executed)

  • Index Cond: ((pzrulesetlisthash = '753454fcd79e9e1301c6f7e1c42710d8'::text) AND (pzrulesetname = (r_1.pyruleset)::text) AND (pzrulesetversionmajor = r_1.pzrulesetversionmajor) AND (pzrulesetversionminorpatch >= (r_1.pzrulesetversionminorpatch)::text))
  • Heap Fetches: 0
23.          

CTE bc_wo_inheritance_pc0

24. 0.000 0.019 ↓ 0.0 0 1

Subquery Scan on tmp_1 (cost=0.07..0.11 rows=1 width=516) (actual time=0.019..0.019 rows=0 loops=1)

  • Filter: (((tmp_1.pyruleavailable)::text <> 'blocked'::text) AND (tmp_1.finalrank = 1))
25. 0.000 0.019 ↓ 0.0 0 1

WindowAgg (cost=0.07..0.09 rows=1 width=590) (actual time=0.019..0.019 rows=0 loops=1)

26. 0.001 0.019 ↓ 0.0 0 1

Sort (cost=0.07..0.08 rows=1 width=582) (actual time=0.019..0.019 rows=0 loops=1)

  • Sort Key: second_1.group_idx, second_1.rank_idx
  • Sort Method: quicksort Memory: 25kB
27. 0.000 0.018 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..0.06 rows=1 width=582) (actual time=0.018..0.018 rows=0 loops=1)

  • Join Filter: ((COALESCE(first_1.rank_idx, '0'::bigint) <= second_1.rank_idx) AND (first_1.group_idx = second_1.group_idx) AND ((first_1.pyruleset)::text = (second_1.pyruleset)::text))
  • Filter: (first_1.rank_idx IS NULL)
28. 0.018 0.018 ↓ 0.0 0 1

CTE Scan on rr_wo_inheritance_pc0 second_1 (cost=0.00..0.02 rows=1 width=856) (actual time=0.018..0.018 rows=0 loops=1)

29. 0.000 0.000 ↓ 0.0 0

CTE Scan on rr_wo_inheritance_pc0 first_1 (cost=0.00..0.02 rows=1 width=290) (never executed)

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

CTE non_rr_rules_pc0

31. 0.712 0.712 ↑ 33,861.0 1 1

Index Scan using pr4_rule_vw_idx6 on pr4_rule_vw r_2 (cost=0.42..36,744.27 rows=33,861 width=86) (actual time=0.712..0.712 rows=1 loops=1)

  • Index Cond: (pzclasstype = '2'::numeric)
  • Filter: ((pycircumstancetype IS NULL) AND (pyrulestarts IS NULL) AND (pyruleends IS NULL) AND ((COALESCE(pyruleavailable, 'yes'::character varying))::text <> 'no'::text))
32.          

CTE resolved_rules_pc0

33. 0.001 0.771 ↑ 33,863.0 1 1

Append (cost=0.00..846.58 rows=33,863 width=516) (actual time=0.771..0.771 rows=1 loops=1)

34. 0.038 0.038 ↓ 0.0 0 1

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

35. 0.019 0.019 ↓ 0.0 0 1

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

36. 0.713 0.713 ↑ 33,861.0 1 1

CTE Scan on non_rr_rules_pc0 (cost=0.00..677.22 rows=33,861 width=516) (actual time=0.713..0.713 rows=1 loops=1)

37. 0.007 0.796 ↓ 0.0 0 1

Hash Join (cost=1.90..768.05 rows=1 width=86) (actual time=0.796..0.796 rows=0 loops=1)

  • Hash Cond: ((resolved_rules_pc0.pzinskey)::text = (pc0.pzinskey)::text)
38. 0.772 0.772 ↑ 33,863.0 1 1

CTE Scan on resolved_rules_pc0 (cost=0.00..677.26 rows=33,863 width=516) (actual time=0.772..0.772 rows=1 loops=1)

39. 0.000 0.017 ↓ 0.0 0 1

Hash (cost=1.89..1.89 rows=1 width=86) (actual time=0.017..0.017 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
40. 0.017 0.017 ↓ 0.0 0 1

Index Scan using pr4_rule_vw_idx1 on pr4_rule_vw pc0 (cost=0.42..1.89 rows=1 width=86) (actual time=0.017..0.017 rows=0 loops=1)

  • Index Cond: ((pxobjclass)::text = 'data-rule-summary'::text)
  • Filter: ((pxcommitdatetime > '2020-09-11 08:12:02.419'::timestamp without time zone) AND (pxcommitdatetime < '2020-09-11 09:12:09.103'::timestamp without time zone))
Planning time : 1.728 ms
Execution time : 0.925 ms