explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YvXZ

Settings
# exclusive inclusive rows x rows loops node
1. 10,057.470 3,271,818.673 ↓ 1.4 781,552 1

GroupAggregate (cost=5,282,324.00..5,310,796.50 rows=569,450 width=188) (actual time=3,242,576.256..3,271,818.673 rows=781,552 loops=1)

  • Group Key: (CASE WHEN (sc.id = 98) THEN 97 ELSE sc.id END), (COALESCE(live.id, vod.provider_file_id)), (CASE WHEN (l.cpid = 0) THEN 'live'::text WHEN (l.cpid = 1) THEN 'vod'::text ELSE NULL::text END), (btrim((COALESCE(live.distributor, vod.distributor)))), (COALESCE(report_getmedia.utils_strip_whitespaces(live.title), report_getmedia.utils_strip_whitespaces(vod.title))), (COALESCE(live.title, vod.title_en)), (COALESCE(live.planned_start, vp.first_published_at)), l.user_id, (CASE WHEN (CASE WHEN ((np.option_type = 'cpwallet'::text) OR (p.cause = 'cp'::text)) THEN true ELSE false END) THEN 'cp'::text WHEN (((CASE WHEN (np.option_type = ANY ('{dotpay,paypal}'::text[])) THEN 'transfer'::text ELSE np.option_type END) = 'plusmt'::text) OR (np.option_sms_operator = 'P'::text) OR ((CASE WHEN (np.option_type = ANY ('{dotpay,paypal}'::text[])) THEN 'transfer'::text ELSE np.option_type END) = 'plusbill'::text)) THEN 'plk'::text ELSE 'free'::text END)
2. 130,458.056 3,261,761.203 ↓ 44.9 25,586,903 1

Sort (cost=5,282,324.00..5,283,747.63 rows=569,450 width=188) (actual time=3,242,576.200..3,261,761.203 rows=25,586,903 loops=1)

  • Sort Key: (CASE WHEN (sc.id = 98) THEN 97 ELSE sc.id END), (COALESCE(live.id, vod.provider_file_id)), (CASE WHEN (l.cpid = 0) THEN 'live'::text WHEN (l.cpid = 1) THEN 'vod'::text ELSE NULL::text END), (btrim((COALESCE(live.distributor, vod.distributor)))), (COALESCE(report_getmedia.utils_strip_whitespaces(live.title), report_getmedia.utils_strip_whitespaces(vod.title))), (COALESCE(live.title, vod.title_en)), (COALESCE(live.planned_start, vp.first_published_at)), l.user_id, (CASE WHEN (CASE WHEN ((np.option_type = 'cpwallet'::text) OR (p.cause = 'cp'::text)) THEN true ELSE false END) THEN 'cp'::text WHEN (((CASE WHEN (np.option_type = ANY ('{dotpay,paypal}'::text[])) THEN 'transfer'::text ELSE np.option_type END) = 'plusmt'::text) OR (np.option_sms_operator = 'P'::text) OR ((CASE WHEN (np.option_type = ANY ('{dotpay,paypal}'::text[])) THEN 'transfer'::text ELSE np.option_type END) = 'plusbill'::text)) THEN 'plk'::text ELSE 'free'::text END)
  • Sort Method: external merge Disk: 2589832kB
3. 19,292.423 3,131,303.147 ↓ 44.9 25,586,903 1

Merge Left Join (cost=3,871,906.63..5,227,886.83 rows=569,450 width=188) (actual time=3,077,937.225..3,131,303.147 rows=25,586,903 loops=1)

  • Merge Cond: ((COALESCE((live.id)::text, (vod.hashkey)::text)) = ((lc.id)::text))
  • Join Filter: ((CASE WHEN (l.cpid = 0) THEN 'live'::text WHEN (l.cpid = 1) THEN 'vod'::text ELSE NULL::text END) = 'live'::text)
  • Filter: ((lc.id IS NULL) OR (lc.test_live IS NULL) OR (NOT lc.test_live))
  • Rows Removed by Filter: 18234
4. 72,494.525 3,111,140.008 ↓ 41.9 25,605,137 1

Sort (cost=3,866,955.11..3,868,483.50 rows=611,358 width=223) (actual time=3,077,888.824..3,111,140.008 rows=25,605,137 loops=1)

  • Sort Key: (COALESCE((live.id)::text, (vod.hashkey)::text))
  • Sort Method: external merge Disk: 3271328kB
5. 13,087.268 3,038,645.483 ↓ 41.9 25,605,137 1

Hash Left Join (cost=2,306,953.01..3,743,420.53 rows=611,358 width=223) (actual time=201,366.421..3,038,645.483 rows=25,605,137 loops=1)

  • Hash Cond: (COALESCE(sc_1.id, pg.sellablecategory_id) = sc.id)
6. 17,931.432 3,025,558.157 ↓ 41.9 25,605,137 1

Hash Left Join (cost=2,306,927.76..3,738,045.90 rows=611,358 width=227) (actual time=201,366.354..3,025,558.157 rows=25,605,137 loops=1)

  • Hash Cond: (np.price_code = pg.code)
7. 2,766,991.144 3,007,626.587 ↓ 41.9 25,605,137 1

Merge Left Join (cost=2,306,909.80..3,723,508.19 rows=611,358 width=559) (actual time=201,366.208..3,007,626.587 rows=25,605,137 loops=1)

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

CTE first_license

9. 729.662 32,019.804 ↓ 5.1 296,041 1

Unique (cost=905,800.58..927,352.35 rows=58,396 width=136) (actual time=22,013.758..32,019.804 rows=296,041 loops=1)

10. 30,809.935 31,290.142 ↑ 1.0 4,310,354 1

Sort (cost=905,800.58..916,576.46 rows=4,310,354 width=136) (actual time=22,013.757..31,290.142 rows=4,310,354 loops=1)

  • Sort Key: licenses.possession_id, licenses.valid_from
  • Sort Method: external merge Disk: 642552kB
11. 480.207 480.207 ↑ 1.0 4,310,354 1

Seq Scan on licenses (cost=0.00..136,158.54 rows=4,310,354 width=136) (actual time=0.008..480.207 rows=4,310,354 loops=1)

12.          

CTE vod_publication

13. 240.361 2,721.774 ↑ 1.0 363,287 1

WindowAgg (cost=167,718.24..174,993.92 rows=363,784 width=54) (actual time=2,420.348..2,721.774 rows=363,287 loops=1)

14. 2,162.029 2,481.413 ↑ 1.0 363,287 1

Sort (cost=167,718.24..168,627.70 rows=363,784 width=54) (actual time=2,420.325..2,481.413 rows=363,287 loops=1)

  • Sort Key: vv.distributor, vv.title
  • Sort Method: quicksort Memory: 54956kB
15. 319.384 319.384 ↑ 1.0 363,287 1

Seq Scan on vods_vod vv (cost=0.00..134,117.84 rows=363,784 width=54) (actual time=0.008..319.384 rows=363,287 loops=1)

16. 65,087.468 238,527.159 ↓ 114.0 25,605,137 1

Sort (cost=1,199,612.00..1,200,173.52 rows=224,607 width=190) (actual time=201,314.276..238,527.159 rows=25,605,137 loops=1)

  • Sort Key: l.media_id
  • Sort Method: external merge Disk: 3577568kB
17. 18,425.518 173,439.691 ↓ 114.0 25,605,137 1

Hash Left Join (cost=1,135,163.53..1,179,647.76 rows=224,607 width=190) (actual time=140,460.087..173,439.691 rows=25,605,137 loops=1)

  • Hash Cond: (np.product_id = lower(sc_1.code))
  • Join Filter: ((np.product_sub_type = 'packet'::text) AND (np.product_type = 'multiple'::text))
18. 7,429.839 155,014.000 ↓ 114.0 25,605,137 1

Merge Join (cost=1,135,138.28..1,177,846.55 rows=224,607 width=210) (actual time=140,459.865..155,014.000 rows=25,605,137 loops=1)

  • Merge Cond: (np.possession_id = l.possession_id)
19. 1,913.454 1,913.454 ↑ 1.0 212,543 1

Index Scan using new_payments_possession_id_idx on new_payments np (cost=0.42..39,962.56 rows=218,535 width=57) (actual time=38.960..1,913.454 rows=212,543 loops=1)

  • Filter: (product_type <> 'media'::text)
  • Rows Removed by Filter: 94792
20. 57,262.424 145,670.707 ↓ 165.3 27,884,374 1

Sort (cost=1,135,137.77..1,135,559.44 rows=168,671 width=181) (actual time=140,392.038..145,670.707 rows=27,884,374 loops=1)

  • Sort Key: l.possession_id
  • Sort Method: external sort Disk: 1660648kB
21. 2,296.380 88,408.283 ↓ 75.4 12,714,135 1

Nested Loop Left Join (cost=242,331.30..1,120,493.87 rows=168,671 width=181) (actual time=46,350.314..88,408.283 rows=12,714,135 loops=1)

  • Filter: (p.bundle_id IS NULL)
  • Rows Removed by Filter: 3142888
22. 880.299 47,652.471 ↓ 198.9 1,748,156 1

Hash Right Join (cost=242,330.74..251,058.48 rows=8,787 width=164) (actual time=46,350.271..47,652.471 rows=1,748,156 loops=1)

  • Hash Cond: (vp.id = vod.id)
23. 2,855.004 2,855.004 ↑ 1.0 363,287 1

CTE Scan on vod_publication vp (cost=0.00..7,275.68 rows=363,784 width=12) (actual time=2,420.352..2,855.004 rows=363,287 loops=1)

24. 641.837 43,917.168 ↓ 198.9 1,748,156 1

Hash (cost=242,220.90..242,220.90 rows=8,787 width=156) (actual time=43,917.168..43,917.168 rows=1,748,156 loops=1)

  • Buckets: 1048576 (originally 16384) Batches: 4 (originally 1) Memory Usage: 124121kB
25. 1,551.581 43,275.331 ↓ 198.9 1,748,156 1

Nested Loop Left Join (cost=178,349.11..242,220.90 rows=8,787 width=156) (actual time=24,077.965..43,275.331 rows=1,748,156 loops=1)

  • Join Filter: (l.cpid = 1)
  • Rows Removed by Join Filter: 127206
26. 686.396 34,731.126 ↓ 198.9 1,748,156 1

Hash Right Join (cost=178,348.69..179,736.79 rows=8,787 width=47) (actual time=24,077.940..34,731.126 rows=1,748,156 loops=1)

  • Hash Cond: (fl.id = l.id)
27. 32,079.227 32,079.227 ↓ 5.1 296,041 1

CTE Scan on first_license fl (cost=0.00..1,167.92 rows=58,396 width=4) (actual time=22,013.764..32,079.227 rows=296,041 loops=1)

28. 488.386 1,965.503 ↓ 198.9 1,748,156 1

Hash (cost=178,238.85..178,238.85 rows=8,787 width=51) (actual time=1,965.503..1,965.503 rows=1,748,156 loops=1)

  • Buckets: 2097152 (originally 16384) Batches: 2 (originally 1) Memory Usage: 114689kB
29. 1,216.975 1,477.117 ↓ 198.9 1,748,156 1

Bitmap Heap Scan on licenses l (cost=36,856.29..178,238.85 rows=8,787 width=51) (actual time=267.629..1,477.117 rows=1,748,156 loops=1)

  • Recheck Cond: ((valid_from >= '2019-03-01 00:00:00+01'::timestamp with time zone) AND (valid_from < '2019-04-01'::date))
  • Filter: ((portal = 'ipla'::text) AND ((valid_from)::date >= '2019-03-01'::date) AND ((valid_from)::date <= '2019-03-31'::date))
  • Heap Blocks: exact=37910
30. 260.142 260.142 ↑ 1.0 1,748,156 1

Bitmap Index Scan on licenses_valid_from_idx (cost=0.00..36,854.09 rows=1,757,366 width=0) (actual time=260.142..260.142 rows=1,748,156 loops=1)

  • Index Cond: ((valid_from >= '2019-03-01 00:00:00+01'::timestamp with time zone) AND (valid_from < '2019-04-01'::date))
31. 6,992.624 6,992.624 ↑ 1.0 1 1,748,156

Index Scan using vods_vod_hashkey_idx on vods_vod vod (cost=0.42..7.10 rows=1 width=109) (actual time=0.004..0.004 rows=1 loops=1,748,156)

  • Index Cond: ((hashkey)::text = l.media_id)
32. 38,459.432 38,459.432 ↑ 5.7 9 1,748,156

Index Scan using possessions_possession_id_idx on possessions p (cost=0.56..98.44 rows=51 width=40) (actual time=0.011..0.022 rows=9 loops=1,748,156)

  • Index Cond: (l.possession_id = possession_id)
33. 0.084 0.173 ↑ 1.0 100 1

Hash (cost=24.00..24.00 rows=100 width=12) (actual time=0.173..0.173 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
34. 0.089 0.089 ↑ 1.0 100 1

Seq Scan on payments_sellablecategory sc_1 (cost=0.00..24.00 rows=100 width=12) (actual time=0.018..0.089 rows=100 loops=1)

35. 2,093.381 2,108.284 ↓ 590.2 11,521,822 1

Sort (cost=4,951.53..5,000.33 rows=19,523 width=82) (actual time=50.030..2,108.284 rows=11,521,822 loops=1)

  • Sort Key: ((live.id)::text)
  • Sort Method: quicksort Memory: 3710kB
36. 14.903 14.903 ↑ 1.1 18,584 1

Seq Scan on live_channel live (cost=0.00..3,560.23 rows=19,523 width=82) (actual time=0.019..14.903 rows=18,584 loops=1)

37. 0.025 0.138 ↓ 1.0 270 1

Hash (cost=14.65..14.65 rows=265 width=7) (actual time=0.138..0.138 rows=270 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
38. 0.113 0.113 ↓ 1.0 270 1

Seq Scan on payments_pricegroup pg (cost=0.00..14.65 rows=265 width=7) (actual time=0.008..0.113 rows=270 loops=1)

39. 0.012 0.058 ↑ 1.0 100 1

Hash (cost=24.00..24.00 rows=100 width=4) (actual time=0.058..0.058 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
40. 0.046 0.046 ↑ 1.0 100 1

Seq Scan on payments_sellablecategory sc (cost=0.00..24.00 rows=100 width=4) (actual time=0.003..0.046 rows=100 loops=1)

41. 857.947 870.716 ↓ 590.2 11,521,822 1

Sort (cost=4,951.53..5,000.33 rows=19,523 width=5) (actual time=48.376..870.716 rows=11,521,822 loops=1)

  • Sort Key: ((lc.id)::text)
  • Sort Method: quicksort Memory: 1640kB
42. 12.769 12.769 ↑ 1.1 18,584 1

Seq Scan on live_channel lc (cost=0.00..3,560.23 rows=19,523 width=5) (actual time=0.014..12.769 rows=18,584 loops=1)