explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OwAk

Settings
# exclusive inclusive rows x rows loops node
1. 31.020 11,326.103 ↑ 1.0 1 1

Aggregate (cost=343,658.78..343,658.81 rows=1 width=104) (actual time=11,326.102..11,326.103 rows=1 loops=1)

2. 0.000 11,295.078 ↑ 1.2 32,187 1

Finalize GroupAggregate (cost=337,704.15..342,986.13 rows=38,437 width=42) (actual time=10,816.476..11,295.078 rows=32,187 loops=1)

  • Group Key: elt.entity_id, tier.name
3. 288.078 11,491.079 ↑ 1.2 32,187 1

Gather Merge (cost=337,704.15..342,322.78 rows=37,197 width=42) (actual time=10,816.416..11,491.079 rows=32,187 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
4. 216.548 11,203.001 ↑ 1.5 8,047 4 / 4

Partial GroupAggregate (cost=336,704.11..336,952.09 rows=12,399 width=42) (actual time=10,811.916..11,203.001 rows=8,047 loops=4)

  • Group Key: elt.entity_id, tier.name
5. 381.282 10,986.453 ↓ 19.2 238,294 4 / 4

Sort (cost=336,704.11..336,735.11 rows=12,399 width=16) (actual time=10,811.858..10,986.453 rows=238,294 loops=4)

  • Sort Key: elt.entity_id, tier.name
  • Sort Method: quicksort Memory: 24,096kB
  • Worker 0: Sort Method: quicksort Memory: 24,364kB
  • Worker 1: Sort Method: quicksort Memory: 24,064kB
  • Worker 2: Sort Method: quicksort Memory: 23,488kB
6. 351.002 10,605.171 ↓ 19.2 238,294 4 / 4

Hash Left Join (cost=7.04..335,861.11 rows=12,399 width=16) (actual time=25.140..10,605.171 rows=238,294 loops=4)

  • Hash Cond: (entity.tier_id = tier.id)
7. 345.351 10,253.886 ↓ 19.2 238,294 4 / 4

Nested Loop (cost=1.28..335,821.98 rows=12,399 width=14) (actual time=24.820..10,253.886 rows=238,294 loops=4)

8. 22.540 95.499 ↓ 1.5 8,103 4 / 4

Nested Loop (cost=0.71..36,204.19 rows=5,244 width=8) (actual time=0.113..95.499 rows=8,103 loops=4)

9. 40.546 40.546 ↑ 1.0 8,103 4 / 4

Parallel Index Scan using cdno_deleted_ix on child_dno entity (cost=0.42..32,825.84 rows=8,417 width=12) (actual time=0.064..40.546 rows=8,103 loops=4)

  • Index Cond: (client_id = 1,002)
  • Filter: ((ageing IS NOT NULL) AND (status_id = ANY ('{1578,1585,1586,1590,2,1,4}'::integer[])))
  • Rows Removed by Filter: 181
10. 32.413 32.413 ↑ 1.0 1 32,413 / 4

Index Scan using pk_dno on dno (cost=0.29..0.40 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=32,413)

  • Index Cond: (id = entity.dnoid)
  • Filter: (status_id = ANY ('{1572,1573,1574,1575,1580,1581,1582,1583,1588,1589,2,1,5,6}'::integer[]))
11. 9,813.036 9,813.036 ↓ 1.6 29 32,413 / 4

Index Scan using idx_entity_lead_time_6 on entity_lead_time elt (cost=0.57..56.96 rows=18 width=10) (actual time=1.051..1.211 rows=29 loops=32,413)

  • Index Cond: ((entity_id = entity.id) AND (entity_type_id = 13))
  • Filter: ((ageing IS NOT NULL) AND (month_year >= date_trunc('month'::text, (CURRENT_DATE - '2 mons'::interval))) AND (month_year < date_trunc('month'::text, (CURRENT_DATE - '1 mon'::interval))))
  • Rows Removed by Filter: 827
12. 0.139 0.283 ↑ 1.0 167 4 / 4

Hash (cost=3.67..3.67 rows=167 width=10) (actual time=0.282..0.283 rows=167 loops=4)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
13. 0.144 0.144 ↑ 1.0 167 4 / 4

Seq Scan on tier (cost=0.00..3.67 rows=167 width=10) (actual time=0.016..0.144 rows=167 loops=4)

14.          

SubPlan (for Aggregate)

15. 0.005 0.005 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=1)

Planning time : 0.909 ms
Execution time : 11,578.406 ms