explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DDC8

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 9,126.804 ↓ 0.0 0 1

Limit (cost=223,834.2..223,834.21 rows=1 width=634) (actual time=9,126.803..9,126.804 rows=0 loops=1)

  • Buffers: shared hit=1,174,104 dirtied=8
2. 0.020 9,126.803 ↓ 0.0 0 1

Sort (cost=223,834.2..223,834.21 rows=1 width=634) (actual time=9,126.803..9,126.803 rows=0 loops=1)

  • Sort Key: message."time" DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1,174,104 dirtied=8
3. 0.001 9,126.783 ↓ 0.0 0 1

Nested Loop (cost=2,655.69..223,834.19 rows=1 width=634) (actual time=9,126.783..9,126.783 rows=0 loops=1)

  • Buffers: shared hit=1,174,101 dirtied=8
4. 0.000 9,126.782 ↓ 0.0 0 1

Nested Loop (cost=2,655.69..223,831.55 rows=1 width=633) (actual time=9,126.782..9,126.782 rows=0 loops=1)

  • Buffers: shared hit=1,174,101 dirtied=8
5. 0.000 9,126.782 ↓ 0.0 0 1

Nested Loop (cost=2,655.25..223,824.82 rows=1 width=640) (actual time=9,126.782..9,126.782 rows=0 loops=1)

  • Buffers: shared hit=1,174,101 dirtied=8
6. 0.001 9,126.782 ↓ 0.0 0 1

Nested Loop (cost=2,655.25..223,780.15 rows=1 width=624) (actual time=9,126.782..9,126.782 rows=0 loops=1)

  • Buffers: shared hit=1,174,101 dirtied=8
7. 8,661.456 9,126.781 ↓ 0.0 0 1

Nested Loop (cost=2,135.28..223,259.97 rows=1 width=616) (actual time=9,126.781..9,126.781 rows=0 loops=1)

  • Buffers: shared hit=1,174,101 dirtied=8
8. 0.000 465.325 ↓ 1,254.0 1,254 1

Hash Join (cost=1,092.38..222,216.85 rows=1 width=615) (actual time=384.228..465.325 rows=1,254 loops=1)

  • Buffers: shared hit=711,476 dirtied=6
9. 6.927 16.568 ↑ 1.0 17,612 1

Hash Join (cost=502.21..2,163.07 rows=17,640 width=527) (actual time=4.774..16.568 rows=17,612 loops=1)

  • Buffers: shared hit=1,392
10. 4.971 4.971 ↑ 1.0 17,612 1

Seq Scan on message message (cost=0..1,419.4 rows=17,640 width=503) (actual time=0.008..4.971 rows=17,612 loops=1)

  • Buffers: shared hit=1,243
11. 2.685 4.670 ↓ 1.0 15,771 1

Hash (cost=305.98..305.98 rows=15,698 width=40) (actual time=4.67..4.67 rows=15,771 loops=1)

  • Buffers: shared hit=149
12. 1.985 1.985 ↓ 1.0 15,771 1

Seq Scan on thread thread (cost=0..305.98 rows=15,698 width=40) (actual time=0.004..1.985 rows=15,771 loops=1)

  • Buffers: shared hit=149
13. 376.266 379.307 ↑ 12.4 1,254 1

Hash (cost=356.67..356.67 rows=15,567 width=88) (actual time=379.307..379.307 rows=1,254 loops=1)

  • Buffers: shared hit=626,626 dirtied=6
14. 3.041 3.041 ↓ 1.0 15,579 1

Seq Scan on spool spool (cost=0..356.67 rows=15,567 width=88) (actual time=0.003..3.041 rows=15,579 loops=1)

  • Buffers: shared hit=201 dirtied=1
15.          

SubPlan (for Hash Join)

16. 16.833 437.658 ↑ 1.0 1 16,833

Limit (cost=24.86..24.87 rows=1 width=8) (actual time=0.025..0.026 rows=1 loops=16,833)

  • Buffers: shared hit=709,880 dirtied=5
17. 16.833 420.825 ↑ 1.0 1 16,833

Aggregate (cost=24.86..24.87 rows=1 width=8) (actual time=0.025..0.025 rows=1 loops=16,833)

  • Buffers: shared hit=709,880 dirtied=5
18. 49.963 403.992 ↑ 4.0 1 16,833

Nested Loop (cost=1.83..24.85 rows=4 width=8) (actual time=0.021..0.024 rows=1 loops=16,833)

  • Filter: ((identity.id = 'b16690e4-a3c5-4868-945e-c2458c27a525'::uuid) OR (relation."to" IS NOT NULL))
  • Buffers: shared hit=709,880 dirtied=5
19. 49.963 269.328 ↓ 1.2 5 16,833

Nested Loop (cost=1.42..23.05 rows=4 width=24) (actual time=0.007..0.016 rows=5 loops=16,833)

  • Buffers: shared hit=421,862 dirtied=5
20. 26.378 134.664 ↓ 1.2 5 16,833

Nested Loop (cost=0.99..17.31 rows=4 width=24) (actual time=0.006..0.008 rows=5 loops=16,833)

  • Buffers: shared hit=166,986 dirtied=4
21. 0.000 67.332 ↑ 1.0 1 16,833

Nested Loop (cost=0.57..16.62 rows=1 width=40) (actual time=0.004..0.004 rows=1 loops=16,833)

  • Buffers: shared hit=103,630 dirtied=3
22. 33.666 33.666 ↑ 1.0 1 16,833

Index Scan using unique_thread on thread thread2_2 (cost=0.29..8.3 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=16,833)

  • Index Cond: (thread2_2.spool = spool.id)
  • Buffers: shared hit=50,800 dirtied=1
23. 34.218 34.218 ↑ 1.0 1 17,109

Index Scan using message_thread_time_idx on message message2 (cost=0.29..8.3 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=17,109)

  • Index Cond: (message2.thread = thread2_2.id)
  • Filter: (NOT message2.draft)
  • Buffers: shared hit=52,830 dirtied=2
24. 40.954 40.954 ↑ 1.2 4 20,477

Index Only Scan using unique_participant on participant participant (cost=0.41..0.63 rows=5 width=32) (actual time=0.002..0.002 rows=4 loops=20,477)

  • Index Cond: (participant.thread = message2.thread)
  • Heap Fetches: 3,104
  • Buffers: shared hit=63,356 dirtied=1
25. 84.701 84.701 ↑ 1.0 1 84,701

Index Only Scan using identity_pkey on identity identity (cost=0.43..1.43 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=84,701)

  • Index Cond: (identity.id = participant.identity)
  • Heap Fetches: 447
  • Buffers: shared hit=254,876 dirtied=1
26. 84.701 84.701 ↓ 0.0 0 84,701

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=84,701)

  • Index Cond: ((relation."from" = 'b16690e4-a3c5-4868-945e-c2458c27a525'::uuid) AND (relation."to" = identity.id))
  • Filter: relation.manages
  • Buffers: shared hit=288,018
27. 0.000 0.000 ↓ 0.0 0 1,254

Index Scan using notification_pkey on notification notification (cost=1,042.9..1,043.11 rows=1 width=17) (actual time=0..0 rows=0 loops=1,254)

  • Index Cond: ((notification.id = (SubPlan 1)) AND (notification.id IS NOT NULL))
  • Filter: (notification."user" = 'b16690e4-a3c5-4868-945e-c2458c27a525'::uuid)
28.          

SubPlan (for Index Scan)

29. 1.254 8,660.124 ↓ 0.0 0 1,254

Limit (cost=1,042.61..1,042.61 rows=1 width=24) (actual time=6.906..6.906 rows=0 loops=1,254)

  • Buffers: shared hit=462,625 dirtied=2
30. 2.508 8,658.870 ↓ 0.0 0 1,254

Sort (cost=1,042.61..1,042.61 rows=1 width=24) (actual time=6.905..6.905 rows=0 loops=1,254)

  • Sort Key: notification3."time" DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=462,625 dirtied=2
31. 2.676 8,656.362 ↓ 0.0 0 1,254

Nested Loop (cost=8.6..1,042.6 rows=1 width=24) (actual time=6.903..6.903 rows=0 loops=1,254)

  • Buffers: shared hit=462,625 dirtied=2
32. 4,977.126 8,650.092 ↑ 3.0 1 1,254

Hash Join (cost=8.31..1,041.45 rows=3 width=16) (actual time=5.83..6.898 rows=1 loops=1,254)

  • Buffers: shared hit=459,019 dirtied=1
33. 3,670.458 3,670.458 ↓ 1.0 48,736 1,254

Seq Scan on notification_thread notification_thread (cost=0..850.35 rows=48,735 width=32) (actual time=0.001..2.927 rows=48,736 loops=1,254)

  • Buffers: shared hit=455,202 dirtied=1
34. 1.254 2.508 ↑ 1.0 1 1,254

Hash (cost=8.3..8.3 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,254)

  • Buffers: shared hit=3,817
35. 1.254 1.254 ↑ 1.0 1 1,254

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

  • Index Cond: (thread2.spool = spool.id)
  • Buffers: shared hit=3,817
36. 3.594 3.594 ↓ 0.0 0 1,198

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

  • Index Cond: (notification3.id = notification_thread.id)
  • Filter: ((notification3."time" <= '2020-09-30 16:32:38.054558-04'::timestamp with time zone) AND (notification3."user" = 'b16690e4-a3c5-4868-945e-c2458c27a525'::uuid))
  • Buffers: shared hit=3,606 dirtied=1
37. 0.000 0.000 ↓ 0.0 0 0

Index Scan using notification_pkey on notification notification2 (cost=519.97..520.18 rows=1 width=24) (never executed)

  • Index Cond: (notification2.id = (SubPlan 2))
  • Filter: (notification2."user" = 'b16690e4-a3c5-4868-945e-c2458c27a525'::uuid)
38.          

SubPlan (for Index Scan)

39. 0.000 0.000 ↓ 0.0 0 0

Limit (cost=519.68..519.68 rows=1 width=24) (never executed)

40. 0.000 0.000 ↓ 0.0 0 0

Sort (cost=519.68..519.68 rows=1 width=24) (never executed)

  • Sort Key: notification3_1."time" DESC
41. 0.000 0.000 ↓ 0.0 0 0

Nested Loop (cost=28.44..519.67 rows=1 width=24) (never executed)

42. 0.000 0.000 ↓ 0.0 0 0

Nested Loop (cost=28.16..519.33 rows=1 width=40) (never executed)

43. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on notification notification3_1 (cost=27.87..511.02 rows=1 width=24) (never executed)

  • Filter: (notification3_1."user" = 'b16690e4-a3c5-4868-945e-c2458c27a525'::uuid)
44. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on notification_time_idx (cost=0..27.86 rows=1,010 width=0) (never executed)

  • Index Cond: (notification3_1."time" > '2020-09-30 16:32:38.054558-04'::timestamp with time zone)
45. 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: (notification_thread_1.id = notification3_1.id)
46. 0.000 0.000 ↓ 0.0 0 0

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

  • Index Cond: (thread2_1.id = notification_thread_1.thread)
  • Filter: (thread2_1.spool = spool.id)
47. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on location location (cost=0..29.85 rows=1,185 width=48) (never executed)

48. 0.000 0.000 ↓ 0.0 0 0

Index Scan using handle_pkey on handle handle (cost=0.43..6.73 rows=1 width=25) (never executed)

  • Index Cond: (handle.id = thread.spool)
49. 0.000 0.000 ↓ 0.0 0 0

Seq Scan on case case (cost=0..1.73 rows=73 width=17) (never executed)

Planning time : 10.458 ms
Execution time : 9,127.119 ms