explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xUXK

Settings
# exclusive inclusive rows x rows loops node
1. 3.848 2,697.387 ↓ 1,637.0 6,548 1

Unique (cost=2,282.01..2,282.10 rows=4 width=37) (actual time=2,625.543..2,697.387 rows=6,548 loops=1)

2. 3.874 2,693.539 ↓ 10,587.2 42,349 1

Subquery Scan on a (cost=2,282.01..2,282.09 rows=4 width=37) (actual time=2,625.543..2,693.539 rows=42,349 loops=1)

3. 14.373 2,689.665 ↓ 10,587.2 42,349 1

Unique (cost=2,282.01..2,282.05 rows=4 width=53) (actual time=2,625.541..2,689.665 rows=42,349 loops=1)

4. 343.170 2,675.292 ↓ 24,899.8 99,599 1

Sort (cost=2,282.01..2,282.02 rows=4 width=53) (actual time=2,625.540..2,675.292 rows=99,599 loops=1)

  • Sort Key: dos.uid, (COALESCE(max(evt.date_time), '1753-01-01 00:00:00'::timestamp without time zone)), status_change.date_time
  • Sort Method: external merge Disk: 6456kB
5. 82.655 2,332.122 ↓ 24,899.8 99,599 1

Nested Loop (cost=104.67..2,281.97 rows=4 width=53) (actual time=0.208..2,332.122 rows=99,599 loops=1)

  • Join Filter: (meta.dossier_id = dos.id)
6. 55.758 2,147.569 ↓ 25,474.5 101,898 1

Nested Loop (cost=104.38..2,280.23 rows=4 width=32) (actual time=0.204..2,147.569 rows=101,898 loops=1)

  • Join Filter: (status_change.date_time > (COALESCE(max(evt.date_time), '1753-01-01 00:00:00'::timestamp without time zone)))
7. 48.426 359.545 ↓ 9,263.5 101,898 1

Hash Join (cost=85.14..2,068.21 rows=11 width=32) (actual time=0.187..359.545 rows=101,898 loops=1)

  • Hash Cond: (status_change.new_status_id = new_status_type.id)
  • Join Filter: (((new_status_type.value)::text = ANY ('{"To be received",Received,"Provisionally approved","To be replaced",Approved,Cancelled,Deferred}'::text[])) OR ((old_status_type.value)::text = ANY ('{"To be received","To be replaced",Received}'::text[])))
  • Rows Removed by Join Filter: 17098
8. 30.620 311.105 ↓ 87.8 118,996 1

Hash Left Join (cost=47.01..2,026.53 rows=1,356 width=53) (actual time=0.165..311.105 rows=118,996 loops=1)

  • Hash Cond: (status_change.old_status_id = old_status_type.id)
9. 46.628 280.477 ↓ 87.8 118,996 1

Nested Loop (cost=8.89..1,984.85 rows=1,356 width=48) (actual time=0.144..280.477 rows=118,996 loops=1)

10. 28.570 87.327 ↓ 93.1 73,261 1

Nested Loop (cost=8.47..1,449.46 rows=787 width=32) (actual time=0.140..87.327 rows=73,261 loops=1)

11. 9.554 13.787 ↓ 90.7 22,485 1

Hash Join (cost=8.17..1,180.16 rows=248 width=16) (actual time=0.132..13.787 rows=22,485 loops=1)

  • Hash Cond: (meta.handling_id = handling.id)
12. 4.221 4.221 ↓ 1.0 34,719 1

Seq Scan on metadata meta (cost=0.00..1,078.11 rows=34,711 width=24) (actual time=0.112..4.221 rows=34,719 loops=1)

13. 0.003 0.012 ↑ 1.0 1 1

Hash (cost=8.16..8.16 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.009 0.009 ↑ 1.0 1 1

Index Scan using ix_dossier_handling_type_value on dossier_handling_type handling (cost=0.14..8.16 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: ((value)::text = 'Manual'::text)
15. 44.970 44.970 ↑ 4.0 3 22,485

Index Scan using ix_stipulation_dossier_id on stipulation stip (cost=0.29..0.97 rows=12 width=16) (actual time=0.001..0.002 rows=3 loops=22,485)

  • Index Cond: (dossier_id = meta.dossier_id)
16. 146.522 146.522 ↑ 1.5 2 73,261

Index Scan using ix_stipulation_status_change_stipulation_id on stipulation_status_change status_change (cost=0.42..0.65 rows=3 width=32) (actual time=0.002..0.002 rows=2 loops=73,261)

  • Index Cond: (stipulation_id = stip.id)
17. 0.005 0.008 ↑ 73.5 17 1

Hash (cost=22.50..22.50 rows=1,250 width=21) (actual time=0.008..0.008 rows=17 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
18. 0.003 0.003 ↑ 73.5 17 1

Seq Scan on stipulation_status_type old_status_type (cost=0.00..22.50 rows=1,250 width=21) (actual time=0.001..0.003 rows=17 loops=1)

19. 0.007 0.014 ↑ 73.5 17 1

Hash (cost=22.50..22.50 rows=1,250 width=21) (actual time=0.014..0.014 rows=17 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
20. 0.007 0.007 ↑ 73.5 17 1

Seq Scan on stipulation_status_type new_status_type (cost=0.00..22.50 rows=1,250 width=21) (actual time=0.005..0.007 rows=17 loops=1)

21. 0.000 1,732.266 ↑ 1.0 1 101,898

Aggregate (cost=19.24..19.25 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=101,898)

22. 1,732.266 1,732.266 ↓ 0.0 0 101,898

Index Scan using ix_dossier_event_metadata_id on dossier_event evt (cost=0.42..19.24 rows=1 width=8) (actual time=0.017..0.017 rows=0 loops=101,898)

  • Index Cond: (metadata_id = meta.id)
  • Filter: ((name)::text = 'aa'::text)
  • Rows Removed by Filter: 62
23. 101.898 101.898 ↑ 1.0 1 101,898

Index Scan using pk_dossier on dossier dos (cost=0.29..0.42 rows=1 width=45) (actual time=0.001..0.001 rows=1 loops=101,898)

  • Index Cond: (id = stip.dossier_id)
  • Filter: (lock_token IS NULL)
  • Rows Removed by Filter: 0
Planning time : 4.866 ms
Execution time : 2,699.110 ms