explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wFtP : to char

Settings
# exclusive inclusive rows x rows loops node
1. 31,772.247 31,772.247 ↑ 20,636.0 3 1

CTE Scan on metrics (cost=308,573.72..309,811.88 rows=61,908 width=484) (actual time=30,869.992..31,772.247 rows=3 loops=1)

2.          

CTE facts_fo

3. 2,511.074 31,772.078 ↑ 37,144.8 5 1

GroupAggregate (cost=226,236.08..294,953.96 rows=185,724 width=436) (actual time=29,629.603..31,772.078 rows=5 loops=1)

  • Group Key: ((to_char(pc_ses.session_end_utc, 'IW'::text))::integer)
4. 789.498 29,261.004 ↓ 3.0 555,586 1

Sort (cost=226,236.08..226,700.39 rows=185,724 width=94) (actual time=29,047.259..29,261.004 rows=555,586 loops=1)

  • Sort Key: ((to_char(pc_ses.session_end_utc, 'IW'::text))::integer)
  • Sort Method: quicksort Memory: 102,706kB
5. 0.000 28,471.506 ↓ 3.0 555,586 1

Gather (cost=1,005.90..209,982.63 rows=185,724 width=94) (actual time=5.376..28,471.506 rows=555,586 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
6. 433.407 28,658.970 ↓ 2.4 111,117 5 / 5

Nested Loop Left Join (cost=5.91..189,365.54 rows=46,431 width=94) (actual time=5.307..28,658.970 rows=111,117 loops=5)

7. 329.895 1,112.966 ↓ 2.4 111,117 5 / 5

Hash Join (cost=5.34..154,457.92 rows=46,431 width=66) (actual time=2.012..1,112.966 rows=111,117 loops=5)

  • Hash Cond: (((pc_ses.queue_id)::character varying)::text = (cs_enumeration_queue.purecloud_id)::text)
8. 782.980 782.980 ↑ 1.4 180,596 5 / 5

Parallel Index Scan using purecloud_session_conversation_start_idx on purecloud_session pc_ses (cost=0.57..151,479.05 rows=250,979 width=82) (actual time=1.831..782.980 rows=180,596 loops=5)

  • Index Cond: (conversation_start_utc >= (CURRENT_DATE - '1 mon'::interval))
  • Filter: (direction = 'inbound'::text)
  • Rows Removed by Filter: 93,151
9. 0.030 0.091 ↓ 1.2 43 5 / 5

Hash (cost=4.31..4.31 rows=37 width=36) (actual time=0.090..0.091 rows=43 loops=5)

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

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

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

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.243..0.244 rows=1 loops=555,586)

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

CTE metrics

13. 31,772.234 31,772.234 ↑ 20,636.0 3 1

CTE Scan on facts_fo (cost=0.00..13,619.76 rows=61,908 width=484) (actual time=30,869.988..31,772.234 rows=3 loops=1)

  • Filter: (week > ((to_char((CURRENT_DATE)::timestamp with time zone, 'IW'::text))::integer - 3))
  • Rows Removed by Filter: 2
Planning time : 63.016 ms
Execution time : 31,783.659 ms