explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 37Eu : With RLS

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 6,382.421 ↑ 1.0 1 1

Result (cost=810,413.44..810,413.45 rows=1 width=32) (actual time=6,382.420..6,382.421 rows=1 loops=1)

2.          

Initplan (for Result)

3. 5.470 6,382.417 ↑ 1.0 1 1

Aggregate (cost=810,413.43..810,413.44 rows=1 width=32) (actual time=6,382.417..6,382.417 rows=1 loops=1)

4. 105.779 6,376.947 ↓ 1.2 80,626 1

Nested Loop (cost=522,849.59..810,239.65 rows=69,509 width=0) (actual time=5,456.966..6,376.947 rows=80,626 loops=1)

  • Join Filter: (CASE WHEN ((t6.question)::text = 'Compliance'::text) THEN t6.result ELSE NULL::double precision END < t9.risk_threshold)
  • Rows Removed by Join Filter: 1,269,585
5. 0.007 0.007 ↑ 1.0 1 1

Index Scan using rule_category_pkey on rule_category t9 (cost=0.15..8.17 rows=1 width=8) (actual time=0.004..0.007 rows=1 loops=1)

  • Index Cond: (id = '2dd5c3bb-527c-4c51-b623-2a2cc1d29527'::uuid)
6. 403.792 6,271.161 ↓ 6.5 1,350,211 1

Hash Right Join (cost=522,849.44..807,103.57 rows=208,528 width=33) (actual time=5,456.916..6,271.161 rows=1,350,211 loops=1)

  • Hash Cond: (t6.parent = t5.id)
7. 461.610 588.121 ↓ 1.0 1,349,277 1

Bitmap Heap Scan on scorecard_result t6 (cost=41,357.31..320,571.39 rows=1,295,650 width=49) (actual time=176.829..588.121 rows=1,349,277 loops=1)

  • Recheck Cond: (((question)::text = 'Communication'::text) OR ((question)::text = 'Compliance'::text) OR ((question)::text = 'Conduct'::text))
  • Heap Blocks: exact=255,703
8. 0.003 126.511 ↓ 0.0 0 1

BitmapOr (cost=41,357.31..41,357.31 rows=1,336,519 width=0) (actual time=126.511..126.511 rows=0 loops=1)

9. 66.840 66.840 ↑ 1.0 449,759 1

Bitmap Index Scan on scorecard_result_question_idx (cost=0.00..13,791.53 rows=456,396 width=0) (actual time=66.840..66.840 rows=449,759 loops=1)

  • Index Cond: ((question)::text = 'Communication'::text)
10. 31.751 31.751 ↑ 1.0 449,759 1

Bitmap Index Scan on scorecard_result_question_idx (cost=0.00..13,717.51 rows=453,994 width=0) (actual time=31.751..31.751 rows=449,759 loops=1)

  • Index Cond: ((question)::text = 'Compliance'::text)
11. 27.917 27.917 ↓ 1.1 449,759 1

Bitmap Index Scan on scorecard_result_question_idx (cost=0.00..12,876.53 rows=426,129 width=0) (actual time=27.916..27.917 rows=449,759 loops=1)

  • Index Cond: ((question)::text = 'Conduct'::text)
12. 87.512 5,279.248 ↓ 2.2 450,693 1

Hash (cost=478,885.53..478,885.53 rows=208,528 width=16) (actual time=5,279.248..5,279.248 rows=450,693 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 25,208kB
13. 198.874 5,191.736 ↓ 2.2 450,693 1

Hash Right Join (cost=209,546.98..478,885.53 rows=208,528 width=16) (actual time=4,723.195..5,191.736 rows=450,693 loops=1)

  • Hash Cond: (t5.scorecard_run_id = t4.id)
14. 319.136 381.924 ↑ 1.0 449,759 1

Bitmap Heap Scan on scorecard_result t5 (cost=15,714.15..281,207.30 rows=469,367 width=32) (actual time=111.395..381.924 rows=449,759 loops=1)

  • Recheck Cond: (parent IS NULL)
  • Heap Blocks: exact=243,080
15. 62.788 62.788 ↑ 1.0 449,854 1

Bitmap Index Scan on scorecard_result_unique_position (cost=0.00..15,596.81 rows=469,367 width=0) (actual time=62.788..62.788 rows=449,854 loops=1)

  • Index Cond: (parent IS NULL)
16. 129.788 4,610.938 ↓ 2.3 450,693 1

Hash (cost=191,335.30..191,335.30 rows=199,802 width=16) (actual time=4,610.938..4,610.938 rows=450,693 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 25,208kB
17. 201.430 4,481.150 ↓ 2.3 450,693 1

Hash Left Join (cost=54,931.60..191,335.30 rows=199,802 width=16) (actual time=423.316..4,481.150 rows=450,693 loops=1)

  • Hash Cond: (t3.id = t4.enriched_transcript_id)
18. 207.906 4,141.436 ↓ 2.3 450,693 1

Hash Left Join (cost=38,381.79..174,261.00 rows=199,802 width=16) (actual time=283.321..4,141.436 rows=450,693 loops=1)

  • Hash Cond: (t2_2.id = t3.transcript_id)
19. 202.980 3,801.847 ↓ 2.3 450,693 1

Hash Left Join (cost=23,148.14..158,502.87 rows=199,802 width=16) (actual time=149.924..3,801.847 rows=450,693 loops=1)

  • Hash Cond: (t0_2.id = t2_2.call_id)
20. 104.420 3,456.742 ↓ 2.4 450,693 1

Hash Join (cost=1,531.65..136,384.69 rows=191,119 width=16) (actual time=6.075..3,456.742 rows=450,693 loops=1)

  • Hash Cond: (t0_2.agent_id = t1_2.id)
21. 3,346.341 3,346.341 ↓ 1.6 450,693 1

Seq Scan on call t0_2 (cost=649.74..134,745.65 rows=286,863 width=32) (actual time=0.090..3,346.341 rows=450,693 loops=1)

  • Filter: ((agent_id IS NOT NULL) AND (began < '2020-07-02 14:00:00+00'::timestamp with time zone) AND (began >= '2019-07-01 14:00:00+00'::timestamp with time zone) AND (myob.lisa_super_user() OR ((hashed SubPlan 1) IS TRUE)))
22.          

SubPlan (for Seq Scan)

23. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=71.08..604.41 rows=18,133 width=16) (never executed)

  • Hash Cond: (((((unnest(regexp_match(t1.c1, '^team_(.+)'::text))))::character varying))::text = (t0.access_control_name)::text)
24. 0.000 0.000 ↓ 0.0 0

Result (cost=0.01..202.01 rows=10,000 width=32) (never executed)

25. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.01..52.01 rows=10,000 width=32) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Function Scan on json_array_elements_text t1 (cost=0.01..1.01 rows=100 width=32) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Hash (cost=54.07..54.07 rows=1,360 width=48) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=26.88..54.07 rows=1,360 width=48) (never executed)

  • Hash Cond: (t2.ac_team_id = t0.id)
29. 0.000 0.000 ↓ 0.0 0

Seq Scan on ac_team_membership t2 (cost=0.00..23.60 rows=1,360 width=32) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Hash (cost=17.50..17.50 rows=750 width=48) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Seq Scan on ac_team t0 (cost=0.00..17.50 rows=750 width=48) (never executed)

32. 0.121 5.981 ↓ 1.5 787 1

Hash (cost=875.44..875.44 rows=517 width=16) (actual time=5.981..5.981 rows=787 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 45kB
33. 5.860 5.860 ↓ 1.5 787 1

Seq Scan on agent t1_2 (cost=649.74..875.44 rows=517 width=16) (actual time=0.050..5.860 rows=787 loops=1)

  • Filter: (myob.lisa_super_user() OR ((hashed SubPlan 2) IS TRUE))
34.          

SubPlan (for Seq Scan)

35. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=71.08..604.41 rows=18,133 width=16) (never executed)

  • Hash Cond: (((((unnest(regexp_match(t1_1.c1, '^team_(.+)'::text))))::character varying))::text = (t0_1.access_control_name)::text)
36. 0.000 0.000 ↓ 0.0 0

Result (cost=0.01..202.01 rows=10,000 width=32) (never executed)

37. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.01..52.01 rows=10,000 width=32) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Function Scan on json_array_elements_text t1_1 (cost=0.01..1.01 rows=100 width=32) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Hash (cost=54.07..54.07 rows=1,360 width=48) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=26.88..54.07 rows=1,360 width=48) (never executed)

  • Hash Cond: (t2_1.ac_team_id = t0_1.id)
41. 0.000 0.000 ↓ 0.0 0

Seq Scan on ac_team_membership t2_1 (cost=0.00..23.60 rows=1,360 width=32) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Hash (cost=17.50..17.50 rows=750 width=48) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Seq Scan on ac_team t0_1 (cost=0.00..17.50 rows=750 width=48) (never executed)

44. 86.930 142.125 ↑ 1.0 449,781 1

Hash (cost=15,993.44..15,993.44 rows=449,844 width=32) (actual time=142.125..142.125 rows=449,781 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,208kB
45. 55.195 55.195 ↑ 1.0 449,781 1

Seq Scan on transcript t2_2 (cost=0.00..15,993.44 rows=449,844 width=32) (actual time=0.004..55.195 rows=449,781 loops=1)

46. 82.977 131.683 ↓ 1.0 449,776 1

Hash (cost=9,612.18..9,612.18 rows=449,718 width=32) (actual time=131.683..131.683 rows=449,776 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,207kB
47. 48.706 48.706 ↓ 1.0 449,776 1

Seq Scan on enriched_transcript t3 (cost=0.00..9,612.18 rows=449,718 width=32) (actual time=0.008..48.706 rows=449,776 loops=1)

48. 87.189 138.284 ↓ 1.0 449,759 1

Hash (cost=10,928.25..10,928.25 rows=449,725 width=32) (actual time=138.284..138.284 rows=449,759 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,206kB
49. 51.095 51.095 ↓ 1.0 449,759 1

Seq Scan on scorecard_run t4 (cost=0.00..10,928.25 rows=449,725 width=32) (actual time=0.008..51.095 rows=449,759 loops=1)

Planning time : 1.989 ms
Execution time : 6,385.916 ms