explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Id8Uj

Settings
# exclusive inclusive rows x rows loops node
1. 0.030 2,113.307 ↑ 1.0 50 1

Limit (cost=4,291,765,458.39..5,059,437,889.22 rows=50 width=352) (actual time=1,413.041..2,113.307 rows=50 loops=1)

2. 0.161 2,113.277 ↑ 14,190.2 50 1

Result (cost=4,291,765,458.39..10,897,747,800,339.54 rows=709,512 width=352) (actual time=1,413.040..2,113.277 rows=50 loops=1)

3. 4.597 1,391.916 ↑ 14,190.2 50 1

Sort (cost=4,291,765,458.39..4,291,767,232.17 rows=709,512 width=320) (actual time=1,391.900..1,391.916 rows=50 loops=1)

  • Sort Key: pb.name
  • Sort Method: top-N heapsort Memory: 97kB
4. 51.397 1,387.319 ↑ 102.0 6,956 1

WindowAgg (cost=1,425,848,881.79..4,291,741,888.91 rows=709,512 width=320) (actual time=1,345.679..1,387.319 rows=6,956 loops=1)

5. 122.329 1,335.922 ↑ 102.0 6,956 1

GroupAggregate (cost=1,425,848,881.79..4,291,729,472.45 rows=709,512 width=457) (actual time=1,209.562..1,335.922 rows=6,956 loops=1)

  • Group Key: pb.id, pbd.id
6. 22.114 1,213.593 ↑ 99,918.8 14,199 1

Sort (cost=1,425,848,881.79..1,429,395,750.54 rows=1,418,747,499 width=353) (actual time=1,209.441..1,213.593 rows=14,199 loops=1)

  • Sort Key: pb.id, pbd.id
  • Sort Method: external merge Disk: 4,360kB
7. 4.333 1,191.479 ↑ 99,918.8 14,199 1

Hash Left Join (cost=9,021,626.63..46,368,957.29 rows=1,418,747,499 width=353) (actual time=1,081.586..1,191.479 rows=14,199 loops=1)

  • Hash Cond: (COALESCE(pbd.updated_by, pb.updated_by) = uu.id)
8. 2.964 1,187.120 ↑ 99,918.8 14,199 1

Hash Left Join (cost=9,021,607.18..42,565,807.83 rows=1,418,747,499 width=330) (actual time=1,081.548..1,187.120 rows=14,199 loops=1)

  • Hash Cond: (pb.created_by = cu.id)
9. 139.312 1,184.115 ↑ 99,918.8 14,199 1

Hash Right Join (cost=9,021,587.73..38,735,059.24 rows=1,418,747,499 width=291) (actual time=1,081.490..1,184.115 rows=14,199 loops=1)

  • Hash Cond: ((childcategory.id)::text = cat_ids.id)
10. 279.281 907.835 ↓ 1.0 2,226 1

Nested Loop Left Join (cost=0.00..148,606.72 rows=2,223 width=48) (actual time=0.058..907.835 rows=2,226 loops=1)

  • Join Filter: (parentcategory.parent_id = grandparentcategory.id)
  • Rows Removed by Join Filter: 4,248,638
11. 260.505 432.666 ↓ 1.0 2,226 1

Nested Loop Left Join (cost=0.00..74,360.64 rows=2,223 width=160) (actual time=0.030..432.666 rows=2,226 loops=1)

  • Join Filter: (childcategory.parent_id = parentcategory.id)
  • Rows Removed by Join Filter: 3,536,423
12. 0.759 0.759 ↓ 1.0 2,226 1

Seq Scan on categories childcategory (cost=0.00..131.23 rows=2,223 width=88) (actual time=0.003..0.759 rows=2,226 loops=1)

13. 170.954 171.402 ↑ 1.4 1,589 2,226

Materialize (cost=0.00..142.34 rows=2,223 width=88) (actual time=0.000..0.077 rows=1,589 loops=2,226)

14. 0.448 0.448 ↓ 1.0 2,226 1

Seq Scan on categories parentcategory (cost=0.00..131.23 rows=2,223 width=88) (actual time=0.001..0.448 rows=2,226 loops=1)

15. 195.559 195.888 ↑ 1.2 1,909 2,226

Materialize (cost=0.00..142.34 rows=2,223 width=72) (actual time=0.000..0.088 rows=1,909 loops=2,226)

16. 0.329 0.329 ↓ 1.0 2,226 1

Seq Scan on categories grandparentcategory (cost=0.00..131.23 rows=2,223 width=72) (actual time=0.001..0.329 rows=2,226 loops=1)

17. 9.780 136.968 ↑ 8,989.5 14,199 1

Hash (cost=2,689,317.23..2,689,317.23 rows=127,642,600 width=275) (actual time=136.968..136.968 rows=14,199 loops=1)

  • Buckets: 16,384 Batches: 16,384 Memory Usage: 1,661kB
18. 2.322 127.188 ↑ 8,989.5 14,199 1

Nested Loop Left Join (cost=43.26..2,689,317.23 rows=127,642,600 width=275) (actual time=0.403..127.188 rows=14,199 loops=1)

19. 4.431 111.068 ↑ 92.5 13,798 1

Hash Left Join (cost=43.26..136,465.23 rows=1,276,426 width=378) (actual time=0.398..111.068 rows=13,798 loops=1)

  • Hash Cond: (adv_ids.id = (adv.id)::text)
20. 4.022 106.401 ↑ 50.4 13,798 1

Nested Loop Left Join (cost=0.00..25,404.21 rows=695,600 width=384) (actual time=0.152..106.401 rows=13,798 loops=1)

21. 64.390 95.423 ↑ 1.0 6,956 1

Nested Loop Left Join (cost=0.00..11,492.21 rows=6,956 width=546) (actual time=0.146..95.423 rows=6,956 loops=1)

  • Join Filter: (pbd.parent_brand_id = pb.id)
  • Rows Removed by Join Filter: 674,635
22. 3.209 3.209 ↑ 1.0 6,956 1

Seq Scan on parent_brands pb (cost=0.00..820.93 rows=6,956 width=328) (actual time=0.004..3.209 rows=6,956 loops=1)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 37
23. 27.728 27.824 ↑ 1.1 97 6,956

Materialize (cost=0.00..28.86 rows=102 width=234) (actual time=0.000..0.004 rows=97 loops=6,956)

24. 0.096 0.096 ↑ 1.1 97 1

Seq Scan on parent_brand_drafts pbd (cost=0.00..28.34 rows=102 width=234) (actual time=0.004..0.096 rows=97 loops=1)

  • Filter: ((status = 'Pending'::text) AND (updated_by_company_id = '224629d9-701d-48f4-9b75-051c1ca4a382'::uuid))
  • Rows Removed by Filter: 126
25. 6.956 6.956 ↑ 50.0 2 6,956

Function Scan on unnest adv_ids (cost=0.00..1.00 rows=100 width=32) (actual time=0.001..0.001 rows=2 loops=6,956)

26. 0.138 0.236 ↓ 1.0 370 1

Hash (cost=38.67..38.67 rows=367 width=26) (actual time=0.236..0.236 rows=370 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
27. 0.098 0.098 ↓ 1.0 370 1

Seq Scan on advertisers adv (cost=0.00..38.67 rows=367 width=26) (actual time=0.001..0.098 rows=370 loops=1)

28. 13.798 13.798 ↑ 100.0 1 13,798

Function Scan on unnest cat_ids (cost=0.00..1.00 rows=100 width=32) (actual time=0.001..0.001 rows=1 loops=13,798)

29. 0.009 0.041 ↑ 1.0 20 1

Hash (cost=19.20..19.20 rows=20 width=55) (actual time=0.041..0.041 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
30. 0.032 0.032 ↑ 1.0 20 1

Seq Scan on users cu (cost=0.00..19.20 rows=20 width=55) (actual time=0.006..0.032 rows=20 loops=1)

31. 0.007 0.026 ↑ 1.0 20 1

Hash (cost=19.20..19.20 rows=20 width=55) (actual time=0.026..0.026 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
32. 0.019 0.019 ↑ 1.0 20 1

Seq Scan on users uu (cost=0.00..19.20 rows=20 width=55) (actual time=0.001..0.019 rows=20 loops=1)

33.          

SubPlan (for Result)

34. 0.850 721.200 ↑ 1.0 1 50

Aggregate (cost=15,353,448.59..15,353,448.60 rows=1 width=32) (actual time=14.424..14.424 rows=1 loops=50)

35. 1.150 720.350 ↑ 756.0 1 50

GroupAggregate (cost=3,817,426.61..15,353,433.47 rows=756 width=328) (actual time=14.406..14.407 rows=1 loops=50)

  • Group Key: ib.id, ibd.id
36. 0.350 719.200 ↑ 5,710,887.0 1 50

Sort (cost=3,817,426.61..3,831,703.82 rows=5,710,887 width=379) (actual time=14.383..14.384 rows=1 loops=50)

  • Sort Key: ib.id, ibd.id
  • Sort Method: quicksort Memory: 25kB
37. 0.080 718.850 ↑ 5,710,887.0 1 50

Hash Left Join (cost=38,580.71..190,009.72 rows=5,710,887 width=379) (actual time=13.126..14.377 rows=1 loops=50)

  • Hash Cond: (COALESCE(ibd.updated_by, ib.updated_by) = ibuu.id)
38. 0.216 718.750 ↑ 5,710,887.0 1 50

Hash Left Join (cost=38,561.26..174,681.53 rows=5,710,887 width=356) (actual time=13.124..14.375 rows=1 loops=50)

  • Hash Cond: (ib.created_by = ibcu.id)
39. 157.886 718.500 ↑ 5,710,887.0 1 50

Hash Right Join (cost=38,541.81..159,260.49 rows=5,710,887 width=317) (actual time=13.119..14.370 rows=1 loops=50)

  • Hash Cond: ((childcategory_1.id)::text = cat_ids1.id)
40. 322.194 352.064 ↓ 1.0 2,226 32

Hash Left Join (cost=318.03..477.63 rows=2,223 width=48) (actual time=0.066..11.002 rows=2,226 loops=32)

  • Hash Cond: (parentcategory_1.parent_id = grandparentcategory_1.id)
41. 18.968 29.120 ↓ 1.0 2,226 32

Hash Left Join (cost=159.02..296.09 rows=2,223 width=160) (actual time=0.032..0.910 rows=2,226 loops=32)

  • Hash Cond: (childcategory_1.parent_id = parentcategory_1.id)
42. 9.216 9.216 ↓ 1.0 2,226 32

Seq Scan on categories childcategory_1 (cost=0.00..131.23 rows=2,223 width=88) (actual time=0.001..0.288 rows=2,226 loops=32)

43. 0.473 0.936 ↓ 1.0 2,226 1

Hash (cost=131.23..131.23 rows=2,223 width=88) (actual time=0.936..0.936 rows=2,226 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 279kB
44. 0.463 0.463 ↓ 1.0 2,226 1

Seq Scan on categories parentcategory_1 (cost=0.00..131.23 rows=2,223 width=88) (actual time=0.001..0.463 rows=2,226 loops=1)

45. 0.420 0.750 ↓ 1.0 2,226 1

Hash (cost=131.23..131.23 rows=2,223 width=72) (actual time=0.750..0.750 rows=2,226 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 259kB
46. 0.330 0.330 ↓ 1.0 2,226 1

Seq Scan on categories grandparentcategory_1 (cost=0.00..131.23 rows=2,223 width=72) (actual time=0.002..0.330 rows=2,226 loops=1)

47. 0.550 208.550 ↑ 513,800.0 1 50

Hash (cost=11,228.28..11,228.28 rows=513,800 width=301) (actual time=4.171..4.171 rows=1 loops=50)

  • Buckets: 16,384 Batches: 64 Memory Usage: 128kB
48. 0.142 208.000 ↑ 513,800.0 1 50

Nested Loop Left Join (cost=822.78..11,228.28 rows=513,800 width=301) (actual time=4.105..4.160 rows=1 loops=50)

49. 4.350 207.750 ↑ 5,138.0 1 50

Hash Right Join (cost=822.77..952.28 rows=5,138 width=385) (actual time=4.100..4.155 rows=1 loops=50)

  • Hash Cond: ((adv1.id)::text = adv_ids1.id)
50. 1.600 1.600 ↓ 1.0 370 32

Seq Scan on advertisers adv1 (cost=0.00..38.67 rows=367 width=26) (actual time=0.001..0.050 rows=370 loops=32)

51. 0.100 201.800 ↑ 2,800.0 1 50

Hash (cost=787.77..787.77 rows=2,800 width=391) (actual time=4.036..4.036 rows=1 loops=50)

  • Buckets: 4,096 Batches: 1 Memory Usage: 32kB
52. 0.106 201.700 ↑ 2,800.0 1 50

Nested Loop Left Join (cost=13.46..787.77 rows=2,800 width=391) (actual time=2.545..4.034 rows=1 loops=50)

53. 0.098 201.450 ↑ 28.0 1 50

Hash Left Join (cost=13.46..731.77 rows=28 width=478) (actual time=2.541..4.029 rows=1 loops=50)

  • Hash Cond: (ib.id = ibd.individual_brand_id)
54. 201.300 201.300 ↑ 28.0 1 50

Seq Scan on individual_brands ib (cost=0.00..718.19 rows=28 width=313) (actual time=2.538..4.026 rows=1 loops=50)

  • Filter: ((NOT is_deleted) AND (pb.identifiers @> ARRAY[(parent_brand_id)::text]))
  • Rows Removed by Filter: 5,610
55. 0.010 0.052 ↑ 1.2 22 1

Hash (cost=13.12..13.12 rows=27 width=181) (actual time=0.052..0.052 rows=22 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
56. 0.042 0.042 ↑ 1.2 22 1

Seq Scan on individual_brand_drafts ibd (cost=0.00..13.12 rows=27 width=181) (actual time=0.005..0.042 rows=22 loops=1)

  • Filter: ((status = 'Pending'::text) AND (updated_by_company_id = '224629d9-701d-48f4-9b75-051c1ca4a382'::uuid))
  • Rows Removed by Filter: 53
57. 0.144 0.144 ↑ 100.0 1 36

Function Scan on unnest adv_ids1 (cost=0.00..1.00 rows=100 width=32) (actual time=0.004..0.004 rows=1 loops=36)

58. 0.108 0.108 ↑ 100.0 1 36

Function Scan on unnest cat_ids1 (cost=0.00..1.00 rows=100 width=32) (actual time=0.003..0.003 rows=1 loops=36)

59. 0.008 0.034 ↑ 1.0 20 1

Hash (cost=19.20..19.20 rows=20 width=55) (actual time=0.033..0.034 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
60. 0.026 0.026 ↑ 1.0 20 1

Seq Scan on users ibcu (cost=0.00..19.20 rows=20 width=55) (actual time=0.004..0.026 rows=20 loops=1)

61. 0.007 0.020 ↑ 1.0 20 1

Hash (cost=19.20..19.20 rows=20 width=55) (actual time=0.020..0.020 rows=20 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
62. 0.013 0.013 ↑ 1.0 20 1

Seq Scan on users ibuu (cost=0.00..19.20 rows=20 width=55) (actual time=0.001..0.013 rows=20 loops=1)

Planning time : 4.174 ms
Execution time : 2,115.292 ms