explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u0C5

Settings
# exclusive inclusive rows x rows loops node
1. 13.543 9,978.038 ↓ 8.8 7,424 1

GroupAggregate (cost=265,933.50..265,948.27 rows=844 width=36) (actual time=9,956.838..9,978.038 rows=7,424 loops=1)

  • Group Key: elt.entity_id
2.          

CTE month_data

3. 0.009 0.009 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1)

4. 50.789 9,964.495 ↓ 96.8 81,664 1

Sort (cost=265,933.47..265,935.58 rows=844 width=10) (actual time=9,956.825..9,964.495 rows=81,664 loops=1)

  • Sort Key: elt.entity_id
  • Sort Method: external merge Disk: 1,680kB
5. 650.456 9,913.706 ↓ 96.8 81,664 1

Hash Join (cost=181.46..265,892.45 rows=844 width=10) (actual time=3.143..9,913.706 rows=81,664 loops=1)

  • Hash Cond: (date_part('MONTH'::text, (elt.month_year)::timestamp without time zone) = md.month_number)
  • Join Filter: (elt.month_year > md.month)
  • Rows Removed by Join Filter: 233,742
6. 308.784 9,263.236 ↓ 6.0 3,042,941 1

Nested Loop (cost=181.42..262,697.45 rows=506,132 width=14) (actual time=1.780..9,263.236 rows=3,042,941 loops=1)

7. 8.277 28.444 ↓ 3.4 8,174 1

Hash Join (cost=180.99..1,404.46 rows=2,412 width=4) (actual time=1.737..28.444 rows=8,174 loops=1)

  • Hash Cond: (entity.dnoid = dno.id)
8. 18.576 18.576 ↓ 1.3 9,436 1

Seq Scan on child_dno entity (cost=0.00..1,205.00 rows=7,026 width=8) (actual time=0.136..18.576 rows=9,436 loops=1)

  • Filter: ((NOT deleted) AND (ageing IS NOT NULL) AND (status_id = ANY ('{2462,2490,2491,2492,2,1,4,21}'::integer[])))
  • Rows Removed by Filter: 6,614
9. 0.147 1.591 ↑ 1.0 691 1

Hash (cost=172.20..172.20 rows=703 width=4) (actual time=1.591..1.591 rows=691 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
10. 1.444 1.444 ↑ 1.0 691 1

Seq Scan on dno (cost=0.00..172.20 rows=703 width=4) (actual time=0.009..1.444 rows=691 loops=1)

  • Filter: (status_id = ANY ('{2460,2461,2463,2464,2465,2466,2467,2468,2,1,5,6}'::integer[]))
  • Rows Removed by Filter: 1,341
11. 8,926.008 8,926.008 ↓ 1.8 372 8,174

Index Scan using idx_entity_lead_time_6 on entity_lead_time elt (cost=0.44..106.23 rows=210 width=14) (actual time=0.017..1.092 rows=372 loops=8,174)

  • Index Cond: ((entity_id = entity.id) AND (entity_type_id = 13))
  • Filter: (ageing IS NOT NULL)
12. 0.002 0.014 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=0.014..0.014 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.012 0.012 ↑ 1.0 1 1

CTE Scan on month_data md (cost=0.00..0.02 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=1)

Planning time : 0.949 ms
Execution time : 9,978.677 ms