explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2a8j

Settings
# exclusive inclusive rows x rows loops node
1. 66.023 228.491 ↑ 1.0 6,256 1

GroupAggregate (cost=114,851.07..116,540.42 rows=6,258 width=647) (actual time=158.784..228.491 rows=6,256 loops=1)

  • Group Key: pb.id
2. 23.588 162.468 ↑ 10.1 12,667 1

Sort (cost=114,851.07..115,170.16 rows=127,639 width=593) (actual time=158.747..162.468 rows=12,667 loops=1)

  • Sort Key: pb.id
  • Sort Method: external merge Disk: 7608kB
3. 4.467 138.880 ↑ 10.1 12,667 1

Merge Right Join (cost=33,361.57..35,965.19 rows=127,639 width=593) (actual time=120.723..138.880 rows=12,667 loops=1)

  • Merge Cond: (((childcategory.id)::text) = (unnest(parent_brands.categories)))
4. 4.863 16.742 ↑ 1.0 2,214 1

Sort (cost=601.21..606.77 rows=2,223 width=48) (actual time=16.472..16.742 rows=2,214 loops=1)

  • Sort Key: ((childcategory.id)::text)
  • Sort Method: quicksort Memory: 1001kB
5. 9.264 11.879 ↓ 1.0 2,226 1

Hash Left Join (cost=318.03..477.63 rows=2,223 width=48) (actual time=1.883..11.879 rows=2,226 loops=1)

  • Hash Cond: (parentcategory.parent_id = grandparentcategory.id)
6. 0.547 1.827 ↓ 1.0 2,226 1

Hash Left Join (cost=159.02..296.09 rows=2,223 width=160) (actual time=1.060..1.827 rows=2,226 loops=1)

  • Hash Cond: (childcategory.parent_id = parentcategory.id)
7. 0.252 0.252 ↓ 1.0 2,226 1

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

8. 0.542 1.028 ↓ 1.0 2,226 1

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

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

10. 0.455 0.788 ↓ 1.0 2,226 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 259kB
11. 0.333 0.333 ↓ 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.333 rows=2,226 loops=1)

12. 9.017 117.671 ↓ 1.1 12,667 1

Materialize (cost=32,760.36..32,817.78 rows=11,483 width=593) (actual time=104.243..117.671 rows=12,667 loops=1)

13. 29.216 108.654 ↓ 1.1 12,667 1

Sort (cost=32,760.36..32,789.07 rows=11,483 width=593) (actual time=104.240..108.654 rows=12,667 loops=1)

  • Sort Key: (unnest(parent_brands.categories))
  • Sort Method: external merge Disk: 7168kB
14. 3.980 79.438 ↓ 1.1 12,667 1

Merge Right Join (cost=28,690.16..28,923.49 rows=11,483 width=593) (actual time=70.642..79.438 rows=12,667 loops=1)

  • Merge Cond: (((adv.id)::text) = (unnest(parent_brands_1.advertisers)))
15. 0.560 0.708 ↓ 1.0 370 1

Sort (cost=54.30..55.22 rows=367 width=26) (actual time=0.666..0.708 rows=370 loops=1)

  • Sort Key: ((adv.id)::text)
  • Sort Method: quicksort Memory: 77kB
16. 0.148 0.148 ↓ 1.0 370 1

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

17. 35.600 74.750 ↓ 2.0 12,667 1

Sort (cost=28,635.86..28,651.50 rows=6,258 width=599) (actual time=69.968..74.750 rows=12,667 loops=1)

  • Sort Key: (unnest(parent_brands_1.advertisers))
  • Sort Method: external sort Disk: 7576kB
18. 7.745 39.150 ↓ 2.0 12,667 1

Hash Right Join (cost=14,193.41..28,241.25 rows=6,258 width=599) (actual time=26.750..39.150 rows=12,667 loops=1)

  • Hash Cond: (parent_brands.id = pb.id)
19. 3.652 4.723 ↑ 98.5 7,101 1

ProjectSet (cost=0.00..4,369.88 rows=699,300 width=48) (actual time=0.007..4.723 rows=7,101 loops=1)

20. 1.071 1.071 ↑ 1.0 6,993 1

Seq Scan on parent_brands (cost=0.00..820.93 rows=6,993 width=77) (actual time=0.005..1.071 rows=6,993 loops=1)

21. 9.984 26.682 ↓ 2.0 12,462 1

Hash (cost=14,115.19..14,115.19 rows=6,258 width=567) (actual time=26.682..26.682 rows=12,462 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 2 (originally 1) Memory Usage: 4033kB
22. 4.218 16.698 ↓ 2.0 12,462 1

Hash Right Join (cost=916.64..14,115.19 rows=6,258 width=567) (actual time=7.246..16.698 rows=12,462 loops=1)

  • Hash Cond: (parent_brands_1.id = pb.id)
23. 4.252 5.296 ↑ 50.8 13,757 1

ProjectSet (cost=0.00..4,369.88 rows=699,300 width=48) (actual time=0.006..5.296 rows=13,757 loops=1)

24. 1.044 1.044 ↑ 1.0 6,993 1

Seq Scan on parent_brands parent_brands_1 (cost=0.00..820.93 rows=6,993 width=115) (actual time=0.001..1.044 rows=6,993 loops=1)

25. 3.724 7.184 ↑ 1.0 6,256 1

Hash (cost=838.41..838.41 rows=6,258 width=535) (actual time=7.184..7.184 rows=6,256 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 3431kB
26. 3.460 3.460 ↑ 1.0 6,256 1

Seq Scan on parent_brands pb (cost=0.00..838.41 rows=6,258 width=535) (actual time=0.003..3.460 rows=6,256 loops=1)

  • Filter: (status = 'Approved'::text)
  • Rows Removed by Filter: 737
Planning time : 1.791 ms
Execution time : 235.754 ms