explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5BY8

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 14,389.263 ↓ 18.0 18 1

Limit (cost=116,449.83..116,449.83 rows=1 width=637) (actual time=14,389.257..14,389.263 rows=18 loops=1)

  • Buffers: shared hit=28,163,480 dirtied=11
2. 0.048 14,389.262 ↓ 18.0 18 1

Sort (cost=116,449.83..116,449.83 rows=1 width=637) (actual time=14,389.256..14,389.262 rows=18 loops=1)

  • Sort Key: message."time" DESC
  • Sort Method: quicksort Memory: 43kB
  • Buffers: shared hit=28,163,480 dirtied=11
3. 0.140 14,389.214 ↓ 18.0 18 1

Nested Loop (cost=102,385.49..116,449.82 rows=1 width=637) (actual time=800.754..14,389.214 rows=18 loops=1)

  • Buffers: shared hit=28,163,480 dirtied=11
4. 0.000 14,388.192 ↓ 18.0 18 1

Nested Loop (cost=102,381.13..116,267.68 rows=1 width=629) (actual time=800.695..14,388.192 rows=18 loops=1)

  • Buffers: shared hit=28,161,986 dirtied=11
5. 102.033 14,098.520 ↓ 1,436.0 1,436 1

Nested Loop (cost=102,376.77..115,909.6 rows=1 width=628) (actual time=755.072..14,098.52 rows=1,436 loops=1)

  • Buffers: shared hit=27,748,334 dirtied=9
6. 3.347 13,834.219 ↓ 1,436.0 1,436 1

Merge Join (cost=102,376.77..115,864.69 rows=1 width=612) (actual time=754.837..13,834.219 rows=1,436 loops=1)

  • Buffers: shared hit=27,722,486 dirtied=9
7. 3.219 45.579 ↓ 1.0 18,425 1

Nested Loop (cost=0.57..11,857.59 rows=18,423 width=531) (actual time=0.013..45.579 rows=18,425 loops=1)

  • Buffers: shared hit=66,551 dirtied=2
8. 9.402 9.402 ↓ 1.0 16,479 1

Index Scan using thread_spool_idx on thread thread (cost=0.29..1,139.33 rows=16,477 width=40) (actual time=0.007..9.402 rows=16,479 loops=1)

  • Buffers: shared hit=16,279 dirtied=1
9. 32.958 32.958 ↑ 1.0 1 16,479

Index Scan using message_thread_idx on message message (cost=0.29..0.64 rows=1 width=507) (actual time=0.002..0.002 rows=1 loops=16,479)

  • Index Cond: (thread = thread.id)
  • Buffers: shared hit=50,272 dirtied=1
10. 10.463 758.818 ↓ 1.1 18,425 1

Sort (cost=102,375.79..102,416.44 rows=16,261 width=113) (actual time=754.288..758.818 rows=18,425 loops=1)

  • Sort Key: spool.id
  • Sort Method: quicksort Memory: 3,229kB
  • Buffers: shared hit=41,173 dirtied=3
11. 482.985 748.355 ↓ 1.0 16,263 1

Hash Join (cost=576.87..101,238.4 rows=16,261 width=113) (actual time=4.771..748.355 rows=16,263 loops=1)

  • Buffers: shared hit=41,173 dirtied=3
12. 260.626 260.626 ↑ 1.0 4,324,163 1

Seq Scan on handle handle (cost=0..84,261.58 rows=4,329,958 width=25) (actual time=0.004..260.626 rows=4,324,163 loops=1)

  • Buffers: shared hit=40,962 dirtied=1
13. 2.496 4.744 ↓ 1.0 16,263 1

Hash (cost=373.61..373.61 rows=16,261 width=88) (actual time=4.743..4.744 rows=16,263 loops=1)

  • Buffers: shared hit=211 dirtied=2
14. 2.248 2.248 ↓ 1.0 16,263 1

Seq Scan on spool spool (cost=0..373.61 rows=16,261 width=88) (actual time=0.002..2.248 rows=16,263 loops=1)

  • Buffers: shared hit=211 dirtied=2
15.          

SubPlan (for Merge Join)

16. 18.425 13,026.475 ↑ 1.0 1 18,425

Limit (cost=21.78..21.79 rows=1 width=8) (actual time=0.706..0.707 rows=1 loops=18,425)

  • Buffers: shared hit=27,614,762 dirtied=4
17. 184.250 13,008.050 ↑ 1.0 1 18,425

Aggregate (cost=21.78..21.79 rows=1 width=8) (actual time=0.706..0.706 rows=1 loops=18,425)

  • Buffers: shared hit=27,614,762 dirtied=4
18. 2,306.769 12,823.800 ↓ 27.0 108 18,425

Nested Loop (cost=1.84..21.77 rows=4 width=8) (actual time=0.053..0.696 rows=108 loops=18,425)

  • Filter: ((identity.id = 'b16690e4-a3c5-4868-945e-c2458c27a525'::uuid) OR (relation."to" IS NOT NULL))
  • Buffers: shared hit=27,614,762 dirtied=4
19. 1,330.244 6,798.825 ↓ 50.5 202 18,425

Nested Loop (cost=1.42..19.97 rows=4 width=24) (actual time=0.006..0.369 rows=202 loops=18,425)

  • Buffers: shared hit=12,815,450 dirtied=1
20. 497.155 1,750.375 ↓ 50.5 202 18,425

Nested Loop (cost=0.99..14.46 rows=4 width=24) (actual time=0.005..0.095 rows=202 loops=18,425)

  • Buffers: shared hit=1,660,379 dirtied=1
21. 35.805 110.550 ↓ 1.5 6 18,425

Nested Loop (cost=0.7..12.86 rows=4 width=48) (actual time=0.003..0.006 rows=6 loops=18,425)

  • Buffers: shared hit=144,918 dirtied=1
22. 18.425 18.425 ↓ 2.0 2 18,425

Index Scan using thread_spool_idx on thread thread2_2 (cost=0.29..8.3 rows=1 width=16) (actual time=0.001..0.001 rows=2 loops=18,425)

  • Index Cond: (spool = spool.id)
  • Buffers: shared hit=59,605
23. 56.320 56.320 ↑ 1.2 4 28,160

Index Only Scan using unique_participant on participant participant (cost=0.42..4.5 rows=5 width=32) (actual time=0.002..0.002 rows=4 loops=28,160)

  • Index Cond: (thread = thread2_2.id)
  • Heap Fetches: 199
  • Buffers: shared hit=85,313 dirtied=1
24. 1,142.670 1,142.670 ↓ 33.0 33 114,267

Index Scan using message_thread_idx on message message2 (cost=0.29..0.39 rows=1 width=24) (actual time=0.001..0.01 rows=33 loops=114,267)

  • Index Cond: (thread = participant.thread)
  • Filter: (NOT draft)
  • Buffers: shared hit=1,515,461
25. 3,718.206 3,718.206 ↑ 1.0 1 3,718,206

Index Only Scan using identity_pkey on identity identity (cost=0.43..1.37 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=3,718,206)

  • Index Cond: (id = participant.identity)
  • Heap Fetches: 3
  • Buffers: shared hit=11,155,071
26. 3,718.206 3,718.206 ↓ 0.0 0 3,718,206

Index Scan using unique_relation on relation relation (cost=0.41..0.44 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=3,718,206)

  • Index Cond: (("from" = 'b16690e4-a3c5-4868-945e-c2458c27a525'::uuid) AND ("to" = identity.id))
  • Filter: manages
  • Buffers: shared hit=14,799,312 dirtied=3
27. 162.268 162.268 ↑ 1.0 1,196 1,436

Seq Scan on location location (cost=0..29.96 rows=1,196 width=48) (actual time=0.001..0.113 rows=1,196 loops=1,436)

  • Buffers: shared hit=25,848
28. 18.668 24.412 ↓ 3.6 32 1,436

Bitmap Heap Scan on notification notification (cost=4.36..36.83 rows=9 width=17) (actual time=0.005..0.017 rows=32 loops=1,436)

  • Filter: (id IS NOT NULL)
  • Heap Blocks: exact=22,976
  • Buffers: shared hit=27,284 dirtied=1
29. 5.744 5.744 ↓ 3.6 32 1,436

Bitmap Index Scan on notification_user_idx (cost=0..4.36 rows=9 width=0) (actual time=0.004..0.004 rows=32 loops=1,436)

  • Index Cond: ("user" = 'b16690e4-a3c5-4868-945e-c2458c27a525'::uuid)
  • Buffers: shared hit=4,308
30.          

SubPlan (for Nested Loop)

31. 45.952 275.712 ↓ 0.0 0 45,952

Limit (cost=35.68..35.68 rows=1 width=24) (actual time=0.006..0.006 rows=0 loops=45,952)

  • Buffers: shared hit=386,368 dirtied=1
32. 0.000 229.760 ↓ 0.0 0 45,952

Sort (cost=35.68..35.68 rows=1 width=24) (actual time=0.005..0.005 rows=0 loops=45,952)

  • Sort Key: notification3."time" DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=386,368 dirtied=1
33. 48.160 229.760 ↓ 0.0 0 45,952

Nested Loop (cost=4.91..35.67 rows=1 width=24) (actual time=0.005..0.005 rows=0 loops=45,952)

  • Buffers: shared hit=386,368 dirtied=1
34. 42.976 137.856 ↑ 3.0 1 45,952

Nested Loop (cost=4.62..34.52 rows=3 width=16) (actual time=0.003..0.003 rows=1 loops=45,952)

  • Buffers: shared hit=254,752 dirtied=1
35. 45.952 45.952 ↑ 1.0 1 45,952

Index Scan using thread_spool_idx on thread thread2 (cost=0.29..8.3 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=45,952)

  • Index Cond: (spool = spool.id)
  • Buffers: shared hit=139,360
36. 0.000 48.928 ↑ 6.0 1 48,928

Bitmap Heap Scan on notification_thread notification_thread (cost=4.34..26.15 rows=6 width=32) (actual time=0.001..0.001 rows=1 loops=48,928)

  • Heap Blocks: exact=17,056
  • Buffers: shared hit=115,392 dirtied=1
37. 48.928 48.928 ↑ 6.0 1 48,928

Bitmap Index Scan on notification_thread_thread_idx (cost=0..4.33 rows=6 width=0) (actual time=0.001..0.001 rows=1 loops=48,928)

  • Index Cond: (thread = thread2.id)
  • Buffers: shared hit=98,336
38. 43.744 43.744 ↓ 0.0 0 43,744

Index Scan using notification_pkey on notification notification3 (cost=0.29..0.37 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=43,744)

  • Index Cond: (id = notification_thread.id)
  • Filter: (("time" <= '2020-10-30 16:32:38.054558-04'::timestamp with time zone) AND ("user" = 'b16690e4-a3c5-4868-945e-c2458c27a525'::uuid))
  • Buffers: shared hit=131,616
39. 0.234 0.306 ↓ 3.6 32 18

Bitmap Heap Scan on notification notification2 (cost=4.36..36.83 rows=9 width=24) (actual time=0.006..0.017 rows=32 loops=18)

  • Heap Blocks: exact=288
  • Buffers: shared hit=342
40. 0.072 0.072 ↓ 3.6 32 18

Bitmap Index Scan on notification_user_idx (cost=0..4.36 rows=9 width=0) (actual time=0.004..0.004 rows=32 loops=18)

  • Index Cond: ("user" = 'b16690e4-a3c5-4868-945e-c2458c27a525'::uuid)
  • Buffers: shared hit=54
41.          

SubPlan (for Nested Loop)

42. 0.000 0.576 ↓ 0.0 0 576

Limit (cost=0.87..16.13 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=576)

  • Buffers: shared hit=1,152
43. 0.000 0.576 ↓ 0.0 0 576

Nested Loop (cost=0.87..16.13 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=576)

  • Buffers: shared hit=1,152
44. 0.000 0.576 ↓ 0.0 0 576

Nested Loop (cost=0.58..15.8 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=576)

  • Buffers: shared hit=1,152
45. 0.576 0.576 ↓ 0.0 0 576

Index Scan using notification_time_idx on notification notification3_1 (cost=0.29..7.48 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=576)

  • Index Cond: ("time" > '2020-10-30 16:32:38.054558-04'::timestamp with time zone)
  • Filter: ("user" = 'b16690e4-a3c5-4868-945e-c2458c27a525'::uuid)
  • Buffers: shared hit=1,152
46. 0.000 0.000 ↓ 0.0 0 0

Index Scan using notification_thread_pkey on notification_thread notification_thread_1 (cost=0.29..8.31 rows=1 width=32) (never executed)

  • Index Cond: (id = notification3_1.id)
47. 0.000 0.000 ↓ 0.0 0 0

Index Scan using thread_pkey on thread thread2_1 (cost=0.29..0.33 rows=1 width=16) (never executed)

  • Index Cond: (id = notification_thread_1.thread)
  • Filter: (spool = spool.id)
Planning time : 6.052 ms
Execution time : 14,389.416 ms