explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SDL5 : With RBAC

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 28,549.157 ↑ 1.2 27 1

Sort (cost=36,045.00..36,045.08 rows=33 width=36) (actual time=28,549.155..28,549.157 rows=27 loops=1)

  • Sort Key: i.bucket
  • Sort Method: quicksort Memory: 27kB
2. 713.291 28,549.140 ↑ 1.2 27 1

HashAggregate (cost=36,043.50..36,044.16 rows=33 width=36) (actual time=28,549.130..28,549.140 rows=27 loops=1)

  • Group Key: i.bucket
3. 6,758.667 27,835.849 ↓ 77.4 1,602,596 1

Nested Loop (cost=733.86..35,629.54 rows=20,698 width=53) (actual time=30.511..27,835.849 rows=1,602,596 loops=1)

  • Join Filter: ((t0.began >= i.begin) AND (t0.began <= i.""end""))
  • Rows Removed by Join Filter: 70,514,224
4. 371.376 16,269.394 ↓ 283.9 1,602,596 1

Nested Loop (cost=733.86..32,365.47 rows=5,645 width=57) (actual time=30.468..16,269.394 rows=1,602,596 loops=1)

5. 252.086 9,086.985 ↓ 276.1 400,649 1

Nested Loop (cost=733.30..18,931.04 rows=1,451 width=24) (actual time=30.454..9,086.985 rows=400,649 loops=1)

6. 265.534 7,632.901 ↓ 276.1 400,666 1

Nested Loop (cost=732.88..18,180.59 rows=1,451 width=24) (actual time=30.447..7,632.901 rows=400,666 loops=1)

7. 47.218 6,165.357 ↓ 279.4 400,670 1

Nested Loop (cost=732.46..17,454.51 rows=1,434 width=24) (actual time=30.439..6,165.357 rows=400,670 loops=1)

8. 4,483.461 4,511.871 ↓ 280.0 401,567 1

Bitmap Heap Scan on call t0 (cost=732.03..7,451.55 rows=1,434 width=24) (actual time=30.427..4,511.871 rows=401,567 loops=1)

  • Recheck Cond: ((began >= ('{""2019-07-31 14:00:00+00"",""2019-08-04 14:00:00+00"",""2019-08-11 14:00:00+00"",""2019-08-18 14:00:00+00"",""2019-08-25 14:00:00+00"",""2019-09-01 14:00:00+00"",""2019-09-08 14:00:00+00"",""2019-09-15 14:00:00+00"",""2019-09-22 14:00:00+00"",""2019-09-29 14:00:00+00"",""2019-10-06 13:00:00+00"",""2019-10-13 13:00:00+00"",""2019-10-20 13:00:00+00"",""2019-10-27 13:00:00+00"",""2019-11-03 13:00:00+00"",""2019-11-10 13:00:00+00"",""2019-11-17 13:00:00+00"",""2019-11-24 13:00:00+00"",""2019-12-01 13:00:00+00"",""2019-12-08 13:00:00+00"",""2019-12-15 13:00:00+00"",""2019-12-22 13:00:00+00"",""2019-12-29 13:00:00+00"",""2020-01-05 13:00:00+00"",""2020-01-12 13:00:00+00"",""2020-01-19 13:00:00+00"",""2020-01-26 13:00:00+00"",""2020-02-02 13:00:00+00"",""2020-02-09 13:00:00+00"",""2020-02-16 13:00:00+00"",""2020-02-23 13:00:00+00"",""2020-03-01 13:00:00+00"",""2020-03-08 13:00:00+00"",""2020-03-15 13:00:00+00"",""2020-03-22 13:00:00+00"",""2020-03-29 13:00:00+00"",""2020-04-05 14:00:00+00"",""2020-04-12 14:00:00+00"",""2020-04-19 14:00:00+00"",""2020-04-26 14:00:00+00"",""2020-05-03 14:00:00+00"",""2020-05-10 14:00:00+00"",""2020-05-17 14:00:00+00"",""2020-05-24 14:00:00+00"",""2020-05-31 14:00:00+00"",""2020-06-11 14:00:00+00""}'::timestamp with time zone[])[1]) AND (began <= ('{""2019-07-31 14:00:00+00"",""2019-08-04 14:00:00+00"",""2019-08-11 14:00:00+00"",""2019-08-18 14:00:00+00"",""2019-08-25 14:00:00+00"",""2019-09-01 14:00:00+00"",""2019-09-08 14:00:00+00"",""2019-09-15 14:00:00+00"",""2019-09-22 14:00:00+00"",""2019-09-29 14:00:00+00"",""2019-10-06 13:00:00+00"",""2019-10-13 13:00:00+00"",""2019-10-20 13:00:00+00"",""2019-10-27 13:00:00+00"",""2019-11-03 13:00:00+00"",""2019-11-10 13:00:00+00"",""2019-11-17 13:00:00+00"",""2019-11-24 13:00:00+00"",""2019-12-01 13:00:00+00"",""2019-12-08 13:00:00+00"",""2019-12-15 13:00:00+00"",""2019-12-22 13:00:00+00"",""2019-12-29 13:00:00+00"",""2020-01-05 13:00:00+00"",""2020-01-12 13:00:00+00"",""2020-01-19 13:00:00+00"",""2020-01-26 13:00:00+00"",""2020-02-02 13:00:00+00"",""2020-02-09 13:00:00+00"",""2020-02-16 13:00:00+00"",""2020-02-23 13:00:00+00"",""2020-03-01 13:00:00+00"",""2020-03-08 13:00:00+00"",""2020-03-15 13:00:00+00"",""2020-03-22 13:00:00+00"",""2020-03-29 13:00:00+00"",""2020-04-05 14:00:00+00"",""2020-04-12 14:00:00+00"",""2020-04-19 14:00:00+00"",""2020-04-26 14:00:00+00"",""2020-05-03 14:00:00+00"",""2020-05-10 14:00:00+00"",""2020-05-17 14:00:00+00"",""2020-05-24 14:00:00+00"",""2020-05-31 14:00:00+00"",""2020-06-11 14:00:00+00""}'::timestamp with time zone[])[46]))
  • Filter: (lisa_super_user() OR ((hashed SubPlan 1) IS TRUE))
  • Heap Blocks: exact=13,095
9. 28.410 28.410 ↓ 186.7 401,567 1

Bitmap Index Scan on call_idx_began (cost=0.00..81.93 rows=2,151 width=0) (actual time=28.410..28.410 rows=401,567 loops=1)

  • Index Cond: ((began >= ('{""2019-07-31 14:00:00+00"",""2019-08-04 14:00:00+00"",""2019-08-11 14:00:00+00"",""2019-08-18 14:00:00+00"",""2019-08-25 14:00:00+00"",""2019-09-01 14:00:00+00"",""2019-09-08 14:00:00+00"",""2019-09-15 14:00:00+00"",""2019-09-22 14:00:00+00"",""2019-09-29 14:00:00+00"",""2019-10-06 13:00:00+00"",""2019-10-13 13:00:00+00"",""2019-10-20 13:00:00+00"",""2019-10-27 13:00:00+00"",""2019-11-03 13:00:00+00"",""2019-11-10 13:00:00+00"",""2019-11-17 13:00:00+00"",""2019-11-24 13:00:00+00"",""2019-12-01 13:00:00+00"",""2019-12-08 13:00:00+00"",""2019-12-15 13:00:00+00"",""2019-12-22 13:00:00+00"",""2019-12-29 13:00:00+00"",""2020-01-05 13:00:00+00"",""2020-01-12 13:00:00+00"",""2020-01-19 13:00:00+00"",""2020-01-26 13:00:00+00"",""2020-02-02 13:00:00+00"",""2020-02-09 13:00:00+00"",""2020-02-16 13:00:00+00"",""2020-02-23 13:00:00+00"",""2020-03-01 13:00:00+00"",""2020-03-08 13:00:00+00"",""2020-03-15 13:00:00+00"",""2020-03-22 13:00:00+00"",""2020-03-29 13:00:00+00"",""2020-04-05 14:00:00+00"",""2020-04-12 14:00:00+00"",""2020-04-19 14:00:00+00"",""2020-04-26 14:00:00+00"",""2020-05-03 14:00:00+00"",""2020-05-10 14:00:00+00"",""2020-05-17 14:00:00+00"",""2020-05-24 14:00:00+00"",""2020-05-31 14:00:00+00"",""2020-06-11 14:00:00+00""}'::timestamp with time zone[])[1]) AND (began <= ('{""2019-07-31 14:00:00+00"",""2019-08-04 14:00:00+00"",""2019-08-11 14:00:00+00"",""2019-08-18 14:00:00+00"",""2019-08-25 14:00:00+00"",""2019-09-01 14:00:00+00"",""2019-09-08 14:00:00+00"",""2019-09-15 14:00:00+00"",""2019-09-22 14:00:00+00"",""2019-09-29 14:00:00+00"",""2019-10-06 13:00:00+00"",""2019-10-13 13:00:00+00"",""2019-10-20 13:00:00+00"",""2019-10-27 13:00:00+00"",""2019-11-03 13:00:00+00"",""2019-11-10 13:00:00+00"",""2019-11-17 13:00:00+00"",""2019-11-24 13:00:00+00"",""2019-12-01 13:00:00+00"",""2019-12-08 13:00:00+00"",""2019-12-15 13:00:00+00"",""2019-12-22 13:00:00+00"",""2019-12-29 13:00:00+00"",""2020-01-05 13:00:00+00"",""2020-01-12 13:00:00+00"",""2020-01-19 13:00:00+00"",""2020-01-26 13:00:00+00"",""2020-02-02 13:00:00+00"",""2020-02-09 13:00:00+00"",""2020-02-16 13:00:00+00"",""2020-02-23 13:00:00+00"",""2020-03-01 13:00:00+00"",""2020-03-08 13:00:00+00"",""2020-03-15 13:00:00+00"",""2020-03-22 13:00:00+00"",""2020-03-29 13:00:00+00"",""2020-04-05 14:00:00+00"",""2020-04-12 14:00:00+00"",""2020-04-19 14:00:00+00"",""2020-04-26 14:00:00+00"",""2020-05-03 14:00:00+00"",""2020-05-10 14:00:00+00"",""2020-05-17 14:00:00+00"",""2020-05-24 14:00:00+00"",""2020-05-31 14:00:00+00"",""2020-06-11 14:00:00+00""}'::timestamp with time zone[])[46]))
10.          

SubPlan (for Bitmap Heap Scan)

11. 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)
12. 0.000 0.000 ↓ 0.0 0

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

13. 0.000 0.000 ↓ 0.0 0

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

14. 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)

15. 0.000 0.000 ↓ 0.0 0

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

16. 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)
17. 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)

18. 0.000 0.000 ↓ 0.0 0

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

19. 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)

20. 1,606.268 1,606.268 ↑ 1.0 1 401,567

Index Scan using transcript_unique_call_id on transcript t1 (cost=0.42..6.98 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=401,567)

  • Index Cond: (call_id = t0.id)
21. 1,202.010 1,202.010 ↑ 1.0 1 400,670

Index Scan using enriched_transcript_unique_transcript_id on enriched_transcript t2 (cost=0.42..0.51 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=400,670)

  • Index Cond: (transcript_id = t1.id)
22. 1,201.998 1,201.998 ↑ 1.0 1 400,666

Index Scan using scorecard_run_unique_enriched_transcript_id on scorecard_run t3 (cost=0.42..0.52 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=400,666)

  • Index Cond: (enriched_transcript_id = t2.id)
23. 6,811.033 6,811.033 ↑ 6.2 4 400,649

Index Scan using scorecard_result_idx_scorecard_run_id_parent on scorecard_result t4 (cost=0.56..9.01 rows=25 width=65) (actual time=0.012..0.017 rows=4 loops=400,649)

  • Index Cond: (scorecard_run_id = t3.id)
  • Filter: ((parent IS NULL) OR ((question)::text = 'Communication'::text) OR ((question)::text = 'Compliance'::text) OR ((question)::text = 'Conduct'::text))
  • Rows Removed by Filter: 27
24. 4,807.753 4,807.788 ↓ 1.4 45 1,602,596

Materialize (cost=0.00..4.17 rows=33 width=20) (actual time=0.000..0.003 rows=45 loops=1,602,596)

25. 0.005 0.035 ↓ 1.4 45 1

Subquery Scan on i (cost=0.00..4.00 rows=33 width=20) (actual time=0.013..0.035 rows=45 loops=1)

  • Filter: (i.bucket > 0)
  • Rows Removed by Filter: 1
26. 0.018 0.030 ↑ 2.2 46 1

WindowAgg (cost=0.00..2.75 rows=100 width=20) (actual time=0.011..0.030 rows=46 loops=1)

27. 0.012 0.012 ↑ 2.2 46 1

Function Scan on unnest times (cost=0.00..1.00 rows=100 width=16) (actual time=0.008..0.012 rows=46 loops=1)

  • Filter: (t IS NOT NULL)
Planning time : 1.543 ms
Execution time : 28,549.356 ms