explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M9iC

Settings
# exclusive inclusive rows x rows loops node
1. 915.242 23,512.448 ↑ 3.1 2,832,388 1

Merge Left Join (cost=1,509,124.11..1,907,307.06 rows=8,920,823 width=136) (actual time=21,356.815..23,512.448 rows=2,832,388 loops=1)

  • Merge Cond: (l.media_id = ((live.id)::text))
  • Join Filter: (l.cpid = 0)
2.          

CTE vod_publication

3. 268.978 2,761.954 ↓ 1.0 363,353 1

WindowAgg (cost=167,634.20..174,894.54 rows=363,017 width=55) (actual time=2,423.482..2,761.954 rows=363,353 loops=1)

4. 2,187.468 2,492.976 ↓ 1.0 363,353 1

Sort (cost=167,634.20..168,541.75 rows=363,017 width=55) (actual time=2,423.468..2,492.976 rows=363,353 loops=1)

  • Sort Key: vv.distributor, vv.title
  • Sort Method: quicksort Memory: 54964kB
5. 305.508 305.508 ↓ 1.0 363,353 1

Seq Scan on vods_vod vv (cost=0.00..134,110.17 rows=363,017 width=55) (actual time=0.007..305.508 rows=363,353 loops=1)

6. 5,479.834 22,456.458 ↑ 1.2 2,832,388 1

Sort (cost=1,330,170.58..1,338,364.37 rows=3,277,517 width=136) (actual time=21,315.912..22,456.458 rows=2,832,388 loops=1)

  • Sort Key: l.media_id
  • Sort Method: external merge Disk: 424832kB
7. 731.572 16,976.624 ↑ 1.2 2,832,388 1

Hash Left Join (cost=644,216.71..751,422.37 rows=3,277,517 width=136) (actual time=12,364.782..16,976.624 rows=2,832,388 loops=1)

  • Hash Cond: (l.id = fl.id)
8. 959.336 16,182.738 ↑ 1.2 2,832,388 1

Merge Right Join (cost=636,245.79..728,909.72 rows=3,277,517 width=136) (actual time=12,301.755..16,182.738 rows=2,832,388 loops=1)

  • Merge Cond: (np.possession_id = l.possession_id)
9. 225.225 225.225 ↑ 1.0 307,335 1

Index Only Scan using new_payments_possession_id_idx on new_payments np (cost=0.42..39,599.21 rows=318,120 width=14) (actual time=0.015..225.225 rows=307,335 loops=1)

  • Heap Fetches: 307335
10. 233.791 14,998.177 ↓ 1.7 2,832,388 1

Materialize (cost=636,245.36..644,791.61 rows=1,709,250 width=136) (actual time=12,277.755..14,998.177 rows=2,832,388 loops=1)

11. 10,028.179 14,764.386 ↑ 1.0 1,693,908 1

Sort (cost=636,245.36..640,518.49 rows=1,709,250 width=136) (actual time=12,277.751..14,764.386 rows=1,693,908 loops=1)

  • Sort Key: l.possession_id
  • Sort Method: external merge Disk: 253064kB
12. 515.242 4,736.207 ↑ 1.0 1,693,908 1

Hash Left Join (cost=191,709.66..342,451.84 rows=1,709,250 width=136) (actual time=3,700.191..4,736.207 rows=1,693,908 loops=1)

  • Hash Cond: (l.media_id = (vod.hashkey)::text)
  • Join Filter: (l.cpid = 1)
  • Rows Removed by Join Filter: 123449
13. 529.790 684.548 ↑ 1.0 1,693,908 1

Bitmap Heap Scan on licenses l (cost=36,272.25..159,239.12 rows=1,709,250 width=136) (actual time=162.185..684.548 rows=1,693,908 loops=1)

  • Recheck Cond: ((valid_from >= '2019-03-01 00:00:00+01'::timestamp with time zone) AND (valid_from <= '2019-03-31 00:00:00+01'::timestamp with time zone))
  • Filter: (portal = 'ipla'::text)
  • Heap Blocks: exact=36742
14. 154.758 154.758 ↑ 1.0 1,693,908 1

Bitmap Index Scan on licenses_valid_from_idx (cost=0.00..35,844.93 rows=1,709,250 width=0) (actual time=154.758..154.758 rows=1,693,908 loops=1)

  • Index Cond: ((valid_from >= '2019-03-01 00:00:00+01'::timestamp with time zone) AND (valid_from <= '2019-03-31 00:00:00+01'::timestamp with time zone))
15. 89.882 3,536.417 ↑ 1.0 363,002 1

Hash (cost=150,899.71..150,899.71 rows=363,017 width=32) (actual time=3,536.417..3,536.417 rows=363,002 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 26977kB
16. 194.780 3,446.535 ↓ 1.0 363,353 1

Hash Right Join (cost=138,647.88..150,899.71 rows=363,017 width=32) (actual time=2,751.953..3,446.535 rows=363,353 loops=1)

  • Hash Cond: (vp.id = vod.id)
17. 2,924.867 2,924.867 ↓ 1.0 363,353 1

CTE Scan on vod_publication vp (cost=0.00..7,260.34 rows=363,017 width=4) (actual time=2,423.489..2,924.867 rows=363,353 loops=1)

18. 82.969 326.888 ↓ 1.0 363,353 1

Hash (cost=134,110.17..134,110.17 rows=363,017 width=36) (actual time=326.888..326.888 rows=363,353 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 29468kB
19. 243.919 243.919 ↓ 1.0 363,353 1

Seq Scan on vods_vod vod (cost=0.00..134,110.17 rows=363,017 width=36) (actual time=0.008..243.919 rows=363,353 loops=1)

20. 33.906 62.314 ↑ 1.0 296,041 1

Hash (cost=4,270.41..4,270.41 rows=296,041 width=4) (actual time=62.314..62.314 rows=296,041 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 14504kB
21. 28.408 28.408 ↑ 1.0 296,041 1

Seq Scan on licenses_first fl (cost=0.00..4,270.41 rows=296,041 width=4) (actual time=0.012..28.408 rows=296,041 loops=1)

22. 133.603 140.748 ↓ 72.1 1,406,955 1

Sort (cost=4,058.98..4,107.79 rows=19,523 width=4) (actual time=40.894..140.748 rows=1,406,955 loops=1)

  • Sort Key: ((live.id)::text)
  • Sort Method: quicksort Memory: 1640kB
23. 7.145 7.145 ↑ 1.1 18,585 1

Index Only Scan using live_channel_pkey on live_channel live (cost=0.29..2,667.69 rows=19,523 width=4) (actual time=0.046..7.145 rows=18,585 loops=1)

  • Heap Fetches: 4147