explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 27N8

Settings
# exclusive inclusive rows x rows loops node
1. 1,876.496 27,636.904 ↑ 41.3 4,641,131 1

Merge Left Join (cost=2,076,499.76..5,130,321.65 rows=191,715,530 width=136) (actual time=22,145.192..27,636.904 rows=4,641,131 loops=1)

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

CTE vod_publication

3. 261.348 2,765.568 ↓ 1.0 363,354 1

WindowAgg (cost=167,634.20..174,894.54 rows=363,017 width=55) (actual time=2,434.467..2,765.568 rows=363,354 loops=1)

4. 2,202.390 2,504.220 ↓ 1.0 363,354 1

Sort (cost=167,634.20..168,541.75 rows=363,017 width=55) (actual time=2,434.444..2,504.220 rows=363,354 loops=1)

  • Sort Key: vv.distributor, vv.title
  • Sort Method: quicksort Memory: 54965kB
5. 301.830 301.830 ↓ 1.0 363,354 1

Seq Scan on vods_vod vv (cost=0.00..134,110.17 rows=363,017 width=55) (actual time=0.004..301.830 rows=363,354 loops=1)

6. 957.226 22,834.409 ↑ 3.1 2,832,388 1

Merge Left Join (cost=1,739,082.55..1,894,869.33 rows=8,920,865 width=136) (actual time=20,026.279..22,834.409 rows=2,832,388 loops=1)

  • Merge Cond: (l.possession_id = np.possession_id)
7. 10,114.220 19,856.917 ↑ 2.7 1,693,908 1

Sort (cost=1,696,770.02..1,708,400.71 rows=4,652,277 width=136) (actual time=18,195.273..19,856.917 rows=1,693,908 loops=1)

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

Merge Left Join (cost=655,858.90..863,514.34 rows=4,652,277 width=136) (actual time=8,300.283..9,742.697 rows=1,693,908 loops=1)

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

Sort (cost=651,799.91..656,073.04 rows=1,709,250 width=136) (actual time=8,259.912..9,066.878 rows=1,693,908 loops=1)

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

Hash Left Join (cost=199,680.59..358,006.39 rows=1,709,250 width=136) (actual time=3,772.681..5,377.403 rows=1,693,908 loops=1)

  • Hash Cond: (l.id = fl.id)
11. 529.907 4,780.395 ↑ 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,704.286..4,780.395 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. 555.230 710.278 ↑ 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.483..710.278 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
13. 155.048 155.048 ↑ 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=155.048..155.048 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. 91.463 3,540.210 ↑ 1.0 363,003 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 26977kB
15. 193.313 3,448.747 ↓ 1.0 363,354 1

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

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

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

17. 81.411 324.620 ↓ 1.0 363,354 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 29468kB
18. 243.209 243.209 ↓ 1.0 363,354 1

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

19. 38.542 62.876 ↑ 1.0 296,041 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 14504kB
20. 24.334 24.334 ↑ 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.025..24.334 rows=296,041 loops=1)

21. 93.289 100.409 ↓ 44.6 871,164 1

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

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

  • Heap Fetches: 4171
23. 1,776.671 2,020.266 ↓ 7.9 2,512,350 1

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

  • Sort Key: np.possession_id
  • Sort Method: quicksort Memory: 27213kB
24. 206.063 243.595 ↓ 1.0 318,386 1

Hash Left Join (cost=25.25..13,220.86 rows=318,290 width=14) (actual time=0.209..243.595 rows=318,386 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.381 37.381 ↓ 1.0 318,386 1

Seq Scan on new_payments np (cost=0.00..10,678.90 rows=318,290 width=38) (actual time=0.024..37.381 rows=318,386 loops=1)

26. 0.063 0.151 ↑ 1.0 100 1

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

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

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

28. 2,496.070 2,925.999 ↓ 5.4 6,914,192 1

Sort (cost=162,522.66..165,726.19 rows=1,281,410 width=32) (actual time=2,118.898..2,925.999 rows=6,914,192 loops=1)

  • Sort Key: p.possession_id
  • Sort Method: external sort Disk: 73728kB
29. 429.929 429.929 ↓ 2.0 2,608,937 1

Seq Scan on possessions p (cost=0.00..32,528.10 rows=1,281,410 width=32) (actual time=0.030..429.929 rows=2,608,937 loops=1)