explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LmNk

Settings
# exclusive inclusive rows x rows loops node
1. 0.074 4,383.566 ↓ 2.5 10 1

Limit (cost=8,338.27..8,338.29 rows=4 width=37) (actual time=4,383.459..4,383.566 rows=10 loops=1)

2. 0.015 4,383.492 ↓ 2.5 10 1

Unique (cost=8,338.27..8,338.29 rows=4 width=37) (actual time=4,383.458..4,383.492 rows=10 loops=1)

3. 483.275 4,383.477 ↓ 36.0 144 1

Sort (cost=8,338.27..8,338.28 rows=4 width=37) (actual time=4,383.457..4,383.477 rows=144 loops=1)

  • Sort Key: dos.uid
  • Sort Method: external merge Disk: 4584kB
4. 114.092 3,900.202 ↓ 24,899.8 99,599 1

Nested Loop (cost=2,260.09..8,338.23 rows=4 width=37) (actual time=27.167..3,900.202 rows=99,599 loops=1)

  • Join Filter: (meta.dossier_id = dos.id)
5. 138.515 3,582.314 ↓ 25,474.5 101,898 1

Hash Join (cost=2,259.80..8,336.49 rows=4 width=16) (actual time=27.160..3,582.314 rows=101,898 loops=1)

  • Hash Cond: (stip.dossier_id = meta.dossier_id)
  • Join Filter: (status_change.date_time > COALESCE((SubPlan 1), '1753-01-01 00:00:00'::timestamp without time zone))
6. 156.140 462.313 ↓ 105.7 160,261 1

Gather (cost=1,076.54..6,935.64 rows=1,516 width=16) (actual time=0.688..462.313 rows=160,261 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 200.878 306.173 ↓ 84.5 53,420 3

Nested Loop (cost=76.54..5,784.04 rows=632 width=16) (actual time=0.258..306.173 rows=53,420 loops=3)

8. 47.138 105.292 ↓ 84.5 53,420 3

Hash Join (cost=76.25..5,534.55 rows=632 width=16) (actual time=0.249..105.292 rows=53,420 loops=3)

  • 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: 10153
9. 43.856 58.120 ↑ 1.2 63,574 3

Hash Left Join (cost=38.12..5,288.81 rows=79,081 width=37) (actual time=0.155..58.120 rows=63,574 loops=3)

  • Hash Cond: (status_change.old_status_id = old_status_type.id)
10. 14.253 14.253 ↑ 1.2 63,574 3

Parallel Seq Scan on stipulation_status_change status_change (cost=0.00..5,042.81 rows=79,081 width=32) (actual time=0.131..14.253 rows=63,574 loops=3)

11. 0.006 0.011 ↑ 73.5 17 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
12. 0.005 0.005 ↑ 73.5 17 3

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

13. 0.009 0.034 ↑ 73.5 17 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
14. 0.025 0.025 ↑ 73.5 17 3

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

15. 0.003 0.003 ↑ 1.0 1 160,261

Index Scan using pk_stipulation on stipulation stip (cost=0.29..0.39 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=160,261)

  • Index Cond: (id = status_change.stipulation_id)
16. 8.829 26.444 ↓ 90.7 22,485 1

Hash (cost=1,180.16..1,180.16 rows=248 width=16) (actual time=26.444..26.444 rows=22,485 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1310kB
17. 9.779 17.615 ↓ 90.7 22,485 1

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

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

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

19. 0.003 0.013 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.010 0.010 ↑ 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.010 rows=1 loops=1)

  • Index Cond: ((value)::text = 'Manual'::text)
21.          

SubPlan (forHash Join)

22. 101.898 2,955.042 ↑ 1.0 1 101,898

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

23. 2,853.144 2,853.144 ↓ 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.028..0.028 rows=0 loops=101,898)

  • Index Cond: (metadata_id = meta.id)
  • Filter: ((name)::text = 'aa'::text)
  • Rows Removed by Filter: 62
24. 203.796 203.796 ↑ 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.002..0.002 rows=1 loops=101,898)

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