explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E96Nv

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 6,288.268 ↓ 0.0 0 1

Limit (cost=52,903.84..52,903.90 rows=1 width=500) (actual time=6,288.268..6,288.268 rows=0 loops=1)

  • Buffers: shared hit=13,481 read=2,902
  • I/O Timings: read=5,893.423
2. 0.006 6,288.266 ↓ 0.0 0 1

Unique (cost=52,903.84..52,903.90 rows=1 width=500) (actual time=6,288.266..6,288.266 rows=0 loops=1)

  • Buffers: shared hit=13,481 read=2,902
  • I/O Timings: read=5,893.423
3. 0.083 6,288.260 ↓ 0.0 0 1

Sort (cost=52,903.84..52,903.85 rows=1 width=500) (actual time=6,288.260..6,288.260 rows=0 loops=1)

  • Sort Key: mohmapplic0_.received_date, mohmapplic0_.vsb_reference, mohmapplic0_.creation_timestamp, mohmapplic0_.creation_user, mohmapplic0_.modification_timestamp, mohmapplic0_.modification_user, mohmapplic0_.assigned_zkc, mohmapplic0_.btc, mohmapplic0_.cancel_comment, mohmapplic0_.cancel_reason, mohmapplic0_.care_unit, mohmapplic0_.careprovider_reference, mohmapplic0_.dossier_id, mohmapplic0_.end_comment, mohmapplic0_.end_date, mohmapplic0_.end_reason, mohmapplic0_.max_delivery_date, mohmapplic0_.procedure, mohmapplic0_.provider_approval_number, mohmapplic0_.province, mohmapplic0_.sda_calculated, mohmapplic0_.zip_code, mohmapplic0_.zkc
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=13,481 read=2,902
  • I/O Timings: read=5,893.423
4. 0.258 6,288.177 ↓ 0.0 0 1

Hash Semi Join (cost=52,746.08..52,903.83 rows=1 width=500) (actual time=6,288.177..6,288.177 rows=0 loops=1)

  • Hash Cond: ((mohmapplic0_.vsb_reference)::text = (applicatio1_.vsb_reference)::text)
  • Buffers: shared hit=13,481 read=2,902
  • I/O Timings: read=5,893.423
5. 1.651 4,938.792 ↓ 9.1 64 1

Nested Loop (cost=2,093.07..2,250.80 rows=7 width=518) (actual time=2,925.536..4,938.792 rows=64 loops=1)

  • Join Filter: ((mohmapplic0_.vsb_reference)::text = (zkcadvicep3_.application_id)::text)
  • Buffers: shared hit=3,662 read=2,537
  • I/O Timings: read=4,816.539
6. 3.296 2,917.837 ↓ 6.6 749 1

HashAggregate (cost=2,092.65..2,096.07 rows=114 width=18) (actual time=2,917.024..2,917.837 rows=749 loops=1)

  • Group Key: (mohmapplic4_.vsb_reference)::text
  • Buffers: shared hit=1,635 read=1,545
  • I/O Timings: read=2,865.443
7. 2.425 2,914.541 ↓ 6.6 749 1

Nested Loop (cost=0.42..2,092.36 rows=114 width=18) (actual time=11.060..2,914.541 rows=749 loops=1)

  • Buffers: shared hit=1,635 read=1,545
  • I/O Timings: read=2,865.443
8. 44.944 44.944 ↓ 6.6 749 1

Seq Scan on zkc_advice zkcadvicep3_ (cost=0.00..1,641.57 rows=114 width=9) (actual time=9.804..44.944 rows=749 loops=1)

  • Filter: (lower((appointed_to)::text) = 'sv'::text)
  • Rows Removed by Filter: 22,175
  • Buffers: shared read=842
  • I/O Timings: read=18.378
9. 2,867.172 2,867.172 ↑ 1.0 1 749

Index Only Scan using application_pkey on application mohmapplic4_ (cost=0.42..3.92 rows=1 width=9) (actual time=3.721..3.828 rows=1 loops=749)

  • Index Cond: (vsb_reference = (zkcadvicep3_.application_id)::text)
  • Heap Fetches: 67
  • Buffers: shared hit=1,635 read=703
  • I/O Timings: read=2,847.065
10. 2,019.304 2,019.304 ↓ 0.0 0 749

Index Scan using application_pkey on application mohmapplic0_ (cost=0.42..1.32 rows=1 width=500) (actual time=2.696..2.696 rows=0 loops=749)

  • Index Cond: ((vsb_reference)::text = (mohmapplic4_.vsb_reference)::text)
  • Filter: (((application_type)::text = 'RENEWAL'::text) AND ((province)::text = ANY ('{Antwerpen,Buitenland,"Brussels H. Gewest",Vlaams-Brabant,West-Vlaanderen,Oost-Vlaanderen,Limburg,Waals-Brabant,Henegouwen,Luik,Luxemburg,Namen}'::text[])))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2,027 read=992
  • I/O Timings: read=1,951.096
11. 15.760 1,349.127 ↑ 18.9 456 1

Hash (cost=50,373.55..50,373.55 rows=8,599 width=18) (actual time=1,349.127..1,349.127 rows=456 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 151kB
  • Buffers: shared hit=9,819 read=365
  • I/O Timings: read=1,076.884
12. 0.986 1,333.367 ↑ 18.9 456 1

Nested Loop (cost=0.42..50,373.55 rows=8,599 width=18) (actual time=2.506..1,333.367 rows=456 loops=1)

  • Buffers: shared hit=9,819 read=365
  • I/O Timings: read=1,076.884
13. 218.373 218.373 ↑ 18.9 456 1

Seq Scan on application_status applicatio1_ (cost=0.00..28,790.53 rows=8,599 width=9) (actual time=2.262..218.373 rows=456 loops=1)

  • Filter: (is_current_status AND ((status)::text = 'PASSED_TO_ZKC'::text))
  • Rows Removed by Filter: 618,592
  • Buffers: shared hit=8,695
14. 1,114.008 1,114.008 ↑ 1.0 1 456

Index Only Scan using application_pkey on application mohmapplic2_ (cost=0.42..2.48 rows=1 width=9) (actual time=2.442..2.443 rows=1 loops=456)

  • Index Cond: (vsb_reference = (applicatio1_.vsb_reference)::text)
  • Heap Fetches: 119
  • Buffers: shared hit=1,124 read=365
  • I/O Timings: read=1,076.884
Planning time : 4.315 ms
Execution time : 6,288.625 ms