explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LHGg

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 19,548.235 ↑ 14.3 14 1

Sort (cost=3,301,529.60..3,301,530.10 rows=200 width=36) (actual time=19,548.234..19,548.235 rows=14 loops=1)

  • Sort Key: i.bucket
  • Sort Method: quicksort Memory: 26kB
2. 265.642 19,548.226 ↑ 14.3 14 1

HashAggregate (cost=3,301,517.95..3,301,521.95 rows=200 width=36) (actual time=19,548.221..19,548.226 rows=14 loops=1)

  • Group Key: i.bucket
3. 87.000 19,282.584 ↓ 1.2 768,428 1

Nested Loop Left Join (cost=651.99..3,288,949.07 rows=628,444 width=53) (actual time=0.152..19,282.584 rows=768,428 loops=1)

4. 0.016 0.016 ↑ 71.4 14 1

Function Scan on generate_series i (cost=0.00..10.00 rows=1,000 width=4) (actual time=0.005..0.016 rows=14 loops=1)

5. 45.893 19,195.568 ↓ 87.0 54,888 14

Nested Loop (cost=651.99..3,282.63 rows=631 width=57) (actual time=0.073..1,371.112 rows=54,888 loops=14)

6. 0.000 16,268.070 ↓ 86.3 13,722 14

Nested Loop (cost=651.43..1,951.93 rows=159 width=24) (actual time=0.055..1,162.005 rows=13,722 loops=14)

7. 4.944 15,692.222 ↓ 86.3 13,723 14

Nested Loop (cost=651.01..1,869.68 rows=159 width=24) (actual time=0.047..1,120.873 rows=13,723 loops=14)

8. 50.137 15,110.900 ↓ 86.9 13,723 14

Nested Loop (cost=650.59..1,789.73 rows=158 width=24) (actual time=0.038..1,079.350 rows=13,723 loops=14)

9. 29.218 14,484.148 ↓ 110.7 13,729 14

Nested Loop (cost=650.16..893.52 rows=124 width=24) (actual time=0.029..1,034.582 rows=13,729 loops=14)

10. 0.014 0.014 ↑ 1.0 1 14

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=14)

11. 14,454.916 14,454.916 ↓ 110.7 13,729 14

Index Scan using call_idx_began on call t0 (cost=650.16..892.26 rows=124 width=24) (actual time=0.026..1,032.494 rows=13,729 loops=14)

  • Index Cond: ((began >= ('{"2020-03-02 00:00:00+11","2020-03-09 00:00:00+11","2020-03-16 00:00:00+11","2020-03-23 00:00:00+11","2020-03-30 00:00:00+11","2020-04-06 00:00:00+10","2020-04-13 00:00:00+10","2020-04-20 00:00:00+10","2020-04-27 00:00:00+10","2020-05-04 00:00:00+10","2020-05-11 00:00:00+10","2020-05-18 00:00:00+10","2020-05-25 00:00:00+10","2020-06-01 00:00:00+10","2020-06-12 00:00:00+10"}'::timestamp with time zone[])[i.bucket]) AND (began >= ('{"2020-03-02 00:00:00+11","2020-03-09 00:00:00+11","2020-03-16 00:00:00+11","2020-03-23 00:00:00+11","2020-03-30 00:00:00+11","2020-04-06 00:00:00+10","2020-04-13 00:00:00+10","2020-04-20 00:00:00+10","2020-04-27 00:00:00+10","2020-05-04 00:00:00+10","2020-05-11 00:00:00+10","2020-05-18 00:00:00+10","2020-05-25 00:00:00+10","2020-06-01 00:00:00+10","2020-06-12 00:00:00+10"}'::timestamp with time zone[])[1]) AND (began < ('{"2020-03-02 00:00:00+11","2020-03-09 00:00:00+11","2020-03-16 00:00:00+11","2020-03-23 00:00:00+11","2020-03-30 00:00:00+11","2020-04-06 00:00:00+10","2020-04-13 00:00:00+10","2020-04-20 00:00:00+10","2020-04-27 00:00:00+10","2020-05-04 00:00:00+10","2020-05-11 00:00:00+10","2020-05-18 00:00:00+10","2020-05-25 00:00:00+10","2020-06-01 00:00:00+10","2020-06-12 00:00:00+10"}'::timestamp with time zone[])[15]))
  • Filter: ((lisa_super_user() OR ((hashed SubPlan 1) IS TRUE)) AND (began < ('{"2020-03-02 00:00:00+11","2020-03-09 00:00:00+11","2020-03-16 00:00:00+11","2020-03-23 00:00:00+11","2020-03-30 00:00:00+11","2020-04-06 00:00:00+10","2020-04-13 00:00:00+10","2020-04-20 00:00:00+10","2020-04-27 00:00:00+10","2020-05-04 00:00:00+10","2020-05-11 00:00:00+10","2020-05-18 00:00:00+10","2020-05-25 00:00:00+10","2020-06-01 00:00:00+10","2020-06-12 00:00:00+10"}'::timestamp with time zone[])[(i.bucket + 1)]))
  • Rows Removed by Filter: 99,098
12.          

SubPlan (for Index Scan)

13. 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)
14. 0.000 0.000 ↓ 0.0 0

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

15. 0.000 0.000 ↓ 0.0 0

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

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

17. 0.000 0.000 ↓ 0.0 0

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

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

20. 0.000 0.000 ↓ 0.0 0

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

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

22. 576.615 576.615 ↑ 1.0 1 192,205

Index Scan using transcript_unique_call_id on transcript t1 (cost=0.42..7.23 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=192,205)

  • Index Cond: (call_id = t0.id)
23. 576.378 576.378 ↑ 1.0 1 192,126

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=192,126)

  • Index Cond: (transcript_id = t1.id)
24. 576.366 576.366 ↑ 1.0 1 192,122

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=192,122)

  • Index Cond: (enriched_transcript_id = t2.id)
25. 2,881.605 2,881.605 ↑ 6.2 4 192,107

Index Scan using scorecard_result_idx_scorecard_run_id_parent on scorecard_result t4 (cost=0.56..8.12 rows=25 width=65) (actual time=0.010..0.015 rows=4 loops=192,107)

  • Index Cond: (scorecard_run_id = t3.id)
  • Filter: ((parent IS NULL) OR ((question)::text = ANY ('{Communication,Compliance,Conduct}'::text[])))
  • Rows Removed by Filter: 29