explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e6eL

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 638.184 ↑ 1.0 100 1

Limit (cost=90,269.71..846,623.18 rows=100 width=518) (actual time=169.229..638.184 rows=100 loops=1)

2. 0.644 638.140 ↑ 4,571.9 100 1

Result (cost=90,269.71..3,458,032,428.32 rows=457,186 width=518) (actual time=169.228..638.140 rows=100 loops=1)

3. 5.963 163.496 ↑ 4,571.9 100 1

Sort (cost=90,269.71..91,412.67 rows=457,186 width=663) (actual time=163.434..163.496 rows=100 loops=1)

  • Sort Key: (COALESCE(name, ib.name))
  • Sort Method: top-N heapsort Memory: 197kB
4. 71.679 157.533 ↑ 55.6 8,225 1

WindowAgg (cost=40.72..72,796.39 rows=457,186 width=663) (actual time=95.882..157.533 rows=8,225 loops=1)

5. 4.075 85.854 ↑ 55.6 8,225 1

Nested Loop Left Join (cost=40.72..55,651.91 rows=457,186 width=690) (actual time=0.195..85.854 rows=8,225 loops=1)

  • Join Filter: false
6. 7.433 81.779 ↑ 55.6 8,225 1

Nested Loop (cost=40.72..51,080.05 rows=457,186 width=683) (actual time=0.194..81.779 rows=8,225 loops=1)

7. 3.766 16.771 ↓ 1.0 8,225 1

Hash Left Join (cost=40.11..1,807.92 rows=7,983 width=670) (actual time=0.158..16.771 rows=8,225 loops=1)

  • Hash Cond: (COALESCE(updated_by, ib.updated_by) = uu.id)
8. 3.380 12.959 ↓ 1.0 8,225 1

Hash Left Join (cost=20.06..1,766.46 rows=7,983 width=612) (actual time=0.100..12.959 rows=8,225 loops=1)

  • Hash Cond: (ib.created_by = cu.id)
9. 3.299 9.503 ↓ 1.0 8,225 1

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

  • Join Filter: false
10. 6.204 6.204 ↓ 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.003..6.204 rows=8,225 loops=1)

  • Filter: ((NOT is_deleted) AND (status = 'Approved'::text))
  • Rows Removed by Filter: 1073
11. 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
12. 0.015 0.076 ↑ 1.0 47 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
13. 0.061 0.061 ↑ 1.0 47 1

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

14. 0.016 0.046 ↑ 1.0 47 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
15. 0.030 0.030 ↑ 1.0 47 1

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

16. 8.225 57.575 ↑ 57.0 1 8,225

Bitmap Heap Scan on parent_brands pb (cost=0.60..5.60 rows=57 width=91) (actual time=0.007..0.007 rows=1 loops=8,225)

  • Recheck Cond: ((identifiers @> ARRAY[(ib.parent_brand_id)::text]) AND (NOT is_deleted))
  • Heap Blocks: exact=8560
17. 49.350 49.350 ↑ 57.0 1 8,225

Bitmap Index Scan on idx_parent_brand_identifiers (cost=0.00..0.59 rows=57 width=0) (actual time=0.006..0.006 rows=1 loops=8,225)

  • Index Cond: (identifiers @> ARRAY[(ib.parent_brand_id)::text])
18. 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
19.          

SubPlan (for Result)

20. 1.100 2.500 ↑ 1.0 1 100

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

21. 0.200 1.400 ↑ 10.0 1 100

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

22. 0.200 1.200 ↑ 10.0 1 100

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

23. 0.700 1.000 ↑ 10.0 1 100

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

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

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

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

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=98)

  • Index Cond: (c1.parent_id = id)
26. 0.000 0.000 ↓ 0.0 0 98

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=98)

  • Index Cond: (c2.parent_id = id)
27. 0.800 1.400 ↑ 1.0 1 100

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

28. 0.400 0.600 ↑ 10.0 1 100

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

  • Recheck Cond: (id = ANY ((COALESCE(advertisers, ib.advertisers))::uuid[]))
  • Heap Blocks: exact=99
29. 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[]))
30. 0.000 0.000 ↓ 0.0 0 100

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

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

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

  • Index Cond: (id = COALESCE(theatre_id, ib.theatre_id))
32. 3.150 470.100 ↑ 1.0 1 100

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

33. 0.066 462.800 ↑ 26.0 1 100

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

  • Hash Cond: (p.updated_by = puu.id)
34. 0.248 462.700 ↑ 26.0 1 100

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

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

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

  • Join Filter: false
36. 462.300 462.300 ↑ 26.0 1 100

Seq Scan on products p (cost=0.00..1,337.98 rows=26 width=322) (actual time=2.863..4.623 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
37. 0.000 0.000 ↓ 0.0 0 83

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

  • One-Time Filter: false
38. 0.018 0.052 ↑ 1.0 47 1

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

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

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

40. 0.014 0.034 ↑ 1.0 47 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
41. 0.020 0.020 ↑ 1.0 47 1

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

42.          

SubPlan (for Aggregate)

43. 0.996 1.909 ↑ 1.0 1 83

Aggregate (cost=61.29..61.30 rows=1 width=32) (actual time=0.023..0.023 rows=1 loops=83)

44. 0.664 0.913 ↑ 10.0 1 83

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

  • Recheck Cond: (id = ANY ((COALESCE(advertisers, p.advertisers))::uuid[]))
  • Heap Blocks: exact=100
45. 0.249 0.249 ↑ 10.0 1 83

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

  • Index Cond: (id = ANY ((COALESCE(advertisers, p.advertisers))::uuid[]))
46. 0.996 1.992 ↑ 1.0 1 83

Aggregate (cost=159.14..159.15 rows=1 width=32) (actual time=0.023..0.024 rows=1 loops=83)

47. 0.166 0.996 ↑ 10.0 1 83

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

48. 0.154 0.830 ↑ 10.0 1 83

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

49. 0.415 0.581 ↑ 10.0 1 83

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=83)

  • Recheck Cond: (id = ANY ((COALESCE(categories, p.categories))::uuid[]))
  • Heap Blocks: exact=82
50. 0.166 0.166 ↑ 10.0 1 83

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

  • Index Cond: (id = ANY ((COALESCE(categories, p.categories))::uuid[]))
51. 0.095 0.095 ↓ 0.0 0 95

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

  • Index Cond: (c1_1.parent_id = id)
52. 0.000 0.000 ↓ 0.0 0 95

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=95)

  • Index Cond: (c2_1.parent_id = id)
53. 0.249 0.249 ↑ 1.0 1 83

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.003..0.003 rows=1 loops=83)

  • Index Cond: (id = COALESCE(product_identification_number_type_id, p.product_identification_number_type_id))
Planning time : 4.240 ms
Execution time : 638.943 ms