explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OGwS

Settings
# exclusive inclusive rows x rows loops node
1. 118.036 69,333.082 ↑ 1.0 6,209 1

GroupAggregate (cost=322.82..324,669,392.07 rows=6,258 width=647) (actual time=16.824..69,333.082 rows=6,209 loops=1)

  • Group Key: pb.id
2. 17.776 69,215.046 ↑ 101,281.5 12,608 1

Nested Loop (cost=322.82..311,899,724.04 rows=1,276,957,416 width=593) (actual time=12.278..69,215.046 rows=12,608 loops=1)

3. 14.778 68,148.316 ↑ 1,101.3 6,319 1

Nested Loop (cost=320.57..1,228,508.49 rows=6,958,896 width=666) (actual time=12.079..68,148.316 rows=6,319 loops=1)

4. 18.210 18.210 ↑ 1.0 6,256 1

Index Scan using parent_brands_pkey on parent_brands pb (cost=0.28..3,314.56 rows=6,258 width=695) (actual time=0.024..18.210 rows=6,256 loops=1)

  • Filter: (status = 'Approved'::text)
  • Rows Removed by Filter: 737
5. 4,313.525 68,115.328 ↑ 1,112.0 1 6,256

Hash Right Join (cost=320.29..504.90 rows=1,112 width=32) (actual time=7.563..10.888 rows=1 loops=6,256)

  • Hash Cond: ((childcategory.id)::text = cat_id.cat_id)
6. 58,876.804 63,776.779 ↓ 1.0 2,226 6,241

Hash Left Join (cost=318.03..477.63 rows=2,223 width=48) (actual time=0.009..10.219 rows=2,226 loops=6,241)

  • Hash Cond: (parentcategory.parent_id = grandparentcategory.id)
7. 3,331.676 4,899.185 ↓ 1.0 2,226 6,241

Hash Left Join (cost=159.02..296.09 rows=2,223 width=160) (actual time=0.002..0.785 rows=2,226 loops=6,241)

  • Hash Cond: (childcategory.parent_id = parentcategory.id)
8. 1,566.491 1,566.491 ↓ 1.0 2,226 6,241

Seq Scan on categories childcategory (cost=0.00..131.23 rows=2,223 width=88) (actual time=0.001..0.251 rows=2,226 loops=6,241)

9. 0.529 1.018 ↓ 1.0 2,226 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 279kB
10. 0.489 0.489 ↓ 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.489 rows=2,226 loops=1)

11. 0.456 0.790 ↓ 1.0 2,226 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 259kB
12. 0.334 0.334 ↓ 1.0 2,226 1

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

13. 12.512 25.024 ↑ 100.0 1 6,256

Hash (cost=1.00..1.00 rows=100 width=32) (actual time=0.004..0.004 rows=1 loops=6,256)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 12.512 12.512 ↑ 100.0 1 6,256

Function Scan on unnest cat_id (cost=0.00..1.00 rows=100 width=32) (actual time=0.002..0.002 rows=1 loops=6,256)

15. 722.478 1,048.954 ↑ 92.0 2 6,319

Hash Right Join (cost=2.25..45.06 rows=184 width=26) (actual time=0.126..0.166 rows=2 loops=6,319)

  • Hash Cond: ((adv.id)::text = adv_id.adv_id)
16. 301.200 301.200 ↓ 1.0 370 6,275

Seq Scan on advertisers adv (cost=0.00..38.67 rows=367 width=26) (actual time=0.001..0.048 rows=370 loops=6,275)

17. 6.319 25.276 ↑ 50.0 2 6,319

Hash (cost=1.00..1.00 rows=100 width=32) (actual time=0.004..0.004 rows=2 loops=6,319)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 18.957 18.957 ↑ 50.0 2 6,319

Function Scan on unnest adv_id (cost=0.00..1.00 rows=100 width=32) (actual time=0.003..0.003 rows=2 loops=6,319)

Planning time : 1.643 ms
Execution time : 69,334.524 ms