explain.depesz.com

PostgreSQL's explain analyze made readable

Result: shWy

Settings
# exclusive inclusive rows x rows loops node
1. 117.644 24,467.337 ↑ 1.8 191,418 1

Group (cost=556,466.75..602,270.23 rows=341,736 width=75) (actual time=21,283.205..24,467.337 rows=191,418 loops=1)

  • Group Key: elt.entity_id, (date_part('month'::text, (elt.month_year)::timestamp without time zone)), elt.entity_type_id, entity.dynamic_metadata
2. 1,154.208 24,349.693 ↑ 1.7 191,418 1

Gather Merge (cost=556,466.75..597,254.44 rows=330,711 width=43) (actual time=21,283.201..24,349.693 rows=191,418 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
3. 1,137.437 23,195.485 ↑ 2.3 47,854 4 / 4

Group (cost=555,466.71..557,395.86 rows=110,237 width=43) (actual time=21,116.394..23,195.485 rows=47,854 loops=4)

  • Group Key: elt.entity_id, (date_part('month'::text, (elt.month_year)::timestamp without time zone)), elt.entity_type_id, entity.dynamic_metadata
4. 2,627.141 22,058.048 ↓ 10.8 1,189,636 4 / 4

Sort (cost=555,466.71..555,742.30 rows=110,237 width=43) (actual time=21,116.390..22,058.048 rows=1,189,636 loops=4)

  • Sort Key: elt.entity_id DESC, (date_part('month'::text, (elt.month_year)::timestamp without time zone)), entity.dynamic_metadata
  • Sort Method: external merge Disk: 44,408kB
  • Worker 0: Sort Method: external merge Disk: 42,792kB
  • Worker 1: Sort Method: external merge Disk: 43,216kB
  • Worker 2: Sort Method: external merge Disk: 42,896kB
5. 1,818.405 19,430.907 ↓ 10.8 1,189,636 4 / 4

Nested Loop (cost=0.99..546,234.22 rows=110,237 width=43) (actual time=1.473..19,430.907 rows=1,189,636 loops=4)

6. 69.012 69.012 ↑ 1.2 8,096 4 / 4

Parallel Index Scan Backward using cdno_deleted_ix on child_dno entity (cost=0.42..35,124.06 rows=9,847 width=43) (actual time=1.336..69.012 rows=8,096 loops=4)

  • Index Cond: (client_id = 1,002)
  • Filter: (ageing IS NOT NULL)
  • Rows Removed by Filter: 184
7. 17,543.490 17,543.490 ↓ 1.6 147 32,383 / 4

Index Scan using idx_entity_lead_time_6 on entity_lead_time elt (cost=0.57..50.91 rows=94 width=12) (actual time=1.642..2.167 rows=147 loops=32,383)

  • Index Cond: ((entity_id = entity.id) AND (entity_type_id = 13))
  • Filter: ((ageing IS NOT NULL) AND (month_year < now()) AND (month_year > (to_timestamp(to_char(now(), 'DDMMYYYY'::text), 'DDMMYYYY'::text) - '5 mons'::interval)))
  • Rows Removed by Filter: 706
Planning time : 0.865 ms
Execution time : 24,860.004 ms