explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NvNT

Settings
# exclusive inclusive rows x rows loops node
1. 0.108 300,289.937 ↓ 11.8 200 1

Limit (cost=999,086.41..999,086.46 rows=17 width=16) (actual time=300,289.782..300,289.937 rows=200 loops=1)

2. 4.379 300,289.829 ↓ 11.8 200 1

Sort (cost=999,086.41..999,086.46 rows=17 width=16) (actual time=300,289.779..300,289.829 rows=200 loops=1)

  • Sort Key: applicatio0_.view_id
  • Sort Method: top-N heapsort Memory: 34kB
3. 16.771 300,285.450 ↓ 596.8 10,145 1

Subquery Scan on applicatio0_ (cost=983,150.49..999,086.07 rows=17 width=16) (actual time=300,225.427..300,285.450 rows=10,145 loops=1)

  • Filter: ((applicatio0_.batch_id = 0) AND (applicatio0_.production_system_id = 1) AND ((applicatio0_.code)::text = '10'::text) AND ((applicatio0_.urgency)::text = '00'::text) AND (applicatio0_.current_state = 15))
  • Rows Removed by Filter: 18439
4. 30.825 300,268.679 ↑ 1.0 28,584 1

WindowAgg (cost=983,150.49..998,442.93 rows=28,584 width=169) (actual time=300,221.642..300,268.679 rows=28,584 loops=1)

5. 438.692 300,237.854 ↑ 1.0 28,584 1

Sort (cost=983,150.49..983,221.95 rows=28,584 width=169) (actual time=300,221.611..300,237.854 rows=28,584 loops=1)

  • Sort Key: loc.code, (CASE WHEN (isexistsparam('Pu Generation'::character varying, 'SORTING_DATA_ELEMENT'::character varying) <> '-1'::integer) THEN lower(el.value) ELSE NULL::text END), (CASE WHEN (isexistsparam('Pu Generation'::character varying, 'SORTING_DATA_ELEMENT'::character varying) <> '-1'::integer) THEN (el.value)::text ELSE NULL::text END) COLLATE "C", ord.date_ordered, app.application_number
  • Sort Method: external merge Disk: 2800kB
6. 5,326.337 299,799.162 ↑ 1.0 28,584 1

Hash Join (cost=1,267.25..978,591.85 rows=28,584 width=169) (actual time=63.679..299,799.162 rows=28,584 loops=1)

  • Hash Cond: (app.location_id = loc.id)
7. 111.918 294,472.803 ↑ 1.0 28,584 1

Nested Loop (cost=1,256.57..978,188.15 rows=28,584 width=139) (actual time=63.433..294,472.803 rows=28,584 loops=1)

8. 103.062 294,217.965 ↑ 1.0 28,584 1

Nested Loop (cost=1,256.43..973,209.41 rows=28,584 width=109) (actual time=63.425..294,217.965 rows=28,584 loops=1)

9. 128.745 293,829.063 ↑ 1.0 28,584 1

Hash Right Join (cost=1,256.14..962,613.45 rows=28,584 width=109) (actual time=63.412..293,829.063 rows=28,584 loops=1)

  • Hash Cond: (el.application_id = app.id)
10. 293,637.370 293,637.370 ↓ 1.0 28,584 1

Seq Scan on data_element el (cost=0.00..960,970.70 rows=28,117 width=54) (actual time=0.415..293,637.370 rows=28,584 loops=1)

  • Filter: (element_id = isexistsparam('Pu Generation'::character varying, 'SORTING_DATA_ELEMENT'::character varying))
  • Rows Removed by Filter: 3457835
11. 33.511 62.948 ↑ 1.0 28,584 1

Hash (cost=898.84..898.84 rows=28,584 width=63) (actual time=62.948..62.948 rows=28,584 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2936kB
12. 29.437 29.437 ↑ 1.0 28,584 1

Seq Scan on application app (cost=0.00..898.84 rows=28,584 width=63) (actual time=0.010..29.437 rows=28,584 loops=1)

13. 285.840 285.840 ↑ 1.0 1 28,584

Index Scan using pk_application_order_id on application_order ord (cost=0.29..0.36 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=28,584)

  • Index Cond: (id = app.application_order_id)
14. 142.920 142.920 ↑ 1.0 1 28,584

Index Scan using pk_application_type_id on application_type atp (cost=0.14..0.16 rows=1 width=46) (actual time=0.004..0.005 rows=1 loops=28,584)

  • Index Cond: (id = app.application_type_id)
15. 0.008 0.022 ↑ 10.0 3 1

Hash (cost=10.30..10.30 rows=30 width=46) (actual time=0.022..0.022 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.014 0.014 ↑ 10.0 3 1

Seq Scan on location loc (cost=0.00..10.30 rows=30 width=46) (actual time=0.009..0.014 rows=3 loops=1)