explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZWUp

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 1,364.465 ↑ 1.0 100 1

Limit (cost=4,291,769,005.95..4,291,769,006.20 rows=100 width=320) (actual time=1,364.444..1,364.465 rows=100 loops=1)

2. 5.055 1,364.449 ↑ 7,095.1 100 1

Sort (cost=4,291,769,005.95..4,291,770,779.73 rows=709,512 width=320) (actual time=1,364.443..1,364.449 rows=100 loops=1)

  • Sort Key: pb.name
  • Sort Method: top-N heapsort Memory: 155kB
3. 51.447 1,359.394 ↑ 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,317.669..1,359.394 rows=6,956 loops=1)

4. 120.919 1,307.947 ↑ 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,182.909..1,307.947 rows=6,956 loops=1)

  • Group Key: pb.id, pbd.id
5. 27.859 1,187.028 ↑ 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,182.730..1,187.028 rows=14,199 loops=1)

  • Sort Key: pb.id, pbd.id
  • Sort Method: external merge Disk: 4,360kB
6. 4.155 1,159.169 ↑ 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,052.789..1,159.169 rows=14,199 loops=1)

  • Hash Cond: (COALESCE(pbd.updated_by, pb.updated_by) = uu.id)
7. 2.908 1,154.994 ↑ 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,052.757..1,154.994 rows=14,199 loops=1)

  • Hash Cond: (pb.created_by = cu.id)
8. 136.222 1,152.045 ↑ 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,052.699..1,152.045 rows=14,199 loops=1)

  • Hash Cond: ((childcategory.id)::text = cat_ids.id)
9. 278.324 876.553 ↓ 1.0 2,226 1

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

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

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

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

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

12. 159.812 160.272 ↑ 1.4 1,589 2,226

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

13. 0.460 0.460 ↓ 1.0 2,226 1

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

14. 184.439 184.758 ↑ 1.2 1,909 2,226

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

15. 0.319 0.319 ↓ 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.319 rows=2,226 loops=1)

16. 9.735 139.270 ↑ 8,989.5 14,199 1

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

  • Buckets: 16,384 Batches: 16,384 Memory Usage: 1,661kB
17. 2.508 129.535 ↑ 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.443..129.535 rows=14,199 loops=1)

18. 4.512 113.229 ↑ 92.5 13,798 1

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

  • Hash Cond: (adv_ids.id = (adv.id)::text)
19. 4.016 108.490 ↑ 50.4 13,798 1

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

20. 66.420 97.518 ↑ 1.0 6,956 1

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

  • Join Filter: (pbd.parent_brand_id = pb.id)
  • Rows Removed by Join Filter: 674,635
21. 3.274 3.274 ↑ 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.274 rows=6,956 loops=1)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 37
22. 27.712 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)

23. 0.112 0.112 ↑ 1.1 97 1

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

  • Filter: ((status = 'Pending'::text) AND (updated_by_company_id = '224629d9-701d-48f4-9b75-051c1ca4a382'::uuid))
  • Rows Removed by Filter: 126
24. 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)

25. 0.138 0.227 ↓ 1.0 370 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
26. 0.089 0.089 ↓ 1.0 370 1

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

27. 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)

28. 0.008 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
29. 0.033 0.033 ↑ 1.0 20 1

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

30. 0.006 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
31. 0.014 0.014 ↑ 1.0 20 1

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

Planning time : 2.930 ms
Execution time : 1,366.185 ms