explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4Haz

Settings
# exclusive inclusive rows x rows loops node
1. 0.878 364,073.712 ↓ 102.0 102 1

HashAggregate (cost=997,688.03..997,688.04 rows=1 width=898) (actual time=364,073.627..364,073.712 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. 0.122 364,072.834 ↓ 102.0 102 1

Nested Loop Left Join (cost=802,415.39..997,687.92 rows=1 width=898) (actual time=346,937.285..364,072.834 rows=102 loops=1)

3. 0.558 364,054.046 ↓ 102.0 102 1

Nested Loop Left Join (cost=802,414.96..997,681.72 rows=1 width=894) (actual time=346,919.141..364,054.046 rows=102 loops=1)

4. 0.198 364,051.448 ↓ 102.0 102 1

Nested Loop Left Join (cost=802,410.78..997,676.21 rows=1 width=851) (actual time=346,919.077..364,051.448 rows=102 loops=1)

5. 0.183 364,050.944 ↓ 102.0 102 1

Nested Loop (cost=802,410.50..997,672.06 rows=1 width=826) (actual time=346,919.060..364,050.944 rows=102 loops=1)

6. 0.712 364,049.945 ↓ 102.0 102 1

Nested Loop Left Join (cost=802,410.09..997,667.30 rows=1 width=802) (actual time=346,918.961..364,049.945 rows=102 loops=1)

  • Join Filter: (creative9_.creative_type_id = creativety10_.id)
  • Rows Removed by Join Filter: 1428
7. 97.885 364,048.825 ↓ 102.0 102 1

Nested Loop (cost=802,410.09..997,665.97 rows=1 width=779) (actual time=346,918.937..364,048.825 rows=102 loops=1)

  • Join Filter: (streamcrea8_.id = streaminse0_.creative_id)
  • Rows Removed by Join Filter: 901010
8. 822.872 1,350.450 ↓ 146.0 146 1

Nested Loop (cost=10.02..8,062.62 rows=1 width=405) (actual time=154.498..1,350.450 rows=146 loops=1)

  • Join Filter: (creative9_.id = streamcrea8_.creative_id)
  • Rows Removed by Join Filter: 7939408
9. 50.876 50.876 ↓ 1.0 16,438 1

Index Scan using origin_insertion_stream_creative on stream_creative streamcrea8_ (cost=0.29..7,786.37 rows=16,430 width=24) (actual time=0.021..50.876 rows=16,438 loops=1)

10. 387.743 476.702 ↓ 483.0 483 16,438

Materialize (cost=9.73..29.80 rows=1 width=389) (actual time=0.005..0.029 rows=483 loops=16,438)

11. 0.135 88.959 ↓ 483.0 483 1

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

12. 14.997 14.997 ↑ 1.0 1 1

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

  • Index Cond: ((name)::text = 'OPAP CYPRUS'::text)
13. 0.205 73.827 ↓ 483.0 483 1

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

14. 27.079 27.079 ↑ 1.0 1 1

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

  • Index Cond: ((name)::text = 'OPAP CYPRUS'::text)
15. 8.575 46.543 ↓ 483.0 483 1

Bitmap Heap Scan on creative creative9_ (cost=9.02..13.04 rows=1 width=352) (actual time=38.079..46.543 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
16. 0.057 37.968 ↓ 0.0 0 1

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

17. 23.677 23.677 ↓ 44.0 484 1

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

  • Index Cond: (brand_id = brand12_.id)
18. 14.234 14.234 ↓ 35.3 494 1

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

  • Index Cond: (company_id = company13_.id)
19. 207.320 362,600.490 ↑ 7.3 6,172 146

Nested Loop (cost=802,400.07..989,037.72 rows=45,250 width=382) (actual time=2,152.268..2,483.565 rows=6,172 loops=146)

20. 1.168 3.212 ↑ 1.0 1 146

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

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

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

  • Index Cond: (subject_id = 6)
22. 321.176 362,389.958 ↑ 7.3 6,172 146

Hash Join (cost=802,395.79..988,576.92 rows=45,250 width=386) (actual time=2,151.907..2,482.123 rows=6,172 loops=146)

  • Hash Cond: (article2_.media_id = media3_.media_id)
23. 88,092.458 362,066.714 ↑ 7.3 6,172 146

Hash Join (cost=802,301.36..987,803.75 rows=45,250 width=358) (actual time=2,151.888..2,479.909 rows=6,172 loops=146)

  • Hash Cond: (subjects6_.article_id = insertion1_.article_id)
24. 64,674.934 97,458.650 ↑ 1.0 2,765,611 146

Bitmap Heap Scan on articles_subjects subjects6_ (cost=53,983.73..203,935.18 rows=2,857,716 width=8) (actual time=229.424..667.525 rows=2,765,611 loops=146)

  • Recheck Cond: (subject_id = 6)
  • Heap Blocks: exact=3064314
25. 32,783.716 32,783.716 ↑ 1.0 2,765,701 146

Bitmap Index Scan on subject_articles_subject (cost=0.00..53,269.30 rows=2,857,716 width=0) (actual time=224.546..224.546 rows=2,765,701 loops=146)

  • Index Cond: (subject_id = 6)
26. 1,301.006 176,515.606 ↑ 7.1 6,172 146

Hash (cost=745,713.45..745,713.45 rows=43,854 width=358) (actual time=1,209.011..1,209.011 rows=6,172 loops=146)

  • Buckets: 8192 Batches: 2 Memory Usage: 1155kB
27. 228.490 175,214.600 ↑ 7.1 6,172 146

Nested Loop (cost=12,597.97..745,713.45 rows=43,854 width=358) (actual time=670.694..1,200.100 rows=6,172 loops=146)

28. 1.898 1.898 ↑ 1.0 1 146

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

  • Filter: (article_category_id = 14)
  • Rows Removed by Filter: 12
29. 449.972 174,984.212 ↑ 7.1 6,172 146

Nested Loop (cost=12,597.97..745,273.75 rows=43,854 width=362) (actual time=670.676..1,198.522 rows=6,172 loops=146)

30. 51,760.044 171,830.904 ↑ 19.4 6,172 146

Hash Join (cost=12,597.54..147,110.13 rows=119,580 width=237) (actual time=670.650..1,176.924 rows=6,172 loops=146)

  • Hash Cond: (insertion1_.id = streaminse0_.insertion_id)
31. 119,833.880 119,833.880 ↑ 1.0 2,662,204 146

Seq Scan on insertion insertion1_ (cost=0.00..100,005.55 rows=2,664,899 width=181) (actual time=0.006..820.780 rows=2,662,204 loops=146)

  • Filter: (verified AND (cost IS NOT NULL))
  • Rows Removed by Filter: 106542
32. 236.980 236.980 ↑ 19.6 6,344 1

Hash (cost=11,044.09..11,044.09 rows=124,276 width=64) (actual time=236.980..236.980 rows=6,344 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 645kB
  • -> Index Scan using stream_insertion_end_time_index on stream_insertion streaminse0_ (cost=0.43..11044.09 rows=124276 width=64) (actual time=26.943..230.008 rows=6344 l (...)
  • Index Cond: (end_time > '2019-09-10 00:00:00'::timestamp without time zone)
  • Filter: (start_time < '2019-09-11 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 133388
33. 2,703.336 2,703.336 ↑ 1.0 1 901,112

Index Scan using article_pkey on article article2_ (cost=0.43..4.99 rows=1 width=125) (actual time=0.002..0.003 rows=1 loops=901,112)

  • Index Cond: (article_id = insertion1_.article_id)
  • Filter: (article_category_id = 14)
34. 0.936 2.068 ↓ 1.0 1,794 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 111kB
35. 0.616 1.132 ↓ 1.0 1,794 1

Hash Join (cost=1.43..72.01 rows=1,793 width=32) (actual time=0.041..1.132 rows=1,794 loops=1)

  • Hash Cond: (media3_.mediatype_id = mediatype4_.mediatype_id)
36. 0.499 0.499 ↓ 1.0 1,794 1

Seq Scan on media media3_ (cost=0.00..45.93 rows=1,793 width=24) (actual time=0.011..0.499 rows=1,794 loops=1)

37. 0.005 0.017 ↑ 1.0 19 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
38. 0.012 0.012 ↑ 1.0 19 1

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

39. 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)

40. 0.816 0.816 ↑ 1.0 1 102

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

  • Index Cond: (id = creative9_.product_id)
  • Filter: (((name)::text = 'OPAP JOKER'::text) OR ((name)::text = 'OPAP JOKER JACKPOT'::text))
41. 0.306 0.306 ↑ 1.0 1 102

Index Scan using pk_sub_market on sub_market submarket14_ (cost=0.28..4.14 rows=1 width=33) (actual time=0.003..0.003 rows=1 loops=102)

  • Index Cond: (creative9_.sub_market_id = id)
42. 1.122 2.040 ↑ 1.0 1 102

Hash Right Join (cost=4.18..5.50 rows=1 width=51) (actual time=0.016..0.020 rows=1 loops=102)

  • Hash Cond: (sector16_.id = market15_.sector_id)
43. 0.306 0.306 ↑ 1.0 23 102

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

44. 0.204 0.612 ↑ 1.0 1 102

Hash (cost=4.17..4.17 rows=1 width=34) (actual time=0.006..0.006 rows=1 loops=102)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
45. 0.408 0.408 ↑ 1.0 1 102

Index Scan using pk_market on market market15_ (cost=0.14..4.17 rows=1 width=34) (actual time=0.004..0.004 rows=1 loops=102)

  • Index Cond: (submarket14_.market_id = id)
46. 18.666 18.666 ↑ 1.0 1 102

Index Scan using stream_insertion_pkey on stream_insertion streaminse17_ (cost=0.43..6.19 rows=1 width=12) (actual time=0.182..0.183 rows=1 loops=102)

  • Index Cond: (streamcrea8_.origin_stream_insertion_id = id)