explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NnAM

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 290,851.081 ↓ 102.0 102 1

Limit (cost=1,052,755.98..1,052,789.74 rows=1 width=898) (actual time=290,850.998..290,851.081 rows=102 loops=1)

2. 0.881 290,851.075 ↓ 102.0 102 1

HashAggregate (cost=1,052,755.98..1,052,789.74 rows=1 width=898) (actual time=290,850.998..290,851.075 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.376 290,850.194 ↓ 102.0 102 1

Nested Loop Left Join (cost=855,722.72..1,052,755.87 rows=1 width=898) (actual time=278,619.919..290,850.194 rows=102 loops=1)

4. 0.595 290,827.990 ↓ 102.0 102 1

Nested Loop Left Join (cost=855,722.29..1,052,715.92 rows=1 width=894) (actual time=278,599.543..290,827.990 rows=102 loops=1)

  • Join Filter: (market15_.sector_id = sector16_.id)
  • Rows Removed by Join Filter: 2244
5. 2.098 290,826.987 ↓ 102.0 102 1

Nested Loop Left Join (cost=855,722.29..1,052,714.40 rows=1 width=877) (actual time=278,599.530..290,826.987 rows=102 loops=1)

  • Join Filter: (submarket14_.market_id = market15_.id)
  • Rows Removed by Join Filter: 17136
6. 14.078 290,823.257 ↓ 102.0 102 1

Nested Loop Left Join (cost=855,722.29..1,052,708.60 rows=1 width=851) (actual time=278,599.507..290,823.257 rows=102 loops=1)

  • Join Filter: (creative9_.sub_market_id = submarket14_.id)
  • Rows Removed by Join Filter: 139740
7. 0.215 290,797.449 ↓ 102.0 102 1

Nested Loop (cost=855,722.29..1,052,664.75 rows=1 width=826) (actual time=278,599.209..290,797.449 rows=102 loops=1)

8. 0.690 290,796.622 ↓ 102.0 102 1

Nested Loop Left Join (cost=855,722.00..1,052,660.15 rows=1 width=808) (actual time=278,599.186..290,796.622 rows=102 loops=1)

  • Join Filter: (creative9_.creative_type_id = creativety10_.id)
  • Rows Removed by Join Filter: 1428
9. 77.048 290,795.524 ↓ 102.0 102 1

Nested Loop (cost=855,722.00..1,052,658.81 rows=1 width=785) (actual time=278,599.165..290,795.524 rows=102 loops=1)

  • Join Filter: (streamcrea8_.id = streaminse0_.creative_id)
  • Rows Removed by Join Filter: 696882
10. 332.661 568.489 ↓ 113.0 113 1

Nested Loop (cost=9.84..8,074.67 rows=1 width=411) (actual time=37.927..568.489 rows=113 loops=1)

  • Join Filter: (creative9_.id = streamcrea8_.creative_id)
  • Rows Removed by Join Filter: 3340658
11. 71.258 71.258 ↓ 1.0 16,457 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.011..71.258 rows=16,457 loops=1)

12. 161.248 164.570 ↓ 203.0 203 16,457

Materialize (cost=9.56..41.85 rows=1 width=395) (actual time=0.000..0.010 rows=203 loops=16,457)

13. 0.050 3.322 ↓ 203.0 203 1

Nested Loop (cost=9.56..41.85 rows=1 width=395) (actual time=0.517..3.322 rows=203 loops=1)

14. 0.124 0.124 ↑ 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.123..0.124 rows=1 loops=1)

  • Index Cond: ((name)::text = 'OPAP CYPRUS'::text)
15. 0.081 3.148 ↓ 203.0 203 1

Nested Loop (cost=9.27..33.53 rows=1 width=376) (actual time=0.387..3.148 rows=203 loops=1)

16. 0.033 0.195 ↑ 1.0 2 1

Bitmap Heap Scan on product product11_ (cost=8.85..16.63 rows=2 width=32) (actual time=0.180..0.195 rows=2 loops=1)

  • Recheck Cond: (((name)::text = 'OPAP JOKER'::text) OR ((name)::text = 'OPAP JOKER JACKPOT'::text))
  • Heap Blocks: exact=2
17. 0.000 0.162 ↓ 0.0 0 1

BitmapOr (cost=8.85..8.85 rows=2 width=0) (actual time=0.162..0.162 rows=0 loops=1)

18. 0.152 0.152 ↑ 1.0 1 1

Bitmap Index Scan on product_name_index (cost=0.00..4.43 rows=1 width=0) (actual time=0.152..0.152 rows=1 loops=1)

  • Index Cond: ((name)::text = 'OPAP JOKER'::text)
19. 0.010 0.010 ↑ 1.0 1 1

Bitmap Index Scan on product_name_index (cost=0.00..4.43 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((name)::text = 'OPAP JOKER JACKPOT'::text)
20. 2.872 2.872 ↓ 102.0 102 2

Index Scan using pd_index on creative creative9_ (cost=0.41..8.44 rows=1 width=352) (actual time=0.112..1.436 rows=102 loops=2)

  • Index Cond: ((product_id = product11_.id) AND (company_id = company13_.id))
  • Filter: verified
21. 115.712 290,149.987 ↑ 8.0 6,168 113

Nested Loop (cost=855,712.15..1,043,970.07 rows=49,125 width=382) (actual time=2,232.785..2,567.699 rows=6,168 loops=113)

22. 0.791 2.712 ↑ 1.0 1 113

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

  • Recheck Cond: (subject_id = 6)
  • Heap Blocks: exact=113
23. 1.921 1.921 ↑ 1.0 1 113

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

  • Index Cond: (subject_id = 6)
24. 134.357 290,031.563 ↑ 8.0 6,168 113

Nested Loop (cost=855,707.87..1,043,470.53 rows=49,125 width=386) (actual time=2,232.758..2,566.651 rows=6,168 loops=113)

25. 1.808 1.808 ↑ 1.0 1 113

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

  • Filter: (article_category_id = 14)
  • Rows Removed by Filter: 12
26. 232.765 289,895.398 ↑ 8.0 6,168 113

Hash Join (cost=855,707.87..1,042,978.12 rows=49,125 width=390) (actual time=2,232.534..2,565.446 rows=6,168 loops=113)

  • Hash Cond: (media3_.mediatype_id = mediatype4_.mediatype_id)
27. 247.686 289,662.618 ↑ 8.0 6,168 113

Hash Join (cost=855,706.44..1,042,301.22 rows=49,125 width=382) (actual time=2,232.530..2,563.386 rows=6,168 loops=113)

  • Hash Cond: (article2_.media_id = media3_.media_id)
28. 70,845.802 289,414.244 ↑ 8.0 6,168 113

Hash Join (cost=855,638.10..1,041,496.00 rows=49,125 width=362) (actual time=2,232.520..2,561.188 rows=6,168 loops=113)

  • Hash Cond: (subjects6_.article_id = insertion1_.article_id)
29. 40,726.895 65,908.606 ↑ 1.0 2,770,151 113

Bitmap Heap Scan on articles_subjects subjects6_ (cost=54,050.59..204,075.55 rows=2,859,117 width=8) (actual time=227.997..583.262 rows=2,770,151 loops=113)

  • Recheck Cond: (subject_id = 6)
  • Heap Blocks: exact=2375599
30. 25,181.711 25,181.711 ↑ 1.0 2,770,154 113

Bitmap Index Scan on subject_articles_subject (cost=0.00..53,335.81 rows=2,859,117 width=0) (actual time=222.847..222.847 rows=2,770,154 loops=113)

  • Index Cond: (subject_id = 6)
31. 852.246 152,659.836 ↑ 7.7 6,168 113

Hash (cost=798,709.65..798,709.65 rows=47,669 width=362) (actual time=1,350.972..1,350.972 rows=6,168 loops=113)

  • Buckets: 8192 Batches: 2 Memory Usage: 1166kB
32. 281.483 151,807.590 ↑ 7.7 6,168 113

Nested Loop (cost=13,293.41..798,709.65 rows=47,669 width=362) (actual time=344.843..1,343.430 rows=6,168 loops=113)

33. 45,639.238 149,435.155 ↑ 21.1 6,168 113

Hash Join (cost=13,292.98..148,141.90 rows=129,984 width=237) (actual time=344.813..1,322.435 rows=6,168 loops=113)

  • Hash Cond: (insertion1_.id = streaminse0_.insertion_id)
34. 103,727.672 103,727.672 ↑ 1.0 2,666,714 113

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

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

Hash (cost=11,604.37..11,604.37 rows=135,089 width=64) (actual time=68.245..68.245 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..11604.37 rows=135089 width=64) (actual time=0.034.. (...)
  • 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
36. 2,090.952 2,090.952 ↑ 1.0 1 696,984

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=696,984)

  • Index Cond: (article_id = insertion1_.article_id)
  • Filter: (article_category_id = 14)
37. 0.340 0.688 ↓ 1.0 1,796 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 101kB
38. 0.348 0.348 ↓ 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.348 rows=1,796 loops=1)

39. 0.007 0.015 ↑ 1.0 19 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
40. 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.007..0.008 rows=19 loops=1)

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

42. 0.612 0.612 ↑ 1.0 1 102

Index Scan using pk_brand on brand brand12_ (cost=0.29..4.60 rows=1 width=26) (actual time=0.006..0.006 rows=1 loops=102)

  • Index Cond: (id = creative9_.brand_id)
  • Filter: ((name)::text = 'OPAP CYPRUS'::text)
43. 11.730 11.730 ↑ 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.115 rows=1,371 loops=102)

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

45. 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.002..0.004 rows=23 loops=102)

46. 20.910 20.910 ↑ 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.204..0.205 rows=1 loops=102)

  • Index Cond: (streamcrea8_.origin_stream_insertion_id = id)
47.          

SubPlan (forNested Loop Left Join)

48. 0.102 0.918 ↑ 1.0 1 102

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

49. 0.000 0.816 ↓ 0.0 0 102

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

50. 0.204 0.816 ↓ 0.0 0 102

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

51. 0.408 0.408 ↑ 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.004..0.004 rows=1 loops=102)

  • Index Cond: (id = creative9_.id)
  • Heap Fetches: 102
52. 0.204 0.204 ↓ 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.002..0.002 rows=0 loops=102)

  • Index Cond: (creative_id = creative9_.id)
53. 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)