explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Wy9

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 5,429.816 ↑ 1.4 5 1

Finalize GroupAggregate (cost=6,626,767.78..6,626,771.00 rows=7 width=15) (actual time=5,429.776..5,429.816 rows=5 loops=1)

  • Group Key: o.media_subtype
2. 0.000 5,429.788 ↑ 1.4 30 1

Sort (cost=6,626,767.78..6,626,768.83 rows=42 width=15) (actual time=5,429.763..5,429.788 rows=30 loops=1)

  • Sort Key: o.media_subtype
  • Sort Method: quicksort Memory: 26kB
3. 497.106 5,915.492 ↑ 1.4 30 1

Gather (cost=6,626,752.19..6,626,756.46 rows=42 width=15) (actual time=5,426.997..5,915.492 rows=30 loops=1)

  • Workers Planned: 6
  • Workers Launched: 6
4. 1.505 5,418.386 ↑ 1.8 4 7

Partial HashAggregate (cost=6,625,752.19..6,625,752.26 rows=7 width=15) (actual time=5,418.382..5,418.386 rows=4 loops=7)

  • Group Key: o.media_subtype
5. 1.899 5,416.881 ↑ 1,260.0 1,302 7

Hash Join (cost=55,080.44..6,543,729.14 rows=1,640,461 width=11) (actual time=577.596..5,416.881 rows=1,302 loops=7)

  • Hash Cond: (a.project_id = p.id)
6. 1.743 5,397.165 ↑ 1,260.0 1,302 7

Hash Left Join (cost=52,762.81..6,498,346.16 rows=1,640,461 width=15) (actual time=559.698..5,397.165 rows=1,302 loops=7)

  • Hash Cond: (a.outlet_id = ot.outlet_id)
  • Filter: ((ot.id IS NULL) OR (ot.tier_type = ANY ('{1,2}'::integer[])))
7. 11.423 5,395.374 ↑ 1,260.0 1,302 7

Hash Join (cost=52,556.06..6,455,076.25 rows=1,640,461 width=19) (actual time=559.626..5,395.374 rows=1,302 loops=7)

  • Hash Cond: (a.outlet_id = o.id)
8. 1.330 4,909.976 ↑ 1,263.0 1,302 7

Append (cost=1.06..6,359,353.98 rows=1,644,463 width=12) (actual time=76.513..4,909.976 rows=1,302 loops=7)

9. 0.000 0.002 ↓ 0.0 0 7

Parallel Seq Scan on articles a (cost=1.06..1.06 rows=1 width=12) (actual time=0.001..0.002 rows=0 loops=7)

  • Filter: (active AND (published_at >= '2019-05-06 00:00:00'::timestamp without time zone) AND (published_at <= '2019-06-06 23:59:59.999'::timestamp without time zone) AND ((project_id = 7311) OR (hashed SubPlan 1)) AND (sentiment = ANY ('{-1,-2,0,1,2}'::double precision[])))
10.          

SubPlan (forParallel Seq Scan)

11. 0.008 0.008 ↑ 1.0 1 110

Seq Scan on project_custom_industries (cost=0.00..1.03 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=110)

  • Filter: (project_id = 7311)
12. 4,431.363 4,431.363 ↑ 1,147.7 1,278 7

Parallel Seq Scan on articles_2019_05 a_1 (cost=1.06..5,777,958.01 rows=1,466,702 width=12) (actual time=76.509..4,431.363 rows=1,278 loops=7)

  • Filter: (active AND (published_at >= '2019-05-06 00:00:00'::timestamp without time zone) AND (published_at <= '2019-06-06 23:59:59.999'::timestamp without time zone) AND ((project_id = 7311) OR (hashed SubPlan 1)) AND (sentiment = ANY ('{-1,-2,0,1,2}'::double precision[])))
  • Rows Removed by Filter: 2974137
13. 477.281 477.281 ↑ 7,406.7 24 7

Parallel Seq Scan on articles_2019_06 a_2 (cost=1.06..581,394.91 rows=177,760 width=12) (actual time=23.543..477.281 rows=24 loops=7)

  • Filter: (active AND (published_at >= '2019-05-06 00:00:00'::timestamp without time zone) AND (published_at <= '2019-06-06 23:59:59.999'::timestamp without time zone) AND ((project_id = 7311) OR (hashed SubPlan 1)) AND (sentiment = ANY ('{-1,-2,0,1,2}'::double precision[])))
  • Rows Removed by Filter: 306623
14. 208.014 473.975 ↑ 1.0 331,038 7

Hash (cost=40,967.03..40,967.03 rows=331,085 width=11) (actual time=473.975..473.975 rows=331,038 loops=7)

  • Buckets: 524288 Batches: 1 Memory Usage: 18355kB
15. 265.961 265.961 ↑ 1.0 331,038 7

Seq Scan on outlets o (cost=0.00..40,967.03 rows=331,085 width=11) (actual time=0.062..265.961 rows=331,038 loops=7)

  • Filter: (active AND ((media_subtype)::text = ANY ('{tv,radio,newspaper,magazine,website,blogs}'::text[])))
  • Rows Removed by Filter: 80
16. 0.004 0.048 ↑ 444.0 1 7

Hash (cost=191.21..191.21 rows=444 width=12) (actual time=0.048..0.048 rows=1 loops=7)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.044 0.044 ↑ 444.0 1 7

Index Scan using index_outlet_tiers_on_project_id on outlet_tiers ot (cost=4.22..191.21 rows=444 width=12) (actual time=0.042..0.044 rows=1 loops=7)

  • Index Cond: (project_id = 7311)
  • Filter: active
  • Rows Removed by Filter: 2
18. 6.057 17.817 ↑ 1.0 10,707 7

Hash (cost=1,942.25..1,942.25 rows=10,725 width=4) (actual time=17.816..17.817 rows=10,707 loops=7)

  • Buckets: 16384 Batches: 1 Memory Usage: 505kB
19. 11.760 11.760 ↑ 1.0 10,707 7

Seq Scan on projects p (cost=0.00..1,942.25 rows=10,725 width=4) (actual time=0.020..11.760 rows=10,707 loops=7)

Planning time : 10.585 ms