explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oLNP : extract

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

CTE Scan on metrics (cost=305,787.56..307,025.72 rows=61,908 width=488) (actual time=2,789.013..3,471.127 rows=3 loops=1)

2.          

CTE facts_fo

3. 1,802.147 3,471.009 ↑ 37,144.8 5 1

GroupAggregate (cost=225,307.16..293,096.42 rows=185,724 width=440) (actual time=1,904.478..3,471.009 rows=5 loops=1)

  • Group Key: (date_part('week'::text, pc_ses.session_end_utc))
4. 558.944 1,668.862 ↓ 3.0 555,586 1

Sort (cost=225,307.16..225,771.47 rows=185,724 width=98) (actual time=1,502.461..1,668.862 rows=555,586 loops=1)

  • Sort Key: (date_part('week'::text, pc_ses.session_end_utc))
  • Sort Method: quicksort Memory: 102,706kB
5. 0.000 1,109.918 ↓ 3.0 555,586 1

Gather (cost=1,005.90..209,053.71 rows=185,724 width=98) (actual time=0.659..1,109.918 rows=555,586 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
6. 220.635 1,235.191 ↓ 2.4 111,117 5 / 5

Nested Loop Left Join (cost=5.91..189,133.08 rows=46,431 width=98) (actual time=0.184..1,235.191 rows=111,117 loops=5)

7. 225.086 458.970 ↓ 2.4 111,117 5 / 5

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

  • Hash Cond: (((pc_ses.queue_id)::character varying)::text = (cs_enumeration_queue.purecloud_id)::text)
8. 233.804 233.804 ↑ 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=0.018..233.804 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.029 0.080 ↓ 1.2 43 5 / 5

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

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

Seq Scan on cs_enumeration_queue (cost=0.00..4.31 rows=37 width=36) (actual time=0.013..0.051 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. 555.586 555.586 ↑ 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.005..0.005 rows=1 loops=555,586)

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

CTE metrics

13. 3,471.116 3,471.116 ↑ 20,636.0 3 1

CTE Scan on facts_fo (cost=0.00..12,691.14 rows=61,908 width=488) (actual time=2,789.010..3,471.116 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 : 0.901 ms
Execution time : 3,478.782 ms