explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rffS

Settings
# exclusive inclusive rows x rows loops node
1. 0.890 2,773.179 ↓ 102.0 102 1

HashAggregate (cost=1,028,170.73..1,028,204.49 rows=1 width=890) (actual time=2,773.085..2,773.179 rows=102 loops=1)

  • Group Key: article2_.article_id, article2_.article_title, article2_.date_created, article2_.date_referred, article2_.pages, article2_.article_source_type, media3_.media_id, media3_.media_name, mediatype4_.mediatype_id, mediatype4_.mediatype_name, creativ (...)
2. 3.853 2,772.289 ↓ 102.0 102 1

Nested Loop Left Join (cost=840,894.58..1,028,170.62 rows=1 width=890) (actual time=2,450.787..2,772.289 rows=102 loops=1)

3. 0.514 2,768.436 ↓ 102.0 102 1

Nested Loop Left Join (cost=840,890.40..1,028,131.36 rows=1 width=847) (actual time=2,450.689..2,768.436 rows=102 loops=1)

4. 0.875 2,767.922 ↓ 102.0 102 1

Nested Loop (cost=840,890.12..1,028,127.21 rows=1 width=822) (actual time=2,450.678..2,767.922 rows=102 loops=1)

5. 1.237 2,767.047 ↓ 102.0 102 1

Nested Loop Left Join (cost=840,889.71..1,028,122.59 rows=1 width=798) (actual time=2,450.653..2,767.047 rows=102 loops=1)

  • Join Filter: (creative9_.creative_type_id = creativety10_.id)
  • Rows Removed by Join Filter: 1428
6. 0.849 2,765.810 ↓ 102.0 102 1

Hash Join (cost=840,889.71..1,028,121.25 rows=1 width=775) (actual time=2,450.631..2,765.810 rows=102 loops=1)

  • Hash Cond: (streaminse0_.creative_id = streamcrea8_.id)
7. 0.990 2,757.594 ↑ 8.0 6,168 1

Nested Loop (cost=840,440.96..1,027,488.25 rows=49,126 width=382) (actual time=2,443.021..2,757.594 rows=6,168 loops=1)

8. 0.011 0.029 ↑ 1.0 1 1

Bitmap Heap Scan on subject subject7_ (cost=4.28..8.30 rows=1 width=4) (actual time=0.025..0.029 rows=1 loops=1)

  • Recheck Cond: (subject_id = 6)
  • Heap Blocks: exact=1
9. 0.018 0.018 ↑ 1.0 1 1

Bitmap Index Scan on subject_pkey (cost=0.00..4.28 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=1)

  • Index Cond: (subject_id = 6)
10. 2.330 2,756.575 ↑ 8.0 6,168 1

Hash Join (cost=840,436.68..1,026,988.70 rows=49,126 width=386) (actual time=2,442.973..2,756.575 rows=6,168 loops=1)

  • Hash Cond: (article2_.media_id = media3_.media_id)
11. 613.961 2,752.841 ↑ 8.0 6,168 1

Hash Join (cost=840,342.25..1,026,157.38 rows=49,126 width=358) (actual time=2,441.544..2,752.841 rows=6,168 loops=1)

  • Hash Cond: (subjects6_.article_id = insertion1_.article_id)
12. 392.801 612.884 ↑ 1.0 2,770,151 1

Bitmap Heap Scan on articles_subjects subjects6_ (cost=54,051.17..204,080.07 rows=2,859,192 width=8) (actual time=224.949..612.884 rows=2,770,151 loops=1)

  • Recheck Cond: (subject_id = 6)
  • Heap Blocks: exact=21023
13. 220.083 220.083 ↑ 1.0 2,770,154 1

Bitmap Index Scan on subject_articles_subject (cost=0.00..53,336.38 rows=2,859,192 width=0) (actual time=220.083..220.083 rows=2,770,154 loops=1)

  • Index Cond: (subject_id = 6)
14. 8.705 1,525.996 ↑ 7.7 6,168 1

Hash (cost=783,460.22..783,460.22 rows=47,669 width=358) (actual time=1,525.996..1,525.996 rows=6,168 loops=1)

  • Buckets: 8192 Batches: 2 Memory Usage: 1154kB
15. 1.518 1,517.291 ↑ 7.7 6,168 1

Nested Loop (cost=13,183.83..783,460.22 rows=47,669 width=358) (actual time=412.145..1,517.291 rows=6,168 loops=1)

16. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on article_category articlecat5_ (cost=0.00..1.16 rows=1 width=4) (actual time=0.012..0.016 rows=1 loops=1)

  • Filter: (article_category_id = 14)
  • Rows Removed by Filter: 12
17. 2.415 1,515.757 ↑ 7.7 6,168 1

Nested Loop (cost=13,183.83..782,982.36 rows=47,669 width=362) (actual time=412.119..1,515.757 rows=6,168 loops=1)

18. 401.209 1,494.838 ↑ 21.1 6,168 1

Hash Join (cost=13,183.40..148,032.32 rows=129,984 width=237) (actual time=412.090..1,494.838 rows=6,168 loops=1)

  • Hash Cond: (insertion1_.id = streaminse0_.insertion_id)
19. 1,026.170 1,026.170 ↑ 1.0 2,666,714 1

Seq Scan on insertion insertion1_ (cost=0.00..100,179.81 rows=2,669,542 width=181) (actual time=0.038..1,026.170 rows=2,666,714 loops=1)

  • Filter: (verified AND (cost IS NOT NULL))
  • Rows Removed by Filter: 106436
20. 67.459 67.459 ↑ 21.3 6,339 1

Hash (cost=11,494.78..11,494.78 rows=135,089 width=64) (actual time=67.459..67.459 rows=6,339 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 644kB
  • -> Index Scan using stream_insertion_start_time_index on stream_insertion streaminse0_ (cost=0.43..11494.78 rows=135089 width=64) (actual time=0.030..64.683 rows=6339 loops=1 (...)
  • Index Cond: (start_time > '2019-09-10 00:00:00'::timestamp without time zone)
  • Filter: (end_time < '2019-09-11 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 137777
21. 18.504 18.504 ↑ 1.0 1 6,168

Index Scan using article_pkey on article article2_ (cost=0.43..4.87 rows=1 width=125) (actual time=0.002..0.003 rows=1 loops=6,168)

  • Index Cond: (article_id = insertion1_.article_id)
  • Filter: (article_category_id = 14)
22. 0.540 1.404 ↓ 1.0 1,796 1

Hash (cost=72.01..72.01 rows=1,793 width=32) (actual time=1.404..1.404 rows=1,796 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 111kB
23. 0.569 0.864 ↓ 1.0 1,796 1

Hash Join (cost=1.43..72.01 rows=1,793 width=32) (actual time=0.032..0.864 rows=1,796 loops=1)

  • Hash Cond: (media3_.mediatype_id = mediatype4_.mediatype_id)
24. 0.282 0.282 ↓ 1.0 1,796 1

Seq Scan on media media3_ (cost=0.00..45.93 rows=1,793 width=24) (actual time=0.010..0.282 rows=1,796 loops=1)

25. 0.004 0.013 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=12) (actual time=0.013..0.013 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
26. 0.009 0.009 ↑ 1.0 19 1

Seq Scan on mediatype mediatype4_ (cost=0.00..1.19 rows=19 width=12) (actual time=0.006..0.009 rows=19 loops=1)

27. 0.130 7.367 ↓ 146.0 146 1

Hash (cost=448.73..448.73 rows=1 width=401) (actual time=7.367..7.367 rows=146 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 57kB
28. 2.097 7.237 ↓ 146.0 146 1

Hash Join (cost=29.81..448.73 rows=1 width=401) (actual time=3.139..7.237 rows=146 loops=1)

  • Hash Cond: (streamcrea8_.creative_id = creative9_.id)
29. 2.239 2.239 ↓ 1.0 16,457 1

Seq Scan on stream_creative streamcrea8_ (cost=0.00..357.30 rows=16,430 width=20) (actual time=0.016..2.239 rows=16,457 loops=1)

30. 0.473 2.901 ↓ 483.0 483 1

Hash (cost=29.80..29.80 rows=1 width=389) (actual time=2.901..2.901 rows=483 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 189kB
31. 0.181 2.428 ↓ 483.0 483 1

Nested Loop (cost=9.73..29.80 rows=1 width=389) (actual time=0.525..2.428 rows=483 loops=1)

32. 0.026 0.026 ↑ 1.0 1 1

Index Scan using company_name_index on company company13_ (cost=0.29..8.31 rows=1 width=27) (actual time=0.025..0.026 rows=1 loops=1)

  • Index Cond: ((name)::text = 'OPAP CYPRUS'::text)
33. 0.141 2.221 ↓ 483.0 483 1

Nested Loop (cost=9.44..21.48 rows=1 width=370) (actual time=0.495..2.221 rows=483 loops=1)

34. 0.021 0.021 ↑ 1.0 1 1

Index Scan using brand_name_index on brand brand12_ (cost=0.41..8.43 rows=1 width=26) (actual time=0.018..0.021 rows=1 loops=1)

  • Index Cond: ((name)::text = 'OPAP CYPRUS'::text)
35. 1.670 2.059 ↓ 483.0 483 1

Bitmap Heap Scan on creative creative9_ (cost=9.02..13.04 rows=1 width=352) (actual time=0.468..2.059 rows=483 loops=1)

  • Recheck Cond: ((brand_id = brand12_.id) AND (company_id = company13_.id))
  • Filter: verified
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=440
36. 0.389 0.389 ↓ 0.0 0 1

BitmapAnd (cost=9.02..9.02 rows=1 width=0) (actual time=0.389..0.389 rows=0 loops=1)