explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qReN

Settings
# exclusive inclusive rows x rows loops node
1. 1,881.918 27,636.885 ↑ 41.3 4,641,037 1

Merge Left Join (cost=2,076,487.87..5,130,302.70 rows=191,715,079 width=136) (actual time=22,112.432..27,636.885 rows=4,641,037 loops=1)

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

CTE vod_publication

3. 262.448 2,744.112 ↓ 1.0 363,353 1

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

4. 2,178.624 2,481.664 ↓ 1.0 363,353 1

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

  • Sort Key: vv.distributor, vv.title
  • Sort Method: quicksort Memory: 54964kB
5. 303.040 303.040 ↓ 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.004..303.040 rows=363,353 loops=1)

6. 968.262 22,833.195 ↑ 3.1 2,832,388 1

Merge Left Join (cost=1,739,070.66..1,894,857.20 rows=8,920,844 width=136) (actual time=20,003.390..22,833.195 rows=2,832,388 loops=1)

  • Merge Cond: (l.possession_id = np.possession_id)
7. 10,077.255 19,809.780 ↑ 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,152.631..19,809.780 rows=1,693,908 loops=1)

  • Sort Key: l.possession_id
  • Sort Method: external merge Disk: 253056kB
8. 571.081 9,732.525 ↑ 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,305.315..9,732.525 rows=1,693,908 loops=1)

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

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

  • Sort Key: l.media_id
  • Sort Method: external merge Disk: 253064kB
10. 535.390 5,372.049 ↑ 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,758.662..5,372.049 rows=1,693,908 loops=1)

  • Hash Cond: (l.id = fl.id)
11. 536.022 4,773.532 ↑ 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,690.014..4,773.532 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. 556.512 711.568 ↑ 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.490..711.568 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.056 155.056 ↑ 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.056..155.056 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.522 3,525.942 ↑ 1.0 363,002 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 26977kB
15. 193.809 3,434.420 ↓ 1.0 363,353 1

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

  • Hash Cond: (vp.id = vod.id)
16. 2,910.865 2,910.865 ↓ 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,414.930..2,910.865 rows=363,353 loops=1)

17. 83.247 329.746 ↓ 1.0 363,353 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 29468kB
18. 246.499 246.499 ↓ 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.007..246.499 rows=363,353 loops=1)

19. 38.618 63.127 ↑ 1.0 296,041 1

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

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

21. 95.973 103.070 ↓ 44.6 871,164 1

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

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

  • Heap Fetches: 4172
23. 1,810.673 2,055.153 ↓ 7.9 2,512,350 1

Sort (cost=42,300.64..43,096.15 rows=318,205 width=14) (actual time=1,836.456..2,055.153 rows=2,512,350 loops=1)

  • Sort Key: np.possession_id
  • Sort Method: quicksort Memory: 27210kB
24. 205.886 244.480 ↓ 1.0 318,334 1

Hash Left Join (cost=25.25..13,217.35 rows=318,205 width=14) (actual time=0.201..244.480 rows=318,334 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. 38.444 38.444 ↓ 1.0 318,334 1

Seq Scan on new_payments np (cost=0.00..10,676.05 rows=318,205 width=38) (actual time=0.021..38.444 rows=318,334 loops=1)

26. 0.078 0.150 ↑ 1.0 100 1

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

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

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

28. 2,500.764 2,921.772 ↓ 5.4 6,914,089 1

Sort (cost=162,522.66..165,726.19 rows=1,281,410 width=32) (actual time=2,109.026..2,921.772 rows=6,914,089 loops=1)

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

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

Planning time : 2.443 ms