explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Hxk

Settings
# exclusive inclusive rows x rows loops node
1. 915.980 25,170.855 ↑ 3.1 2,832,388 1

Merge Left Join (cost=1,513,742.45..1,911,926.39 rows=8,920,844 width=136) (actual time=23,020.888..25,170.855 rows=2,832,388 loops=1)

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

CTE vod_publication

3. 260.209 2,795.131 ↓ 1.0 363,353 1

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

4. 2,231.886 2,534.922 ↓ 1.0 363,353 1

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

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

6. 5,457.895 24,115.273 ↑ 1.2 2,832,388 1

Sort (cost=1,334,788.92..1,342,982.74 rows=3,277,525 width=136) (actual time=22,980.354..24,115.273 rows=2,832,388 loops=1)

  • Sort Key: l.media_id
  • Sort Method: external merge Disk: 424832kB
7. 716.814 18,657.378 ↑ 1.2 2,832,388 1

Hash Left Join (cost=686,516.93..756,039.80 rows=3,277,525 width=136) (actual time=14,238.579..18,657.378 rows=2,832,388 loops=1)

  • Hash Cond: (l.id = fl.id)
8. 957.506 17,877.931 ↑ 1.2 2,832,388 1

Merge Right Join (cost=678,546.00..733,527.10 rows=3,277,525 width=136) (actual time=14,175.219..17,877.931 rows=2,832,388 loops=1)

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

Sort (cost=42,300.64..43,096.15 rows=318,205 width=14) (actual time=1,857.533..1,905.384 rows=307,335 loops=1)

  • Sort Key: np.possession_id
  • Sort Method: quicksort Memory: 27209kB
10. 212.841 262.383 ↓ 1.0 318,295 1

Hash Left Join (cost=25.25..13,217.35 rows=318,205 width=14) (actual time=0.341..262.383 rows=318,295 loops=1)

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

Seq Scan on new_payments np (cost=0.00..10,676.05 rows=318,205 width=38) (actual time=0.004..49.221 rows=318,295 loops=1)

12. 0.208 0.321 ↑ 1.0 100 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
13. 0.113 0.113 ↑ 1.0 100 1

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

14. 242.612 15,015.041 ↓ 1.7 2,832,388 1

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

15. 9,990.955 14,772.429 ↑ 1.0 1,693,908 1

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

  • Sort Key: l.possession_id
  • Sort Method: external merge Disk: 253064kB
16. 517.422 4,781.474 ↑ 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,739.025..4,781.474 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
17. 532.997 686.073 ↑ 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=160.335..686.073 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
18. 153.076 153.076 ↑ 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=153.076..153.076 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))
19. 90.365 3,577.979 ↑ 1.0 363,002 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 26977kB
20. 196.730 3,487.614 ↓ 1.0 363,353 1

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

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

22. 81.171 327.104 ↓ 1.0 363,353 1

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

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

24. 34.256 62.633 ↑ 1.0 296,041 1

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

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

26. 132.387 139.602 ↓ 72.1 1,406,955 1

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

  • Sort Key: ((live.id)::text)
  • Sort Method: quicksort Memory: 1640kB
27. 7.215 7.215 ↑ 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.042..7.215 rows=18,585 loops=1)

  • Heap Fetches: 4147
Planning time : 2.257 ms