explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VRhQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.047 125,908.872 ↑ 1.0 100 1

Limit (cost=0.00..756,758.12 rows=100 width=518) (actual time=125,230.795..125,908.872 rows=100 loops=1)

2. 78.138 125,908.825 ↑ 4,571.9 100 1

WindowAgg (cost=0.00..3,459,792,188.47 rows=457,186 width=518) (actual time=125,230.794..125,908.825 rows=100 loops=1)

3. 20.489 125,147.887 ↑ 55.6 8,225 1

Nested Loop Left Join (cost=0.00..1,850,029.86 rows=457,186 width=690) (actual time=5.033..125,147.887 rows=8,225 loops=1)

  • Join Filter: false
4. 113,432.880 125,127.398 ↑ 55.6 8,225 1

Nested Loop (cost=0.00..1,845,458.00 rows=457,186 width=683) (actual time=5.032..125,127.398 rows=8,225 loops=1)

  • Join Filter: (pb.identifiers @> ARRAY[(ib.parent_brand_id)::text])
  • Rows Removed by Join Filter: 94217375
5. 83.100 409.818 ↓ 1.0 8,225 1

Nested Loop Left Join (cost=0.00..12,876.88 rows=7,983 width=670) (actual time=4.299..409.818 rows=8,225 loops=1)

  • Join Filter: (uu.id = COALESCE(updated_by, ib.updated_by))
  • Rows Removed by Join Filter: 343856
6. 83.813 252.693 ↓ 1.0 8,225 1

Nested Loop Left Join (cost=0.00..7,256.30 rows=7,983 width=612) (actual time=4.243..252.693 rows=8,225 loops=1)

  • Join Filter: (cu.id = ib.created_by)
  • Rows Removed by Join Filter: 343866
7. 47.222 86.630 ↓ 1.0 8,225 1

Nested Loop Left Join (cost=0.00..1,723.55 rows=7,983 width=538) (actual time=0.005..86.630 rows=8,225 loops=1)

  • Join Filter: false
8. 39.408 39.408 ↓ 1.0 8,225 1

Seq Scan on individual_brands ib (cost=0.00..1,643.72 rows=7,983 width=349) (actual time=0.004..39.408 rows=8,225 loops=1)

  • Filter: ((NOT is_deleted) AND (status = 'Approved'::text))
  • Rows Removed by Filter: 1073
9. 0.000 0.000 ↓ 0.0 0 8,225

Result (cost=0.00..0.00 rows=0 width=189) (actual time=0.000..0.000 rows=0 loops=8,225)

  • One-Time Filter: false
10. 82.198 82.250 ↑ 1.1 43 8,225

Materialize (cost=0.00..19.70 rows=47 width=90) (actual time=0.000..0.010 rows=43 loops=8,225)

11. 0.052 0.052 ↑ 1.1 44 1

Seq Scan on users cu (cost=0.00..19.47 rows=47 width=90) (actual time=0.002..0.052 rows=44 loops=1)

12. 73.997 74.025 ↑ 1.1 43 8,225

Materialize (cost=0.00..19.70 rows=47 width=90) (actual time=0.001..0.009 rows=43 loops=8,225)

13. 0.028 0.028 ↑ 1.1 44 1

Seq Scan on users uu (cost=0.00..19.47 rows=47 width=90) (actual time=0.003..0.028 rows=44 loops=1)

14. 11,262.409 11,284.700 ↓ 1.0 11,456 8,225

Materialize (cost=0.00..3,864.12 rows=11,454 width=91) (actual time=0.001..1.372 rows=11,456 loops=8,225)

15. 22.291 22.291 ↓ 1.0 11,456 1

Seq Scan on parent_brands pb (cost=0.00..3,806.85 rows=11,454 width=91) (actual time=0.003..22.291 rows=11,456 loops=1)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 231
16. 0.000 0.000 ↓ 0.0 0 8,225

Result (cost=0.00..0.00 rows=0 width=7) (actual time=0.000..0.000 rows=0 loops=8,225)

  • One-Time Filter: false
17.          

SubPlan (for WindowAgg)

18. 1.400 2.900 ↑ 1.0 1 100

Aggregate (cost=159.14..159.15 rows=1 width=32) (actual time=0.029..0.029 rows=1 loops=100)

19. 0.200 1.500 ↑ 10.0 1 100

Nested Loop Left Join (cost=39.47..159.04 rows=10 width=219) (actual time=0.015..0.015 rows=1 loops=100)

20. 0.200 1.300 ↑ 10.0 1 100

Nested Loop Left Join (cost=39.19..152.78 rows=10 width=162) (actual time=0.013..0.013 rows=1 loops=100)

21. 0.700 1.100 ↑ 10.0 1 100

Bitmap Heap Scan on categories c1 (cost=38.91..73.78 rows=10 width=89) (actual time=0.011..0.011 rows=1 loops=100)

  • Recheck Cond: (id = ANY ((COALESCE(categories, ib.categories))::uuid[]))
  • Heap Blocks: exact=87
22. 0.400 0.400 ↑ 10.0 1 100

Bitmap Index Scan on categories_pkey (cost=0.00..38.91 rows=10 width=0) (actual time=0.004..0.004 rows=1 loops=100)

  • Index Cond: (id = ANY ((COALESCE(categories, ib.categories))::uuid[]))
23. 0.000 0.000 ↓ 0.0 0 87

Index Scan using categories_pkey on categories c2 (cost=0.28..7.90 rows=1 width=89) (actual time=0.000..0.000 rows=0 loops=87)

  • Index Cond: (c1.parent_id = id)
24. 0.000 0.000 ↓ 0.0 0 87

Index Scan using categories_pkey on categories c3 (cost=0.28..0.63 rows=1 width=73) (actual time=0.000..0.000 rows=0 loops=87)

  • Index Cond: (c2.parent_id = id)
25. 1.000 1.700 ↑ 1.0 1 100

Aggregate (cost=61.29..61.30 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=100)

26. 0.500 0.700 ↑ 10.0 1 100

Bitmap Heap Scan on advertisers adv (cost=30.86..61.24 rows=10 width=23) (actual time=0.007..0.007 rows=1 loops=100)

  • Recheck Cond: (id = ANY ((COALESCE(advertisers, ib.advertisers))::uuid[]))
  • Heap Blocks: exact=89
27. 0.200 0.200 ↑ 10.0 1 100

Bitmap Index Scan on advertisers_pkey (cost=0.00..30.85 rows=10 width=0) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (id = ANY ((COALESCE(advertisers, ib.advertisers))::uuid[]))
28. 0.100 0.100 ↓ 0.0 0 100

Index Scan using movies_pkey on movies m (cost=0.14..8.16 rows=1 width=11) (actual time=0.001..0.001 rows=0 loops=100)

  • Index Cond: (id = COALESCE(movie_id, ib.movie_id))
29. 0.100 0.100 ↓ 0.0 0 100

Index Scan using theatres_pkey on theatres t (cost=0.29..8.31 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=100)

  • Index Cond: (id = COALESCE(theatre_id, ib.theatre_id))
30. 3.285 678.000 ↑ 1.0 1 100

Aggregate (cost=7,326.57..7,326.58 rows=1 width=32) (actual time=6.780..6.780 rows=1 loops=100)

31. 0.149 671.400 ↑ 26.0 1 100

Hash Left Join (cost=40.11..1,378.51 rows=26 width=648) (actual time=2.735..6.714 rows=1 loops=100)

  • Hash Cond: (p.updated_by = puu.id)
32. 0.228 671.200 ↑ 26.0 1 100

Hash Left Join (cost=20.06..1,358.38 rows=26 width=574) (actual time=2.733..6.712 rows=1 loops=100)

  • Hash Cond: (p.created_by = pcu.id)
33. 0.100 670.900 ↑ 26.0 1 100

Nested Loop Left Join (cost=0.00..1,338.24 rows=26 width=500) (actual time=2.731..6.709 rows=1 loops=100)

  • Join Filter: false
34. 670.800 670.800 ↑ 26.0 1 100

Seq Scan on products p (cost=0.00..1,337.98 rows=26 width=322) (actual time=2.729..6.708 rows=1 loops=100)

  • Filter: ((NOT is_deleted) AND (status = 'Approved'::text) AND (ib.identifiers @> ARRAY[(individual_brand_id)::text]))
  • Rows Removed by Filter: 6412
35. 0.000 0.000 ↓ 0.0 0 65

Result (cost=0.00..0.00 rows=0 width=178) (actual time=0.000..0.000 rows=0 loops=65)

  • One-Time Filter: false
36. 0.022 0.072 ↑ 1.0 47 1

Hash (cost=19.47..19.47 rows=47 width=90) (actual time=0.072..0.072 rows=47 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
37. 0.050 0.050 ↑ 1.0 47 1

Seq Scan on users pcu (cost=0.00..19.47 rows=47 width=90) (actual time=0.005..0.050 rows=47 loops=1)

38. 0.020 0.051 ↑ 1.0 47 1

Hash (cost=19.47..19.47 rows=47 width=90) (actual time=0.051..0.051 rows=47 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
39. 0.031 0.031 ↑ 1.0 47 1

Seq Scan on users puu (cost=0.00..19.47 rows=47 width=90) (actual time=0.002..0.031 rows=47 loops=1)

40.          

SubPlan (for Aggregate)

41. 0.780 1.300 ↑ 1.0 1 65

Aggregate (cost=61.29..61.30 rows=1 width=32) (actual time=0.020..0.020 rows=1 loops=65)

42. 0.390 0.520 ↑ 10.0 1 65

Bitmap Heap Scan on advertisers adv_1 (cost=30.86..61.24 rows=10 width=23) (actual time=0.008..0.008 rows=1 loops=65)

  • Recheck Cond: (id = ANY ((COALESCE(advertisers, p.advertisers))::uuid[]))
  • Heap Blocks: exact=62
43. 0.130 0.130 ↑ 10.0 1 65

Bitmap Index Scan on advertisers_pkey (cost=0.00..30.85 rows=10 width=0) (actual time=0.002..0.002 rows=1 loops=65)

  • Index Cond: (id = ANY ((COALESCE(advertisers, p.advertisers))::uuid[]))
44. 0.975 1.690 ↑ 1.0 1 65

Aggregate (cost=159.14..159.15 rows=1 width=32) (actual time=0.026..0.026 rows=1 loops=65)

45. 0.065 0.715 ↑ 10.0 1 65

Nested Loop Left Join (cost=39.47..159.04 rows=10 width=219) (actual time=0.011..0.011 rows=1 loops=65)

46. 0.195 0.650 ↑ 10.0 1 65

Nested Loop Left Join (cost=39.19..152.78 rows=10 width=162) (actual time=0.009..0.010 rows=1 loops=65)

47. 0.325 0.455 ↑ 10.0 1 65

Bitmap Heap Scan on categories c1_1 (cost=38.91..73.78 rows=10 width=89) (actual time=0.007..0.007 rows=1 loops=65)

  • Recheck Cond: (id = ANY ((COALESCE(categories, p.categories))::uuid[]))
  • Heap Blocks: exact=61
48. 0.130 0.130 ↑ 10.0 1 65

Bitmap Index Scan on categories_pkey (cost=0.00..38.91 rows=10 width=0) (actual time=0.002..0.002 rows=1 loops=65)

  • Index Cond: (id = ANY ((COALESCE(categories, p.categories))::uuid[]))
49. 0.000 0.000 ↓ 0.0 0 61

Index Scan using categories_pkey on categories c2_1 (cost=0.28..7.90 rows=1 width=89) (actual time=0.000..0.000 rows=0 loops=61)

  • Index Cond: (c1_1.parent_id = id)
50. 0.000 0.000 ↓ 0.0 0 61

Index Scan using categories_pkey on categories c3_1 (cost=0.28..0.63 rows=1 width=73) (actual time=0.000..0.000 rows=0 loops=61)

  • Index Cond: (c2_1.parent_id = id)
51. 0.325 0.325 ↑ 1.0 1 65

Index Scan using product_id_types_pkey on product_identification_number_types pt (cost=0.28..8.29 rows=1 width=10) (actual time=0.004..0.005 rows=1 loops=65)

  • Index Cond: (id = COALESCE(product_identification_number_type_id, p.product_identification_number_type_id))
Planning time : 2.802 ms
Execution time : 125,911.146 ms