explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qPNR

Settings
# exclusive inclusive rows x rows loops node
1. 13.782 10,521.388 ↓ 8.8 7,424 1

GroupAggregate (cost=265,933.50..265,948.27 rows=844 width=36) (actual time=10,499.747..10,521.388 rows=7,424 loops=1)

  • Group Key: elt.entity_id
2.          

CTE month_data

3. 0.012 0.012 ↑ 1.0 1 1

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

4. 41.572 10,507.606 ↓ 96.8 81,664 1

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

  • Sort Key: elt.entity_id
  • Sort Method: external merge Disk: 1,680kB
5. 687.306 10,466.034 ↓ 96.8 81,664 1

Hash Join (cost=181.46..265,892.45 rows=844 width=10) (actual time=3.573..10,466.034 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. 324.562 9,778.710 ↓ 6.0 3,042,941 1

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

7. 9.253 37.700 ↓ 3.4 8,174 1

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

  • Hash Cond: (entity.dnoid = dno.id)
8. 27.242 27.242 ↓ 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.888..27.242 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.153 1.205 ↑ 1.0 691 1

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

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

Seq Scan on dno (cost=0.00..172.20 rows=703 width=4) (actual time=0.008..1.052 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. 9,416.448 9,416.448 ↓ 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.023..1.152 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.018 ↑ 1.0 1 1

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

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

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

Planning time : 1.127 ms
Execution time : 10,522.744 ms