explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x1JG

Settings
# exclusive inclusive rows x rows loops node
1. 10.177 1,785.715 ↑ 1.0 1 1

Nested Loop Left Join (cost=43,925.14..46,834.47 rows=1 width=4,798) (actual time=1,522.061..1,785.715 rows=1 loops=1)

  • Join Filter: (p0_.id = no.project_id)
  • Rows Removed by Join Filter: 12866
2. 9.724 1,405.532 ↑ 1.0 1 1

Nested Loop Left Join (cost=38,590.34..40,867.70 rows=1 width=4,172) (actual time=1,297.223..1,405.532 rows=1 loops=1)

  • Join Filter: (p0_.id = p_5.id)
  • Rows Removed by Join Filter: 12866
3. 0.014 1,066.740 ↑ 1.0 1 1

Nested Loop Left Join (cost=30,403.70..32,262.16 rows=1 width=4,148) (actual time=985.659..1,066.740 rows=1 loops=1)

4. 10.373 1,066.610 ↑ 1.0 1 1

Nested Loop Left Join (cost=30,403.13..32,253.46 rows=1 width=3,770) (actual time=985.547..1,066.610 rows=1 loops=1)

  • Join Filter: (p0_.id = p_4.id)
  • Rows Removed by Join Filter: 12876
5. 9.798 766.626 ↑ 1.0 1 1

Nested Loop Left Join (cost=22,069.75..23,501.19 rows=1 width=3,754) (actual time=713.694..766.626 rows=1 loops=1)

  • Join Filter: (p0_.id = p_3.id)
  • Rows Removed by Join Filter: 12876
6. 9.813 556.542 ↑ 1.0 1 1

Nested Loop Left Join (cost=14,857.20..15,869.75 rows=1 width=3,738) (actual time=530.257..556.542 rows=1 loops=1)

  • Join Filter: (p0_.id = p_2.id)
  • Rows Removed by Join Filter: 12876
7. 0.015 266.336 ↑ 1.0 1 1

Nested Loop Left Join (cost=6,762.57..7,356.23 rows=1 width=3,722) (actual time=266.202..266.336 rows=1 loops=1)

8. 0.018 266.291 ↑ 1.0 1 1

Nested Loop Left Join (cost=6,762.30..7,347.93 rows=1 width=3,280) (actual time=266.161..266.291 rows=1 loops=1)

9. 0.061 266.211 ↑ 1.0 1 1

Nested Loop Left Join (cost=6,758.00..7,335.80 rows=1 width=2,814) (actual time=266.093..266.211 rows=1 loops=1)

  • Join Filter: (p0_.company_id = c1_.id)
  • Rows Removed by Join Filter: 29
10. 2.453 266.098 ↑ 1.0 1 1

Merge Left Join (cost=6,758.00..7,334.13 rows=1 width=1,918) (actual time=266.040..266.098 rows=1 loops=1)

  • Merge Cond: (p0_.id = p_1.id)
11. 0.024 9.410 ↑ 1.0 1 1

Sort (cost=946.12..946.13 rows=1 width=1,902) (actual time=9.409..9.410 rows=1 loops=1)

  • Sort Key: p0_.id
  • Sort Method: quicksort Memory: 25kB
12. 9.386 9.386 ↑ 1.0 1 1

Seq Scan on project p0_ (cost=0.00..946.11 rows=1 width=1,902) (actual time=5.502..9.386 rows=1 loops=1)

  • Filter: ((secure_id)::text = 'y7FXQytt'::text)
  • Rows Removed by Filter: 12889
13. 4.513 254.235 ↑ 4.6 2,821 1

Materialize (cost=5,811.87..6,355.77 rows=12,889 width=16) (actual time=237.315..254.235 rows=2,821 loops=1)

14. 9.100 249.722 ↑ 4.6 2,821 1

GroupAggregate (cost=5,811.87..6,194.65 rows=12,889 width=12) (actual time=237.306..249.722 rows=2,821 loops=1)

  • Group Key: p_1.id
15. 39.570 240.622 ↑ 11.7 2,883 1

Sort (cost=5,811.87..5,896.50 rows=33,852 width=12) (actual time=237.256..240.622 rows=2,883 loops=1)

  • Sort Key: p_1.id
  • Sort Method: quicksort Memory: 2953kB
16. 68.060 201.052 ↓ 1.0 33,855 1

Hash Join (cost=2,750.96..3,265.03 rows=33,852 width=12) (actual time=98.721..201.052 rows=33,855 loops=1)

  • Hash Cond: (doc.project_id = p_1.id)
17. 34.691 106.426 ↓ 1.0 33,855 1

Hash Right Join (cost=1,675.96..1,724.56 rows=33,852 width=12) (actual time=72.074..106.426 rows=33,855 loops=1)

  • Hash Cond: (ri2.document_id = doc.id)
18. 0.212 0.212 ↓ 1.1 17 1

Index Scan using issue_7014ab92ee4ff39cee23e7b39ea88c92 on issue ri2 (cost=0.29..48.68 rows=15 width=8) (actual time=0.035..0.212 rows=17 loops=1)

  • Index Cond: (issue_status_id = 3)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 2
19. 35.294 71.523 ↓ 1.0 33,854 1

Hash (cost=1,252.52..1,252.52 rows=33,852 width=12) (actual time=71.523..71.523 rows=33,854 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1967kB
20. 36.229 36.229 ↓ 1.0 33,854 1

Seq Scan on document doc (cost=0.00..1,252.52 rows=33,852 width=12) (actual time=0.037..36.229 rows=33,854 loops=1)

21. 12.507 26.566 ↓ 1.0 12,890 1

Hash (cost=913.89..913.89 rows=12,889 width=8) (actual time=26.566..26.566 rows=12,890 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 632kB
22. 14.059 14.059 ↓ 1.0 12,890 1

Seq Scan on project p_1 (cost=0.00..913.89 rows=12,889 width=8) (actual time=0.007..14.059 rows=12,890 loops=1)

23. 0.052 0.052 ↑ 1.0 30 1

Seq Scan on company c1_ (cost=0.00..1.30 rows=30 width=896) (actual time=0.028..0.052 rows=30 loops=1)

24. 0.027 0.062 ↑ 1.0 1 1

Bitmap Heap Scan on project_cover p2_ (cost=4.30..12.11 rows=1 width=466) (actual time=0.056..0.062 rows=1 loops=1)

  • Recheck Cond: (p0_.id = project_id)
  • Filter: is_active_flag
  • Heap Blocks: exact=1
25. 0.035 0.035 ↑ 2.0 1 1

Bitmap Index Scan on project_cover_b423e462d8dae182f829545d6bd17a70 (cost=0.00..4.30 rows=2 width=0) (actual time=0.035..0.035 rows=1 loops=1)

  • Index Cond: (p0_.id = project_id)
26. 0.030 0.030 ↓ 0.0 0 1

Index Scan using project_banner_b423e462d8dae182f829545d6bd17a70 on project_banner p3_ (cost=0.28..8.29 rows=1 width=442) (actual time=0.030..0.030 rows=0 loops=1)

  • Index Cond: (p0_.id = project_id)
  • Filter: is_active_flag
27. 51.182 280.393 ↑ 1.0 12,877 1

HashAggregate (cost=8,094.63..8,223.52 rows=12,889 width=12) (actual time=264.043..280.393 rows=12,877 loops=1)

  • Group Key: p_2.id
28. 55.796 229.211 ↓ 1.0 34,907 1

Hash Join (cost=2,750.67..7,925.37 rows=33,852 width=12) (actual time=105.827..229.211 rows=34,907 loops=1)

  • Hash Cond: (doc_1.project_id = p_2.id)
29. 33.859 141.570 ↓ 1.0 34,907 1

Hash Right Join (cost=1,675.67..6,384.90 rows=33,852 width=12) (actual time=73.908..141.570 rows=34,907 loops=1)

  • Hash Cond: (ri1.document_id = doc_1.id)
30. 34.150 34.150 ↑ 1.4 3,377 1

Seq Scan on issue ri1 (cost=0.00..4,642.48 rows=4,855 width=8) (actual time=0.032..34.150 rows=3,377 loops=1)

  • Filter: ((deleted_at IS NULL) AND (issue_status_id = 1))
  • Rows Removed by Filter: 27861
31. 35.965 73.561 ↓ 1.0 33,854 1

Hash (cost=1,252.52..1,252.52 rows=33,852 width=12) (actual time=73.561..73.561 rows=33,854 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1967kB
32. 37.596 37.596 ↓ 1.0 33,854 1

Seq Scan on document doc_1 (cost=0.00..1,252.52 rows=33,852 width=12) (actual time=0.040..37.596 rows=33,854 loops=1)

33. 13.132 31.845 ↓ 1.0 12,890 1

Hash (cost=913.89..913.89 rows=12,889 width=8) (actual time=31.845..31.845 rows=12,890 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 632kB
34. 18.713 18.713 ↓ 1.0 12,890 1

Seq Scan on project p_2 (cost=0.00..913.89 rows=12,889 width=8) (actual time=0.021..18.713 rows=12,890 loops=1)

35. 46.735 200.286 ↑ 1.0 12,877 1

HashAggregate (cost=7,212.54..7,341.43 rows=12,889 width=12) (actual time=183.424..200.286 rows=12,877 loops=1)

  • Group Key: p_3.id
36. 49.088 153.551 ↓ 1.0 35,307 1

Hash Join (cost=2,797.03..7,043.28 rows=33,852 width=12) (actual time=71.804..153.551 rows=35,307 loops=1)

  • Hash Cond: (doc_2.project_id = p_3.id)
37. 28.221 84.593 ↓ 1.0 35,307 1

Hash Right Join (cost=1,722.02..5,502.82 rows=33,852 width=12) (actual time=51.838..84.593 rows=35,307 loops=1)

  • Hash Cond: (ri4.document_id = doc_2.id)
38. 4.942 5.182 ↓ 1.0 2,198 1

Bitmap Heap Scan on issue ri4 (cost=46.35..3,797.05 rows=2,189 width=8) (actual time=0.307..5.182 rows=2,198 loops=1)

  • Recheck Cond: (issue_status_id = 4)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 121
  • Heap Blocks: exact=492
39. 0.240 0.240 ↑ 1.0 2,319 1

Bitmap Index Scan on issue_7014ab92ee4ff39cee23e7b39ea88c92 (cost=0.00..45.81 rows=2,336 width=0) (actual time=0.240..0.240 rows=2,319 loops=1)

  • Index Cond: (issue_status_id = 4)
40. 25.243 51.190 ↓ 1.0 33,854 1

Hash (cost=1,252.52..1,252.52 rows=33,852 width=12) (actual time=51.190..51.190 rows=33,854 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1967kB
41. 25.947 25.947 ↓ 1.0 33,854 1

Seq Scan on document doc_2 (cost=0.00..1,252.52 rows=33,852 width=12) (actual time=0.019..25.947 rows=33,854 loops=1)

42. 8.168 19.870 ↓ 1.0 12,890 1

Hash (cost=913.89..913.89 rows=12,889 width=8) (actual time=19.870..19.870 rows=12,890 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 632kB
43. 11.702 11.702 ↓ 1.0 12,890 1

Seq Scan on project p_3 (cost=0.00..913.89 rows=12,889 width=8) (actual time=0.020..11.702 rows=12,890 loops=1)

44. 61.629 289.611 ↑ 1.0 12,877 1

HashAggregate (cost=8,333.38..8,462.27 rows=12,889 width=12) (actual time=271.837..289.611 rows=12,877 loops=1)

  • Group Key: p_4.id
45. 72.057 227.982 ↓ 1.6 54,028 1

Hash Join (cost=2,750.67..8,164.12 rows=33,852 width=12) (actual time=66.552..227.982 rows=54,028 loops=1)

  • Hash Cond: (doc_3.project_id = p_4.id)
46. 56.456 136.257 ↓ 1.6 54,028 1

Hash Right Join (cost=1,675.67..6,623.66 rows=33,852 width=12) (actual time=46.792..136.257 rows=54,028 loops=1)

  • Hash Cond: (ri3.document_id = doc_3.id)
47. 33.317 33.317 ↓ 1.1 23,690 1

Seq Scan on issue ri3 (cost=0.00..4,642.48 rows=22,219 width=8) (actual time=0.011..33.317 rows=23,690 loops=1)

  • Filter: ((deleted_at IS NULL) AND (issue_status_id = 2))
  • Rows Removed by Filter: 7548
48. 23.548 46.484 ↓ 1.0 33,854 1

Hash (cost=1,252.52..1,252.52 rows=33,852 width=12) (actual time=46.484..46.484 rows=33,854 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1967kB
49. 22.936 22.936 ↓ 1.0 33,854 1

Seq Scan on document doc_3 (cost=0.00..1,252.52 rows=33,852 width=12) (actual time=0.018..22.936 rows=33,854 loops=1)

50. 8.278 19.668 ↓ 1.0 12,890 1

Hash (cost=913.89..913.89 rows=12,889 width=8) (actual time=19.668..19.668 rows=12,890 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 632kB
51. 11.390 11.390 ↓ 1.0 12,890 1

Seq Scan on project p_4 (cost=0.00..913.89 rows=12,889 width=8) (actual time=0.019..11.390 rows=12,890 loops=1)

52. 0.015 0.116 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.57..8.69 rows=1 width=378) (actual time=0.103..0.116 rows=1 loops=1)

53. 0.054 0.054 ↑ 1.0 1 1

Index Scan using project_pkey on project p (cost=0.29..8.30 rows=1 width=12) (actual time=0.049..0.054 rows=1 loops=1)

  • Index Cond: (p0_.id = id)
54. 0.047 0.047 ↑ 1.0 1 1

Index Scan using endpoint_project_export_status_b423e462d8dae182f829545d6bd17a70 on endpoint_project_export_status epes (cost=0.28..0.37 rows=1 width=381) (actual time=0.041..0.047 rows=1 loops=1)

  • Index Cond: (p.id = project_id)
  • Filter: (endpoint_id = p.endpoint_id)
55. 72.992 329.068 ↑ 1.0 12,867 1

HashAggregate (cost=8,186.65..8,315.54 rows=12,889 width=20) (actual time=311.545..329.068 rows=12,867 loops=1)

  • Group Key: p_5.id
56. 85.952 256.076 ↓ 1.8 51,410 1

Hash Join (cost=2,676.87..7,977.04 rows=27,948 width=20) (actual time=56.880..256.076 rows=51,410 loops=1)

  • Hash Cond: (doc_4.project_id = p_5.id)
57. 72.656 152.328 ↓ 1.8 51,410 1

Hash Right Join (cost=1,601.87..6,517.75 rows=27,948 width=20) (actual time=38.952..152.328 rows=51,410 loops=1)

  • Hash Cond: (ri.document_id = doc_4.id)
58. 40.895 40.895 ↓ 1.0 29,282 1

Seq Scan on issue ri (cost=0.00..4,564.38 rows=29,277 width=16) (actual time=0.005..40.895 rows=29,282 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 1956
59. 18.814 38.777 ↓ 1.0 27,967 1

Hash (cost=1,252.52..1,252.52 rows=27,948 width=12) (actual time=38.777..38.777 rows=27,967 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1458kB
60. 19.963 19.963 ↓ 1.0 27,967 1

Seq Scan on document doc_4 (cost=0.00..1,252.52 rows=27,948 width=12) (actual time=0.013..19.963 rows=27,967 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 5887
61. 8.501 17.796 ↓ 1.0 12,890 1

Hash (cost=913.89..913.89 rows=12,889 width=8) (actual time=17.796..17.796 rows=12,890 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 632kB
62. 9.295 9.295 ↓ 1.0 12,890 1

Seq Scan on project p_5 (cost=0.00..913.89 rows=12,889 width=8) (actual time=0.007..9.295 rows=12,890 loops=1)

63. 370.006 370.006 ↓ 91.9 12,867 1

CTE Scan on normalized_orders no (cost=5,334.79..5,963.62 rows=140 width=626) (actual time=201.689..370.006 rows=12,867 loops=1)

  • Filter: ("order" = 1)
  • Rows Removed by Filter: 15100
64.          

CTE normalized_orders

65. 66.132 312.481 ↓ 1.0 27,967 1

WindowAgg (cost=4,775.83..5,334.79 rows=27,948 width=64) (actual time=201.674..312.481 rows=27,967 loops=1)

66. 115.031 246.349 ↓ 1.0 27,967 1

Sort (cost=4,775.83..4,845.70 rows=27,948 width=64) (actual time=201.652..246.349 rows=27,967 loops=1)

  • Sort Key: d.project_id, d.order_number
  • Sort Method: external merge Disk: 2136kB
67. 54.383 131.318 ↓ 1.0 27,967 1

Hash Join (cost=1,075.00..2,711.81 rows=27,948 width=64) (actual time=32.456..131.318 rows=27,967 loops=1)

  • Hash Cond: (d.project_id = p_6.id)
68. 44.676 44.676 ↓ 1.0 27,967 1

Seq Scan on document d (cost=0.00..1,252.52 rows=27,948 width=56) (actual time=0.038..44.676 rows=27,967 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 5887
69. 13.271 32.259 ↓ 1.0 12,890 1

Hash (cost=913.89..913.89 rows=12,889 width=8) (actual time=32.259..32.259 rows=12,890 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 632kB
70. 18.988 18.988 ↓ 1.0 12,890 1

Seq Scan on project p_6 (cost=0.00..913.89 rows=12,889 width=8) (actual time=0.019..18.988 rows=12,890 loops=1)

Planning time : 10.560 ms
Execution time : 1,790.263 ms