explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XHrD

Settings
# exclusive inclusive rows x rows loops node
1. 4.801 85.848 ↑ 5.3 22,608 1

Hash Left Join (cost=4,972.14..12,249.89 rows=119,700 width=300) (actual time=43.139..85.848 rows=22,608 loops=1)

  • Hash Cond: (base.story_id = yahoo.story_id)
2.          

CTE base

3. 10.763 15.163 ↑ 5.3 22,608 1

Nested Loop (cost=5.03..2,458.79 rows=119,700 width=160) (actual time=0.953..15.163 rows=22,608 loops=1)

4. 0.405 1.888 ↑ 1.0 1,256 1

Hash Join (cost=4.88..51.90 rows=1,260 width=34) (actual time=0.101..1.888 rows=1,256 loops=1)

  • Hash Cond: (story.batch_id = batch.batch_id)
5. 1.412 1.412 ↓ 1.0 1,266 1

Seq Scan on story (cost=0.00..43.60 rows=1,260 width=32) (actual time=0.012..1.412 rows=1,266 loops=1)

6. 0.030 0.071 ↑ 1.0 128 1

Hash (cost=3.28..3.28 rows=128 width=6) (actual time=0.071..0.071 rows=128 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
7. 0.041 0.041 ↑ 1.0 128 1

Seq Scan on batch (cost=0.00..3.28 rows=128 width=6) (actual time=0.008..0.041 rows=128 loops=1)

8. 1.188 2.512 ↑ 5.3 18 1,256

Materialize (cost=0.15..13.13 rows=95 width=28) (actual time=0.001..0.002 rows=18 loops=1,256)

9. 1.324 1.324 ↑ 5.3 18 1

Index Scan using all_sites_status_idx on all_sites (cost=0.15..12.66 rows=95 width=28) (actual time=0.838..1.324 rows=18 loops=1)

  • Index Cond: (status = 'ACTIVE'::text)
10.          

CTE outbrain

11. 1.417 13.512 ↓ 6.2 1,212 1

GroupAggregate (cost=826.95..831.38 rows=197 width=40) (actual time=11.455..13.512 rows=1,212 loops=1)

  • Group Key: asset.story_id, asset.channel_id
12. 3.893 12.095 ↓ 67.9 13,380 1

Sort (cost=826.95..827.44 rows=197 width=8) (actual time=11.446..12.095 rows=13,380 loops=1)

  • Sort Key: asset.story_id, asset.channel_id
  • Sort Method: quicksort Memory: 1,012kB
13. 3.733 8.202 ↓ 67.9 13,380 1

Hash Join (cost=20.05..819.44 rows=197 width=8) (actual time=0.039..8.202 rows=13,380 loops=1)

  • Hash Cond: (asset.channel_id = channel.channel_id)
14. 4.454 4.454 ↑ 1.0 38,981 1

Seq Scan on asset (cost=0.00..695.54 rows=39,354 width=8) (actual time=0.007..4.454 rows=38,981 loops=1)

15. 0.011 0.015 ↑ 4.0 1 1

Hash (cost=20.00..20.00 rows=4 width=4) (actual time=0.015..0.015 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.004 0.004 ↑ 4.0 1 1

Seq Scan on channel (cost=0.00..20.00 rows=4 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Filter: (channel_name = 'Outbrain'::text)
  • Rows Removed by Filter: 2
17.          

CTE yahoo

18. 1.298 12.673 ↓ 6.2 1,217 1

GroupAggregate (cost=826.95..831.38 rows=197 width=40) (actual time=10.805..12.673 rows=1,217 loops=1)

  • Group Key: asset_1.story_id, asset_1.channel_id
19. 3.385 11.375 ↓ 61.3 12,073 1

Sort (cost=826.95..827.44 rows=197 width=8) (actual time=10.797..11.375 rows=12,073 loops=1)

  • Sort Key: asset_1.story_id, asset_1.channel_id
  • Sort Method: quicksort Memory: 950kB
20. 3.721 7.990 ↓ 61.3 12,073 1

Hash Join (cost=20.05..819.44 rows=197 width=8) (actual time=0.020..7.990 rows=12,073 loops=1)

  • Hash Cond: (asset_1.channel_id = channel_1.channel_id)
21. 4.265 4.265 ↑ 1.0 38,981 1

Seq Scan on asset asset_1 (cost=0.00..695.54 rows=39,354 width=8) (actual time=0.005..4.265 rows=38,981 loops=1)

22. 0.001 0.004 ↑ 4.0 1 1

Hash (cost=20.00..20.00 rows=4 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.003 0.003 ↑ 4.0 1 1

Seq Scan on channel channel_1 (cost=0.00..20.00 rows=4 width=4) (actual time=0.002..0.003 rows=1 loops=1)

  • Filter: (channel_name = 'Gemini'::text)
  • Rows Removed by Filter: 2
24.          

CTE taboola

25. 1.912 14.234 ↓ 6.2 1,222 1

GroupAggregate (cost=826.95..831.38 rows=197 width=40) (actual time=11.469..14.234 rows=1,222 loops=1)

  • Group Key: asset_2.story_id, asset_2.channel_id
26. 4.020 12.322 ↓ 68.7 13,528 1

Sort (cost=826.95..827.44 rows=197 width=8) (actual time=11.462..12.322 rows=13,528 loops=1)

  • Sort Key: asset_2.story_id, asset_2.channel_id
  • Sort Method: quicksort Memory: 1,019kB
27. 3.858 8.302 ↓ 68.7 13,528 1

Hash Join (cost=20.05..819.44 rows=197 width=8) (actual time=0.013..8.302 rows=13,528 loops=1)

  • Hash Cond: (asset_2.channel_id = channel_2.channel_id)
28. 4.440 4.440 ↑ 1.0 38,981 1

Seq Scan on asset asset_2 (cost=0.00..695.54 rows=39,354 width=8) (actual time=0.006..4.440 rows=38,981 loops=1)

29. 0.001 0.004 ↑ 4.0 1 1

Hash (cost=20.00..20.00 rows=4 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
30. 0.003 0.003 ↑ 4.0 1 1

Seq Scan on channel channel_2 (cost=0.00..20.00 rows=4 width=4) (actual time=0.003..0.003 rows=1 loops=1)

  • Filter: (channel_name = 'Taboola'::text)
  • Rows Removed by Filter: 2
31. 4.730 67.857 ↑ 5.3 22,608 1

Hash Left Join (cost=12.80..5,662.64 rows=119,700 width=272) (actual time=29.943..67.857 rows=22,608 loops=1)

  • Hash Cond: (base.story_id = taboola.story_id)
32. 5.963 48.186 ↑ 5.3 22,608 1

Hash Left Join (cost=6.40..4,028.32 rows=119,700 width=240) (actual time=14.994..48.186 rows=22,608 loops=1)

  • Hash Cond: (base.story_id = outbrain.story_id)
33. 28.203 28.203 ↑ 5.3 22,608 1

CTE Scan on base (cost=0.00..2,394.00 rows=119,700 width=208) (actual time=0.956..28.203 rows=22,608 loops=1)

34. 0.172 14.020 ↓ 6.2 1,212 1

Hash (cost=3.94..3.94 rows=197 width=36) (actual time=14.020..14.020 rows=1,212 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 64kB
35. 13.848 13.848 ↓ 6.2 1,212 1

CTE Scan on outbrain (cost=0.00..3.94 rows=197 width=36) (actual time=11.458..13.848 rows=1,212 loops=1)

36. 0.226 14.941 ↓ 6.2 1,222 1

Hash (cost=3.94..3.94 rows=197 width=36) (actual time=14.941..14.941 rows=1,222 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 64kB
37. 14.715 14.715 ↓ 6.2 1,222 1

CTE Scan on taboola (cost=0.00..3.94 rows=197 width=36) (actual time=11.472..14.715 rows=1,222 loops=1)

38. 0.173 13.190 ↓ 6.2 1,217 1

Hash (cost=3.94..3.94 rows=197 width=36) (actual time=13.190..13.190 rows=1,217 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 64kB
39. 13.017 13.017 ↓ 6.2 1,217 1

CTE Scan on yahoo (cost=0.00..3.94 rows=197 width=36) (actual time=10.809..13.017 rows=1,217 loops=1)

Planning time : 3.399 ms