explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2sQT

Settings
# exclusive inclusive rows x rows loops node
1. 16,311.167 16,311.167 ↑ 507,172.0 3 1

CTE Scan on metrics (cost=517,211.03..547,641.35 rows=1,521,516 width=552) (actual time=16,311.108..16,311.167 rows=3 loops=1)

2.          

CTE facts_fo

3. 3,096.456 16,016.124 ↑ 37,132.8 5 1

GroupAggregate (cost=225,235.18..293,002.54 rows=185,664 width=440) (actual time=13,383.320..16,016.124 rows=5 loops=1)

  • Group Key: (date_part('week'::text, pc_ses.session_end_utc))
4. 951.246 12,919.668 ↓ 3.0 553,226 1

Sort (cost=225,235.18..225,699.34 rows=185,664 width=98) (actual time=12,660.839..12,919.668 rows=553,226 loops=1)

  • Sort Key: (date_part('week'::text, pc_ses.session_end_utc))
  • Sort Method: quicksort Memory: 102,374kB
5. 558.178 11,968.422 ↓ 3.0 553,226 1

Gather (cost=1,005.90..208,987.41 rows=185,664 width=98) (actual time=1.170..11,968.422 rows=553,226 loops=1)

  • Workers Planned: 4
  • Workers Launched: 0
6. 1,321.184 11,410.244 ↓ 11.9 553,226 1

Nested Loop Left Join (cost=5.91..189,072.89 rows=46,416 width=98) (actual time=0.204..11,410.244 rows=553,226 loops=1)

7. 1,813.356 4,556.800 ↓ 11.9 553,226 1

Hash Join (cost=5.34..154,407.96 rows=46,416 width=66) (actual time=0.186..4,556.800 rows=553,226 loops=1)

  • Hash Cond: (((pc_ses.queue_id)::character varying)::text = (cs_enumeration_queue.purecloud_id)::text)
8. 2,743.343 2,743.343 ↓ 3.6 899,247 1

Parallel Index Scan using purecloud_session_conversation_start_idx on purecloud_session pc_ses (cost=0.57..151,430.05 rows=250,898 width=82) (actual time=0.037..2,743.343 rows=899,247 loops=1)

  • Index Cond: (conversation_start_utc >= (CURRENT_DATE - '1 mon'::interval))
  • Filter: (direction = 'inbound'::text)
  • Rows Removed by Filter: 463,950
9. 0.030 0.101 ↓ 1.2 43 1

Hash (cost=4.31..4.31 rows=37 width=36) (actual time=0.101..0.101 rows=43 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
10. 0.071 0.071 ↓ 1.2 43 1

Seq Scan on cs_enumeration_queue (cost=0.00..4.31 rows=37 width=36) (actual time=0.015..0.071 rows=43 loops=1)

  • Filter: (((business_unit)::text = 'Front office'::text) AND ((type_name)::text = ANY ('{customer,internal,partner}'::text[])))
  • Rows Removed by Filter: 99
11. 5,532.260 5,532.260 ↑ 1.0 1 553,226

Index Scan using cs_db_purecloud_session_session_id_idx on cs_db_purecloud_session raw_pc_ses (cost=0.56..0.73 rows=1 width=48) (actual time=0.009..0.010 rows=1 loops=553,226)

  • Index Cond: (pc_ses.session_id = session_id)
12.          

CTE quality_fo

13. 0.000 294.618 ↑ 983.4 5 1

Finalize GroupAggregate (cost=10,528.34..11,223.65 rows=4,917 width=44) (actual time=292.631..294.618 rows=5 loops=1)

  • Group Key: (date_part('week'::text, cs_quality_evaluation.evaluation_date))
14. 0.565 294.632 ↑ 819.6 5 1

Gather Merge (cost=10,528.34..11,057.69 rows=4,098 width=72) (actual time=292.002..294.632 rows=5 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
15. 2.108 294.067 ↑ 409.8 5 1

Partial GroupAggregate (cost=9,528.31..9,584.66 rows=2,049 width=72) (actual time=291.496..294.067 rows=5 loops=1)

  • Group Key: (date_part('week'::text, cs_quality_evaluation.evaluation_date))
16. 8.915 291.959 ↓ 1.5 3,092 1

Sort (cost=9,528.31..9,533.43 rows=2,049 width=18) (actual time=290.752..291.959 rows=3,092 loops=1)

  • Sort Key: (date_part('week'::text, cs_quality_evaluation.evaluation_date))
  • Sort Method: quicksort Memory: 338kB
17. 283.044 283.044 ↓ 1.5 3,092 1

Parallel Seq Scan on cs_quality_evaluation (cost=0.00..9,415.61 rows=2,049 width=18) (actual time=0.137..283.044 rows=3,092 loops=1)

  • Filter: (((eval_channel)::text = ANY ('{Voice,Email}'::text[])) AND ((evaluation_type)::text = ANY ('{assessment,"partner assessment"}'::text[])) AND (evaluation_date >= (CURRENT_DATE - '1 mon'::interval)))
  • Rows Removed by Filter: 200,480
18.          

CTE metrics

19. 0.086 16,311.155 ↑ 507,172.0 3 1

Merge Right Join (cost=11,359.39..212,984.85 rows=1,521,516 width=552) (actual time=16,311.103..16,311.155 rows=3 loops=1)

  • Merge Cond: (quality_fo.week_q = facts_fo.week)
20. 0.014 294.642 ↑ 983.4 5 1

Sort (cost=399.84..412.13 rows=4,917 width=44) (actual time=294.638..294.642 rows=5 loops=1)

  • Sort Key: quality_fo.week_q
  • Sort Method: quicksort Memory: 25kB
21. 294.628 294.628 ↑ 983.4 5 1

CTE Scan on quality_fo (cost=0.00..98.34 rows=4,917 width=44) (actual time=292.634..294.628 rows=5 loops=1)

22. 0.248 16,016.427 ↑ 20,629.3 3 1

Sort (cost=10,959.55..11,114.27 rows=61,888 width=368) (actual time=16,016.426..16,016.427 rows=3 loops=1)

  • Sort Key: facts_fo.week
  • Sort Method: quicksort Memory: 25kB
23. 16,016.179 16,016.179 ↑ 20,629.3 3 1

CTE Scan on facts_fo (cost=0.00..6,034.08 rows=61,888 width=368) (actual time=14,859.267..16,016.179 rows=3 loops=1)

  • Filter: (week > (date_part('week'::text, (CURRENT_DATE)::timestamp without time zone) - '3'::double precision))
  • Rows Removed by Filter: 2
Planning time : 7.833 ms
Execution time : 16,323.845 ms