explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ezFh

Settings
# exclusive inclusive rows x rows loops node
1. 1,877.080 27,449.920 ↑ 3.9 4,641,131 1

Merge Left Join (cost=2,224,176.56..2,671,551.85 rows=18,089,774 width=136) (actual time=21,932.327..27,449.920 rows=4,641,131 loops=1)

  • Merge Cond: (l.possession_id = p.possession_id)
2.          

CTE vod_publication

3. 263.320 2,766.694 ↓ 1.0 363,368 1

WindowAgg (cost=167,634.20..174,894.54 rows=363,017 width=55) (actual time=2,437.469..2,766.694 rows=363,368 loops=1)

4. 2,201.816 2,503.374 ↓ 1.0 363,368 1

Sort (cost=167,634.20..168,541.75 rows=363,017 width=55) (actual time=2,437.444..2,503.374 rows=363,368 loops=1)

  • Sort Key: vv.distributor, vv.title
  • Sort Method: quicksort Memory: 54966kB
5. 301.558 301.558 ↓ 1.0 363,368 1

Seq Scan on vods_vod vv (cost=0.00..134,110.17 rows=363,017 width=55) (actual time=0.005..301.558 rows=363,368 loops=1)

6. 958.486 22,723.233 ↑ 3.1 2,832,388 1

Merge Left Join (cost=1,725,430.79..1,879,409.98 rows=8,819,792 width=136) (actual time=19,890.024..22,723.233 rows=2,832,388 loops=1)

  • Merge Cond: (l.possession_id = np.possession_id)
7. 9,714.230 19,713.709 ↑ 2.7 1,693,908 1

Sort (cost=1,683,118.25..1,694,595.90 rows=4,591,062 width=136) (actual time=18,048.064..19,713.709 rows=1,693,908 loops=1)

  • Sort Key: l.possession_id
  • Sort Method: external merge Disk: 253056kB
8. 574.046 9,999.479 ↑ 2.7 1,693,908 1

Merge Left Join (cost=655,537.81..861,263.14 rows=4,591,062 width=136) (actual time=8,481.989..9,999.479 rows=1,693,908 loops=1)

  • Merge Cond: (l.media_id = ((live.id)::text))
  • Join Filter: (l.cpid = 0)
9. 3,591.222 9,323.217 ↑ 1.0 1,693,908 1

Sort (cost=651,478.82..655,738.84 rows=1,704,006 width=136) (actual time=8,441.577..9,323.217 rows=1,693,908 loops=1)

  • Sort Key: l.media_id
  • Sort Method: external merge Disk: 253064kB
10. 543.404 5,731.995 ↑ 1.0 1,693,908 1

Hash Left Join (cost=199,596.25..358,622.95 rows=1,704,006 width=136) (actual time=3,875.678..5,731.995 rows=1,693,908 loops=1)

  • Hash Cond: (l.id = fl.id)
11. 527.146 5,123.782 ↑ 1.0 1,693,908 1

Hash Left Join (cost=191,595.91..343,069.10 rows=1,704,006 width=136) (actual time=3,805.384..5,123.782 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
12. 811.169 1,050.709 ↑ 1.0 1,693,908 1

Bitmap Heap Scan on licenses l (cost=36,158.49..159,941.60 rows=1,704,006 width=136) (actual time=257.927..1,050.709 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=84659
13. 239.540 239.540 ↑ 1.0 1,693,908 1

Bitmap Index Scan on licenses_valid_from_idx (cost=0.00..35,732.49 rows=1,704,006 width=0) (actual time=239.540..239.540 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))
14. 90.259 3,545.927 ↑ 1.0 363,017 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 26978kB
15. 189.689 3,455.668 ↓ 1.0 363,368 1

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

  • Hash Cond: (vp.id = vod.id)
16. 2,936.914 2,936.914 ↓ 1.0 363,368 1

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

17. 83.430 329.065 ↓ 1.0 363,368 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 29469kB
18. 245.635 245.635 ↓ 1.0 363,368 1

Seq Scan on vods_vod vod (cost=0.00..134,110.17 rows=363,017 width=36) (actual time=0.007..245.635 rows=363,368 loops=1)

19. 38.792 64.809 ↑ 1.0 297,126 1

Hash (cost=4,286.26..4,286.26 rows=297,126 width=4) (actual time=64.809..64.809 rows=297,126 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 14542kB
20. 26.017 26.017 ↑ 1.0 297,126 1

Seq Scan on licenses_first fl (cost=0.00..4,286.26 rows=297,126 width=4) (actual time=0.034..26.017 rows=297,126 loops=1)

21. 95.100 102.216 ↓ 44.6 871,164 1

Sort (cost=4,058.98..4,107.79 rows=19,523 width=4) (actual time=40.404..102.216 rows=871,164 loops=1)

  • Sort Key: ((live.id)::text)
  • Sort Method: quicksort Memory: 1640kB
22. 7.116 7.116 ↑ 1.1 18,587 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.037..7.116 rows=18,587 loops=1)

  • Heap Fetches: 4172
23. 1,806.705 2,051.038 ↓ 7.9 2,512,350 1

Sort (cost=42,312.54..43,108.26 rows=318,290 width=14) (actual time=1,827.485..2,051.038 rows=2,512,350 loops=1)

  • Sort Key: np.possession_id
  • Sort Method: quicksort Memory: 27215kB
24. 206.468 244.333 ↓ 1.0 318,425 1

Hash Left Join (cost=25.25..13,220.86 rows=318,290 width=14) (actual time=0.213..244.333 rows=318,425 loops=1)

  • Hash Cond: (np.product_id = lower(sc.code))
  • Join Filter: ((np.product_sub_type = 'packet'::text) AND (np.product_type = 'multiple'::text))
25. 37.709 37.709 ↓ 1.0 318,425 1

Seq Scan on new_payments np (cost=0.00..10,678.90 rows=318,290 width=38) (actual time=0.025..37.709 rows=318,425 loops=1)

26. 0.079 0.156 ↑ 1.0 100 1

Hash (cost=24.00..24.00 rows=100 width=8) (actual time=0.156..0.156 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
27. 0.077 0.077 ↑ 1.0 100 1

Seq Scan on payments_sellablecategory sc (cost=0.00..24.00 rows=100 width=8) (actual time=0.007..0.077 rows=100 loops=1)

28. 2,519.398 2,849.607 ↓ 2.7 6,914,192 1

Sort (cost=323,851.23..330,373.57 rows=2,608,937 width=14) (actual time=2,042.292..2,849.607 rows=6,914,192 loops=1)

  • Sort Key: p.possession_id
  • Sort Method: external sort Disk: 73728kB
29. 330.209 330.209 ↑ 1.0 2,608,937 1

Seq Scan on possessions p (cost=0.00..45,803.37 rows=2,608,937 width=14) (actual time=0.021..330.209 rows=2,608,937 loops=1)

Planning time : 3.517 ms