explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4rU

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 379,352.026 ↓ 102.0 102 1

Limit (cost=1,029,382.70..1,029,416.46 rows=1 width=890) (actual time=379,351.932..379,352.026 rows=102 loops=1)

2. 0.897 379,352.017 ↓ 102.0 102 1

HashAggregate (cost=1,029,382.70..1,029,416.46 rows=1 width=890) (actual time=379,351.930..379,352.017 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, c (...)
3. 0.807 379,351.120 ↓ 102.0 102 1

Nested Loop Left Join (cost=839,998.35..1,029,382.59 rows=1 width=890) (actual time=361,430.697..379,351.120 rows=102 loops=1)

  • Join Filter: (market15_.sector_id = sector16_.id)
  • Rows Removed by Join Filter: 2244
4. 2.131 379,348.783 ↓ 102.0 102 1

Nested Loop Left Join (cost=839,998.35..1,029,347.33 rows=1 width=873) (actual time=361,430.649..379,348.783 rows=102 loops=1)

  • Join Filter: (submarket14_.market_id = market15_.id)
  • Rows Removed by Join Filter: 17136
5. 14.211 379,345.020 ↓ 102.0 102 1

Nested Loop Left Join (cost=839,998.35..1,029,341.52 rows=1 width=847) (actual time=361,430.623..379,345.020 rows=102 loops=1)

  • Join Filter: (creative9_.sub_market_id = submarket14_.id)
  • Rows Removed by Join Filter: 139740
6. 0.201 379,319.283 ↓ 102.0 102 1

Nested Loop (cost=839,998.35..1,029,297.68 rows=1 width=822) (actual time=361,430.353..379,319.283 rows=102 loops=1)

7. 0.723 379,318.368 ↓ 102.0 102 1

Nested Loop Left Join (cost=839,997.93..1,029,293.06 rows=1 width=798) (actual time=361,430.281..379,318.368 rows=102 loops=1)

  • Join Filter: (creative9_.creative_type_id = creativety10_.id)
  • Rows Removed by Join Filter: 1428
8. 95.105 379,317.237 ↓ 102.0 102 1

Nested Loop (cost=839,997.93..1,029,291.72 rows=1 width=775) (actual time=361,430.260..379,317.237 rows=102 loops=1)

  • Join Filter: (streamcrea8_.id = streaminse0_.creative_id)
  • Rows Removed by Join Filter: 900426
9. 4.302 83.492 ↓ 146.0 146 1

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

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

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

11. 0.525 76.085 ↓ 483.0 483 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 189kB
12. 0.215 75.560 ↓ 483.0 483 1

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

13. 0.131 0.131 ↑ 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.130..0.131 rows=1 loops=1)

  • Index Cond: ((name)::text = 'OPAP CYPRUS'::text)
14. 0.145 75.214 ↓ 483.0 483 1

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

15. 15.023 15.023 ↑ 1.0 1 1

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

  • Index Cond: ((name)::text = 'OPAP CYPRUS'::text)
16. 23.645 60.046 ↓ 483.0 483 1

Bitmap Heap Scan on creative creative9_ (cost=9.02..13.04 rows=1 width=352) (actual time=51.428..60.046 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
17. 0.054 36.401 ↓ 0.0 0 1

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

18. 23.197 23.197 ↓ 44.0 484 1

Bitmap Index Scan on brand_id_creative (cost=0.00..4.37 rows=11 width=0) (actual time=23.197..23.197 rows=484 loops=1)

  • Index Cond: (brand_id = brand12_.id)
19. 13.150 13.150 ↓ 35.3 494 1

Bitmap Index Scan on company_id_creative (cost=0.00..4.40 rows=14 width=0) (actual time=13.150..13.150 rows=494 loops=1)

  • Index Cond: (company_id = company13_.id)
20. 158.556 379,138.640 ↑ 8.0 6,168 146

Nested Loop (cost=839,968.12..1,028,228.91 rows=49,126 width=382) (actual time=2,263.549..2,596.840 rows=6,168 loops=146)

21. 1.022 3.504 ↑ 1.0 1 146

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

  • Recheck Cond: (subject_id = 6)
  • Heap Blocks: exact=146
22. 2.482 2.482 ↑ 1.0 1 146

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

  • Index Cond: (subject_id = 6)
23. 192.428 378,976.580 ↑ 8.0 6,168 146

Nested Loop (cost=839,963.84..1,027,729.36 rows=49,126 width=386) (actual time=2,263.522..2,595.730 rows=6,168 loops=146)

24. 2.044 2.044 ↑ 1.0 1 146

Seq Scan on article_category articlecat5_ (cost=0.00..1.16 rows=1 width=4) (actual time=0.005..0.014 rows=1 loops=146)

  • Filter: (article_category_id = 14)
  • Rows Removed by Filter: 12
25. 300.017 378,782.108 ↑ 8.0 6,168 146

Hash Join (cost=839,963.84..1,027,236.93 rows=49,126 width=390) (actual time=2,263.208..2,594.398 rows=6,168 loops=146)

  • Hash Cond: (media3_.mediatype_id = mediatype4_.mediatype_id)
26. 318.357 378,482.078 ↑ 8.0 6,168 146

Hash Join (cost=839,962.41..1,026,560.02 rows=49,126 width=382) (actual time=2,263.205..2,592.343 rows=6,168 loops=146)

  • Hash Cond: (article2_.media_id = media3_.media_id)
27. 90,164.198 378,163.068 ↑ 8.0 6,168 146

Hash Join (cost=839,894.07..1,025,754.79 rows=49,126 width=362) (actual time=2,263.197..2,590.158 rows=6,168 loops=146)

  • Hash Cond: (subjects6_.article_id = insertion1_.article_id)
28. 53,468.850 85,667.544 ↑ 1.0 2,770,151 146

Bitmap Heap Scan on articles_subjects subjects6_ (cost=54,050.98..204,078.57 rows=2,859,167 width=8) (actual time=225.502..586.764 rows=2,770,151 loops=146)

  • Recheck Cond: (subject_id = 6)
  • Heap Blocks: exact=3069358
29. 32,198.694 32,198.694 ↑ 1.0 2,770,154 146

Bitmap Index Scan on subject_articles_subject (cost=0.00..53,336.19 rows=2,859,167 width=0) (actual time=220.539..220.539 rows=2,770,154 loops=146)

  • Index Cond: (subject_id = 6)
30. 1,090.182 202,331.326 ↑ 7.7 6,168 146

Hash (cost=782,965.23..782,965.23 rows=47,669 width=362) (actual time=1,385.831..1,385.831 rows=6,168 loops=146)

  • Buckets: 8192 Batches: 2 Memory Usage: 1166kB
31. 334.194 201,241.144 ↑ 7.7 6,168 146

Nested Loop (cost=13,183.83..782,965.23 rows=47,669 width=362) (actual time=363.873..1,378.364 rows=6,168 loops=146)

32. 57,226.390 198,205.366 ↑ 21.1 6,168 146

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

  • Hash Cond: (insertion1_.id = streaminse0_.insertion_id)
33. 140,910.732 140,910.732 ↑ 1.0 2,666,714 146

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

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

Hash (cost=11,494.78..11,494.78 rows=135,089 width=64) (actual time=68.244..68.244 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.034..65.279 (...)
  • 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
35. 2,701.584 2,701.584 ↑ 1.0 1 900,528

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=900,528)

  • Index Cond: (article_id = insertion1_.article_id)
  • Filter: (article_category_id = 14)
36. 0.313 0.653 ↓ 1.0 1,796 1

Hash (cost=45.93..45.93 rows=1,793 width=24) (actual time=0.653..0.653 rows=1,796 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 101kB
37. 0.340 0.340 ↓ 1.0 1,796 1

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

38. 0.005 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
39. 0.008 0.008 ↑ 1.0 19 1

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

40. 0.408 0.408 ↑ 1.0 15 102

Seq Scan on creative_type creativety10_ (cost=0.00..1.15 rows=15 width=31) (actual time=0.003..0.004 rows=15 loops=102)

41. 0.714 0.714 ↑ 1.0 1 102

Index Scan using pk_product on product product11_ (cost=0.42..4.61 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=102)

  • Index Cond: (id = creative9_.product_id)
  • Filter: (((name)::text = 'OPAP JOKER'::text) OR ((name)::text = 'OPAP JOKER JACKPOT'::text))
42. 11.526 11.526 ↑ 1.0 1,371 102

Seq Scan on sub_market submarket14_ (cost=0.00..26.71 rows=1,371 width=33) (actual time=0.005..0.113 rows=1,371 loops=102)

43. 1.632 1.632 ↑ 1.0 169 102

Seq Scan on market market15_ (cost=0.00..3.69 rows=169 width=34) (actual time=0.005..0.016 rows=169 loops=102)

44. 0.408 0.408 ↑ 1.0 23 102

Seq Scan on sector sector16_ (cost=0.00..1.23 rows=23 width=25) (actual time=0.003..0.004 rows=23 loops=102)

45.          

SubPlan (forNested Loop Left Join)

46. 0.102 1.122 ↑ 1.0 1 102

Aggregate (cost=33.74..33.75 rows=1 width=24) (actual time=0.011..0.011 rows=1 loops=102)

47. 0.102 1.020 ↓ 0.0 0 102

Nested Loop (cost=1.00..33.73 rows=2 width=24) (actual time=0.010..0.010 rows=0 loops=102)

48. 0.102 0.918 ↓ 0.0 0 102

Nested Loop (cost=0.58..16.84 rows=2 width=8) (actual time=0.009..0.009 rows=0 loops=102)

49. 0.510 0.510 ↑ 1.0 1 102

Index Only Scan using pk_creative on creative creative18_ (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=102)

  • Index Cond: (id = creative9_.id)
  • Heap Fetches: 102
50. 0.306 0.306 ↓ 0.0 0 102

Index Scan using creative_id on sub_creative_product subcreativ19_ (cost=0.29..8.51 rows=2 width=16) (actual time=0.003..0.003 rows=0 loops=102)

  • Index Cond: (creative_id = creative9_.id)
51. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_product on product product20_ (cost=0.42..8.44 rows=1 width=32) (never executed)

  • Index Cond: (id = subcreativ19_.product_id)
Planning time : 15.416 ms
Execution time : 379,352.699 ms