explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jIgB

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 22,433.175 ↑ 1.0 25 1

Limit (cost=8,167,088.69..8,167,088.76 rows=25 width=418) (actual time=22,433.173..22,433.175 rows=25 loops=1)

2. 5.802 22,433.173 ↑ 114.7 25 1

Sort (cost=8,167,088.69..8,167,095.86 rows=2,867 width=418) (actual time=22,433.172..22,433.173 rows=25 loops=1)

  • Sort Key: (date_part('year'::text, timezone('UTC'::text, t.date))) DESC, (("substring"(t.number, '[0-9]+'::text))::bigint) DESC
  • Sort Method: top-N heapsort Memory: 53kB
3. 13.860 22,427.371 ↓ 1.9 5,441 1

Result (cost=0.00..8,167,007.79 rows=2,867 width=418) (actual time=5.339..22,427.371 rows=5,441 loops=1)

4. 1.335 22,413.511 ↓ 1.9 5,441 1

Append (cost=0.00..8,166,943.28 rows=2,867 width=402) (actual time=5.331..22,413.511 rows=5,441 loops=1)

5. 3.041 5,460.825 ↓ 1.9 1,286 1

Seq Scan on model_requestcontractor t (cost=0.00..1,934,198.29 rows=679 width=443) (actual time=5.331..5,460.825 rows=1,286 loops=1)

  • Filter: (NOT COALESCE(is_deleted, false))
  • Rows Removed by Filter: 4
6.          

SubPlan (forSeq Scan)

7. 1.286 5.144 ↑ 1.0 1 1,286

Aggregate (cost=2.51..2.52 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,286)

8. 3.858 3.858 ↑ 1.0 1 1,286

Index Scan using document_file_doc_guid on document_file (cost=0.29..2.51 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1,286)

  • Index Cond: (doc_guid = t.guid)
9. 1.286 5,452.640 ↑ 1.0 1 1,286

Limit (cost=2,845.95..2,845.96 rows=1 width=32) (actual time=4.240..4.240 rows=1 loops=1,286)

10. 0.000 5,451.354 ↑ 1.0 1 1,286

Subquery Scan on vc (cost=2,845.95..2,845.96 rows=1 width=32) (actual time=4.239..4.239 rows=1 loops=1,286)

11. 5.144 5,451.354 ↑ 1.0 1 1,286

Sort (cost=2,845.95..2,845.95 rows=1 width=100) (actual time=4.239..4.239 rows=1 loops=1,286)

  • Sort Key: (comments."array"[3]) DESC
  • Sort Method: quicksort Memory: 25kB
12. 2.572 5,446.210 ↓ 3.0 3 1,286

Subquery Scan on comments (cost=3.15..2,845.94 rows=1 width=100) (actual time=1.831..4.235 rows=3 loops=1,286)

  • Filter: ((comments.type)::text = 'RequestContractor'::text)
  • Rows Removed by Filter: 28
13. 5.144 5,443.638 ↓ 3.1 31 1,286

Append (cost=3.15..2,845.81 rows=10 width=68) (actual time=0.023..4.233 rows=31 loops=1,286)

14. 12.626 24.434 ↑ 1.0 2 1,286

Hash Right Join (cost=3.15..6.62 rows=2 width=44) (actual time=0.011..0.019 rows=2 loops=1,286)

  • Hash Cond: (pr.user_id = mc.author_id)
15. 6.664 6.664 ↑ 1.0 93 952

Seq Scan on tbl_profiles pr (cost=0.00..2.93 rows=93 width=53) (actual time=0.002..0.007 rows=93 loops=952)

16. 5.144 5.144 ↑ 1.0 2 1,286

Hash (cost=3.13..3.13 rows=2 width=126) (actual time=0.004..0.004 rows=2 loops=1,286)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • -> Index Scan using model_comment_commentable_id_commentable_type_idx on model_comment mc (cost=0.29..3.13 rows=2 width=126) (actual time=0
  • Index Cond: (commentable_id = t.id)
17. 2.572 5,414.060 ↓ 3.5 28 1,286

Subquery Scan on *SELECT* 2 (cost=0.42..2,839.18 rows=8 width=68) (actual time=0.031..4.210 rows=28 loops=1,286)

18. 5,375.159 5,411.488 ↓ 3.5 28 1,286

Nested Loop Left Join (cost=0.42..2,839.10 rows=8 width=43) (actual time=0.031..4.208 rows=28 loops=1,286)

  • Join Filter: (pr_1.user_id = mc_1.user_id)
  • Rows Removed by Join Filter: 1028
  • -> Index Scan using model_document_status_log_model_class_model_id_idx on model_document_status_log mc_1 (cost=0.42..2824.58 rows=8 width=8
  • Index Cond: (model_id = t.id)
19. 36.316 36.329 ↑ 2.5 37 36,329

Materialize (cost=0.00..3.40 rows=93 width=53) (actual time=0.000..0.001 rows=37 loops=36,329)

20. 0.013 0.013 ↑ 1.0 93 1

Seq Scan on tbl_profiles pr_1 (cost=0.00..2.93 rows=93 width=53) (actual time=0.002..0.013 rows=93 loops=1)

21. 1.894 2,813.884 ↓ 2.0 670 1

Seq Scan on model_requestsupervisory t_1 (cost=0.00..951,436.37 rows=334 width=423) (actual time=4.124..2,813.884 rows=670 loops=1)

  • Filter: (NOT COALESCE(is_deleted, false))
  • Rows Removed by Filter: 2
22.          

SubPlan (forSeq Scan)

23. 0.670 2.680 ↑ 1.0 1 670

Aggregate (cost=2.51..2.52 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=670)

24. 2.010 2.010 ↑ 1.0 1 670

Index Scan using document_file_doc_guid on document_file document_file_1 (cost=0.29..2.51 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=670)

  • Index Cond: (doc_guid = t_1.guid)
25. 0.000 2,809.310 ↑ 1.0 1 670

Limit (cost=2,845.95..2,845.96 rows=1 width=32) (actual time=4.193..4.193 rows=1 loops=670)

26. 0.000 2,809.310 ↑ 1.0 1 670

Subquery Scan on vc_1 (cost=2,845.95..2,845.96 rows=1 width=32) (actual time=4.193..4.193 rows=1 loops=670)

27. 2.680 2,809.310 ↑ 1.0 1 670

Sort (cost=2,845.95..2,845.95 rows=1 width=100) (actual time=4.193..4.193 rows=1 loops=670)

  • Sort Key: (comments_1."array"[3]) DESC
  • Sort Method: quicksort Memory: 25kB
28. 2.010 2,806.630 ↓ 3.0 3 670

Subquery Scan on comments_1 (cost=3.15..2,845.94 rows=1 width=100) (actual time=2.132..4.189 rows=3 loops=670)

  • Filter: ((comments_1.type)::text = 'RequestSupervisory'::text)
  • Rows Removed by Filter: 34
29. 2.010 2,804.620 ↓ 3.7 37 670

Append (cost=3.15..2,845.81 rows=10 width=68) (actual time=0.017..4.186 rows=37 loops=670)

30. 6.780 13.400 ↓ 1.5 3 670

Hash Right Join (cost=3.15..6.62 rows=2 width=44) (actual time=0.011..0.020 rows=3 loops=670)

  • Hash Cond: (pr_2.user_id = mc_2.author_id)
31. 3.270 3.270 ↑ 1.0 93 545

Seq Scan on tbl_profiles pr_2 (cost=0.00..2.93 rows=93 width=53) (actual time=0.002..0.006 rows=93 loops=545)

32. 3.350 3.350 ↓ 1.5 3 670

Hash (cost=3.13..3.13 rows=2 width=126) (actual time=0.005..0.005 rows=3 loops=670)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • -> Index Scan using model_comment_commentable_id_commentable_type_idx on model_comment mc_2 (cost=0.29..3.13 rows=2 width=126) (actual time
  • Index Cond: (commentable_id = t_1.id)
33. 2.010 2,789.210 ↓ 4.1 33 670

Subquery Scan on *SELECT* 2_1 (cost=0.42..2,839.18 rows=8 width=68) (actual time=0.018..4.163 rows=33 loops=670)

34. 2,764.758 2,787.200 ↓ 4.1 33 670

Nested Loop Left Join (cost=0.42..2,839.10 rows=8 width=43) (actual time=0.018..4.160 rows=33 loops=670)

  • Join Filter: (pr_3.user_id = mc_3.user_id)
  • Rows Removed by Join Filter: 1168
  • -> Index Scan using model_document_status_log_model_class_model_id_idx on model_document_status_log mc_3 (cost=0.42..2824.58 rows=8 width=8
  • Index Cond: (model_id = t_1.id)
35. 22.432 22.442 ↑ 2.6 36 22,442

Materialize (cost=0.00..3.40 rows=93 width=53) (actual time=0.000..0.001 rows=36 loops=22,442)

36. 0.010 0.010 ↑ 1.0 93 1

Seq Scan on tbl_profiles pr_3 (cost=0.00..2.93 rows=93 width=53) (actual time=0.002..0.010 rows=93 loops=1)

37. 5.999 14,121.454 ↓ 2.0 3,481 1

Seq Scan on model_requestperson t_2 (cost=0.00..4,910,993.08 rows=1,724 width=383) (actual time=3.857..14,121.454 rows=3,481 loops=1)

  • Filter: (NOT COALESCE(is_deleted, false))
  • Rows Removed by Filter: 5
38.          

SubPlan (forSeq Scan)

39. 3.481 13.924 ↑ 1.0 1 3,481

Aggregate (cost=2.51..2.52 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=3,481)

40. 10.443 10.443 ↑ 1.0 1 3,481

Index Scan using document_file_doc_guid on document_file document_file_2 (cost=0.29..2.51 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=3,481)

  • Index Cond: (doc_guid = t_2.guid)
41. 3.481 14,101.531 ↑ 1.0 1 3,481

Limit (cost=2,845.95..2,845.96 rows=1 width=32) (actual time=4.051..4.051 rows=1 loops=3,481)

42. 0.000 14,098.050 ↑ 1.0 1 3,481

Subquery Scan on vc_2 (cost=2,845.95..2,845.96 rows=1 width=32) (actual time=4.050..4.050 rows=1 loops=3,481)

43. 13.924 14,098.050 ↑ 1.0 1 3,481

Sort (cost=2,845.95..2,845.95 rows=1 width=100) (actual time=4.050..4.050 rows=1 loops=3,481)

  • Sort Key: (comments_2."array"[3]) DESC
  • Sort Method: quicksort Memory: 25kB
44. 6.962 14,084.126 ↓ 3.0 3 3,481

Subquery Scan on comments_2 (cost=3.15..2,845.94 rows=1 width=100) (actual time=1.852..4.046 rows=3 loops=3,481)

  • Filter: ((comments_2.type)::text = 'RequestPerson'::text)
  • Rows Removed by Filter: 16
45. 6.962 14,077.164 ↓ 1.9 19 3,481

Append (cost=3.15..2,845.81 rows=10 width=68) (actual time=0.130..4.044 rows=19 loops=3,481)

46. 23.071 45.253 ↑ 2.0 1 3,481

Hash Right Join (cost=3.15..6.62 rows=2 width=44) (actual time=0.008..0.013 rows=1 loops=3,481)

  • Hash Cond: (pr_4.user_id = mc_4.author_id)
47. 11.739 11.739 ↑ 1.0 93 1,677

Seq Scan on tbl_profiles pr_4 (cost=0.00..2.93 rows=93 width=53) (actual time=0.002..0.007 rows=93 loops=1,677)

48. 10.443 10.443 ↑ 2.0 1 3,481

Hash (cost=3.13..3.13 rows=2 width=126) (actual time=0.003..0.003 rows=1 loops=3,481)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • -> Index Scan using model_comment_commentable_id_commentable_type_idx on model_comment mc_4 (cost=0.29..3.13 rows=2 width=126) (actual time
  • Index Cond: (commentable_id = t_2.id)
49. 6.962 14,024.949 ↓ 2.2 18 3,481

Subquery Scan on *SELECT* 2_2 (cost=0.42..2,839.18 rows=8 width=68) (actual time=0.180..4.029 rows=18 loops=3,481)

50. 13,955.657 14,017.987 ↓ 2.2 18 3,481

Nested Loop Left Join (cost=0.42..2,839.10 rows=8 width=43) (actual time=0.180..4.027 rows=18 loops=3,481)

  • Join Filter: (pr_5.user_id = mc_5.user_id)
  • Rows Removed by Join Filter: 680
  • -> Index Scan using model_document_status_log_model_class_model_id_idx on model_document_status_log mc_5 (cost=0.42..2824.58 rows=8 width=8
  • Index Cond: (model_id = t_2.id)
51. 62.318 62.330 ↑ 2.4 39 62,330

Materialize (cost=0.00..3.40 rows=93 width=53) (actual time=0.000..0.001 rows=39 loops=62,330)

52. 0.012 0.012 ↑ 1.0 93 1

Seq Scan on tbl_profiles pr_5 (cost=0.00..2.93 rows=93 width=53) (actual time=0.002..0.012 rows=93 loops=1)

53. 0.013 16.013 ↑ 32.5 4 1

Seq Scan on model_requestcommercialoffer t_3 (cost=0.00..370,315.53 rows=130 width=382) (actual time=3.993..16.013 rows=4 loops=1)

  • Filter: (NOT COALESCE(is_deleted, false))
54.          

SubPlan (forSeq Scan)

55. 0.004 0.016 ↑ 1.0 1 4

Aggregate (cost=2.51..2.52 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=4)

56. 0.012 0.012 ↓ 0.0 0 4

Index Scan using document_file_doc_guid on document_file document_file_3 (cost=0.29..2.51 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=4)

  • Index Cond: (doc_guid = t_3.guid)
57. 0.000 15.984 ↑ 1.0 1 4

Limit (cost=2,845.95..2,845.96 rows=1 width=32) (actual time=3.996..3.996 rows=1 loops=4)

58. 0.004 15.984 ↑ 1.0 1 4

Subquery Scan on vc_3 (cost=2,845.95..2,845.96 rows=1 width=32) (actual time=3.996..3.996 rows=1 loops=4)

59. 0.020 15.980 ↑ 1.0 1 4

Sort (cost=2,845.95..2,845.95 rows=1 width=100) (actual time=3.995..3.995 rows=1 loops=4)

  • Sort Key: (comments_3."array"[3]) DESC
  • Sort Method: quicksort Memory: 25kB
60. 0.012 15.960 ↓ 2.0 2 4

Subquery Scan on comments_3 (cost=3.15..2,845.94 rows=1 width=100) (actual time=1.726..3.990 rows=2 loops=4)

  • Filter: ((comments_3.type)::text = 'RequestCommercialOffer'::text)
  • Rows Removed by Filter: 36
61. 0.012 15.948 ↓ 3.8 38 4

Append (cost=3.15..2,845.81 rows=10 width=68) (actual time=0.014..3.987 rows=38 loops=4)

62. 0.048 0.092 ↑ 1.0 2 4

Hash Right Join (cost=3.15..6.62 rows=2 width=44) (actual time=0.013..0.023 rows=2 loops=4)

  • Hash Cond: (pr_6.user_id = mc_6.author_id)
63. 0.024 0.024 ↑ 1.0 93 4

Seq Scan on tbl_profiles pr_6 (cost=0.00..2.93 rows=93 width=53) (actual time=0.002..0.006 rows=93 loops=4)

64. 0.020 0.020 ↑ 1.0 2 4

Hash (cost=3.13..3.13 rows=2 width=126) (actual time=0.005..0.005 rows=2 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Index Scan using model_comment_commentable_id_commentable_type_idx on model_comment mc_6 (cost=0.29..3.13 rows=2 width=126) (actual time
  • Index Cond: (commentable_id = t_3.id)
65. 0.012 15.844 ↓ 4.5 36 4

Subquery Scan on *SELECT* 2_3 (cost=0.42..2,839.18 rows=8 width=68) (actual time=0.043..3.961 rows=36 loops=4)

66. 15.689 15.832 ↓ 4.5 36 4

Nested Loop Left Join (cost=0.42..2,839.10 rows=8 width=43) (actual time=0.042..3.958 rows=36 loops=4)

  • Join Filter: (pr_7.user_id = mc_7.user_id)
  • Rows Removed by Join Filter: 1190
  • -> Index Scan using model_document_status_log_model_class_model_id_idx on model_document_status_log mc_7 (cost=0.42..2824.58 rows=8 width=8
  • Index Cond: (model_id = t_3.id)
67. 0.133 0.143 ↑ 2.7 34 143

Materialize (cost=0.00..3.40 rows=93 width=53) (actual time=0.000..0.001 rows=34 loops=143)

68. 0.010 0.010 ↑ 1.0 93 1

Seq Scan on tbl_profiles pr_7 (cost=0.00..2.93 rows=93 width=53) (actual time=0.002..0.010 rows=93 loops=1)