explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ckAW

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 8,059.675 ↓ 0.0 0 1

GroupAggregate (cost=50,715.97..50,716.00 rows=2 width=40) (actual time=8,059.675..8,059.675 rows=0 loops=1)

  • Group Key: "*SELECT* 2".status
2. 0.000 8,059.672 ↓ 0.0 0 1

Sort (cost=50,715.97..50,715.97 rows=2 width=32) (actual time=8,059.671..8,059.672 rows=0 loops=1)

  • Sort Key: "*SELECT* 2".status
  • Sort Method: quicksort Memory: 25kB
3. 11.378 8,063.421 ↓ 0.0 0 1

Gather (cost=12,528.42..50,715.96 rows=2 width=32) (actual time=8,059.667..8,063.421 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.003 8,052.043 ↓ 0.0 0 3 / 3

Parallel Append (cost=11,528.42..49,715.76 rows=2 width=32) (actual time=8,052.043..8,052.043 rows=0 loops=3)

5. 0.001 7,379.638 ↓ 0.0 0 3 / 3

Subquery Scan on *SELECT* 2 (cost=12,005.19..38,118.05 rows=1 width=32) (actual time=7,379.638..7,379.638 rows=0 loops=3)

6. 1,320.697 7,379.637 ↓ 0.0 0 3 / 3

Nested Loop (cost=12,005.19..38,118.04 rows=1 width=220) (actual time=7,379.637..7,379.637 rows=0 loops=3)

  • Join Filter: (((COALESCE(e.assigneduser, COALESCE(p.assigneduser, d.abstractassigneduser)))::text = 'JOHNS408'::text) AND (COALESCE(e.actiondate, COALESCE(p.actiondate, d.abstractactiondate)) >= to_date('09-Apr-20 00:00:00'::text, 'DD-MON-YY HH24:MI:SS'::text)) AND (COALESCE(e.actiondate, COALESCE(p.actiondate, d.abstractactiondate)) <= to_date('09-Apr-20 23:59:59'::text, 'DD-MON-YY HH24:MI:SS'::text)) AND ((COALESCE(e.status, COALESCE(p.status, d.abstractstatus)))::text = ANY ('{Pending-Repeat,Pending-RepeatReply,Pending-Enrich,Pending-Research,Pending-Tasks,Pending-ResearchBulkAction,Pending-EnrichBulkAction}'::text[])))
  • Rows Removed by Join Filter: 297245
7. 873.682 1,301.431 ↓ 1,450.5 297,344 3 / 3

Parallel Hash Join (cost=12,004.90..38,047.62 rows=205 width=91) (actual time=222.299..1,301.431 rows=297,344 loops=3)

  • Hash Cond: (((e.ddrid)::text = (p.ddrid)::text) AND (e.parentnodeid = p.nodeid))
8. 206.217 206.217 ↑ 1.2 298,914 3 / 3

Parallel Seq Scan on twdc_data_deal_episode e (cost=0.00..23,239.93 rows=373,593 width=51) (actual time=0.008..206.217 rows=298,914 loops=3)

9. 101.132 221.532 ↑ 1.2 74,045 3 / 3

Parallel Hash (cost=10,616.56..10,616.56 rows=92,556 width=52) (actual time=221.531..221.532 rows=74,045 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 19840kB
10. 120.400 120.400 ↓ 2.4 222,134 1 / 3

Parallel Seq Scan on twdc_data_deal_product p (cost=0.00..10,616.56 rows=92,556 width=52) (actual time=0.007..361.199 rows=222,134 loops=1)

11. 4,757.509 4,757.509 ↑ 1.0 1 892,033 / 3

Index Scan using pc_twdc_stud_conts_work_all01 on pc_twdc_stud_contserv_work d (cost=0.29..0.31 rows=1 width=40) (actual time=0.015..0.016 rows=1 loops=892,033)

  • Index Cond: ((pyid)::text = (e.ddrid)::text)
12. 0.001 672.402 ↓ 0.0 0 2 / 3

Subquery Scan on *SELECT* 1 (cost=11,528.42..11,597.70 rows=1 width=32) (actual time=1,008.602..1,008.603 rows=0 loops=2)

13. 119.637 672.401 ↓ 0.0 0 2 / 3

Merge Join (cost=11,528.42..11,597.69 rows=1 width=220) (actual time=1,008.600..1,008.601 rows=0 loops=2)

  • Merge Cond: ((p_1.ddrid)::text = (d_1.pyid)::text)
  • Join Filter: (((COALESCE(p_1.assigneduser, d_1.abstractassigneduser))::text = 'JOHNS408'::text) AND (COALESCE(p_1.actiondate, d_1.abstractactiondate) >= to_date('09-Apr-20 00:00:00'::text, 'DD-MON-YY HH24:MI:SS'::text)) AND (COALESCE(p_1.actiondate, d_1.abstractactiondate) <= to_date('09-Apr-20 23:59:59'::text, 'DD-MON-YY HH24:MI:SS'::text)) AND ((COALESCE(p_1.status, d_1.abstractstatus))::text = ANY ('{Pending-Repeat,Pending-RepeatReply,Pending-Enrich,Pending-Research,Pending-Tasks,Pending-ResearchBulkAction,Pending-EnrichBulkAction}'::text[])))
  • Rows Removed by Join Filter: 88382
14. 323.226 492.490 ↓ 191.0 88,416 2 / 3

Sort (cost=11,331.23..11,332.38 rows=463 width=46) (actual time=685.239..738.735 rows=88,416 loops=2)

  • Sort Key: p_1.ddrid
  • Sort Method: quicksort Memory: 12549kB
  • Worker 1: Sort Method: quicksort Memory: 8779kB
15. 169.264 169.264 ↓ 191.0 88,416 2 / 3

Parallel Seq Scan on twdc_data_deal_product p_1 (cost=0.00..11,310.73 rows=463 width=46) (actual time=2.018..253.896 rows=88,416 loops=2)

  • Filter: ((totalnoofepisodes)::numeric = '0'::numeric)
  • Rows Removed by Filter: 22652
16. 60.274 60.274 ↓ 1.7 98,314 2 / 3

Index Only Scan using testch4 on pc_twdc_stud_contserv_work d_1 (cost=0.41..1,165.09 rows=58,912 width=40) (actual time=0.037..90.411 rows=98,314 loops=2)

  • Heap Fetches: 304
Planning time : 2.210 ms
Execution time : 8,064.445 ms