explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x4P7

Settings
# exclusive inclusive rows x rows loops node
1. 2.520 4,705.987 ↑ 12.8 25,469 1

Append (cost=193,051.93..486,733.10 rows=325,275 width=104) (actual time=3,341.804..4,705.987 rows=25,469 loops=1)

2.          

CTE playweek_bookings

3. 265.357 1,125.059 ↑ 1.5 238,053 1

Hash Join (cost=97.90..66,932.29 rows=354,326 width=44) (actual time=9.123..1,125.059 rows=238,053 loops=1)

  • Hash Cond: (bo_3.theater_id = th.theater_id)
4. 855.641 855.641 ↑ 1.0 700,972 1

Index Scan using ix_bookings_booking_date_3 on bookings bo_3 (cost=0.57..61,032.87 rows=708,652 width=44) (actual time=1.493..855.641 rows=700,972 loops=1)

  • Index Cond: ((booking_date >= ('2018-01-15'::date - ('14 DAYS'::cstring)::interval)) AND (booking_date <= '2018-01-21'::date))
5. 0.946 4.061 ↓ 16.2 3,249 1

Hash (cost=94.83..94.83 rows=200 width=8) (actual time=4.061..4.061 rows=3,249 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 159kB
6. 2.312 3.115 ↓ 16.2 3,249 1

HashAggregate (cost=92.83..94.83 rows=200 width=8) (actual time=2.403..3.115 rows=3,249 loops=1)

  • Group Key: th.theater_id
7. 0.803 0.803 ↓ 2.9 3,249 1

Seq Scan on theaters_temp_table th (cost=0.00..90.06 rows=1,106 width=8) (actual time=0.019..0.803 rows=3,249 loops=1)

8. 201.600 3,590.787 ↑ 4.3 25,260 1

GroupAggregate (cost=126,119.64..134,251.52 rows=108,425 width=104) (actual time=3,341.802..3,590.787 rows=25,260 loops=1)

  • Group Key: bo.theater_id, re.title_id, bo.booking_date
9. 288.039 3,389.187 ↓ 1.1 113,990 1

Sort (cost=126,119.64..126,390.71 rows=108,425 width=82) (actual time=3,341.754..3,389.187 rows=113,990 loops=1)

  • Sort Key: bo.theater_id, re.title_id, bo.booking_date
  • Sort Method: external merge Disk: 8984kB
10. 77.081 3,101.148 ↓ 1.1 113,990 1

Nested Loop Left Join (cost=22,358.31..111,861.38 rows=108,425 width=82) (actual time=519.623..3,101.148 rows=113,990 loops=1)

11. 39.890 2,117.596 ↓ 33.0 29,241 1

Nested Loop Left Join (cost=22,357.75..34,821.04 rows=886 width=56) (actual time=519.584..2,117.596 rows=29,241 loops=1)

12. 137.254 1,960.742 ↓ 33.0 29,241 1

Hash Join (cost=22,357.33..30,816.87 rows=886 width=32) (actual time=519.553..1,960.742 rows=29,241 loops=1)

  • Hash Cond: (bo.release_id = re.id)
  • Join Filter: CASE WHEN (re.release_date IS NOT NULL) THEN ((re.release_date <= '2018-01-21'::date) AND (bo.booking_date >= (re.release_date - ('14 DAYS'::cstring)::interval))) ELSE (bo.booking_date >= '2018-01-15'::date) END
  • Rows Removed by Join Filter: 449
13. 1,341.462 1,341.462 ↑ 1.5 238,053 1

CTE Scan on playweek_bookings bo (cost=0.00..7,086.52 rows=354,326 width=28) (actual time=9.130..1,341.462 rows=238,053 loops=1)

14. 2.146 482.026 ↓ 1.3 4,390 1

Hash (cost=22,313.60..22,313.60 rows=3,498 width=20) (actual time=482.026..482.026 rows=4,390 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 304kB
15. 329.892 479.880 ↓ 1.3 4,390 1

Hash Left Join (cost=1.25..22,313.60 rows=3,498 width=20) (actual time=0.075..479.880 rows=4,390 loops=1)

  • Hash Cond: ((re.title_id = dos.title_id) AND ((re.country_id)::text = dos.country_id))
  • Filter: (COALESCE(dos.distributor_id, (re.distributor_id)::text) = 'BUENA2'::text)
  • Rows Removed by Filter: 695174
16. 149.969 149.969 ↑ 1.0 699,564 1

Seq Scan on releases re (cost=0.00..18,639.64 rows=699,564 width=31) (actual time=0.026..149.969 rows=699,564 loops=1)

17. 0.008 0.019 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=72) (actual time=0.019..0.019 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.011 0.011 ↑ 1.0 10 1

Seq Scan on distributors_overrides dos (cost=0.00..1.10 rows=10 width=72) (actual time=0.007..0.011 rows=10 loops=1)

19. 116.964 116.964 ↓ 0.0 0 29,241

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications fr (cost=0.42..4.52 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=29,241)

  • Index Cond: (booking_id = bo.id)
  • Filter: ((justification_id)::text <> ALL ('{I,ND}'::text[]))
20. 906.471 906.471 ↑ 30.5 4 29,241

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft (cost=0.56..85.73 rows=122 width=42) (actual time=0.025..0.031 rows=4 loops=29,241)

  • Index Cond: (bo.id = booking_id)
21. 0.965 562.641 ↑ 526.3 206 1

GroupAggregate (cost=130,695.20..138,827.08 rows=108,425 width=104) (actual time=561.662..562.641 rows=206 loops=1)

  • Group Key: bo_1.theater_id, re_1.title_id, bo_1.booking_date
22. 0.843 561.676 ↑ 240.9 450 1

Sort (cost=130,695.20..130,966.26 rows=108,425 width=82) (actual time=561.643..561.676 rows=450 loops=1)

  • Sort Key: bo_1.theater_id, re_1.title_id, bo_1.booking_date
  • Sort Method: quicksort Memory: 88kB
23. 0.456 560.833 ↑ 240.9 450 1

Nested Loop Left Join (cost=22,358.74..116,436.93 rows=108,425 width=82) (actual time=481.924..560.833 rows=450 loops=1)

24. 0.213 554.815 ↑ 4.3 206 1

Nested Loop Left Join (cost=22,358.17..38,920.42 rows=886 width=56) (actual time=481.886..554.815 rows=206 loops=1)

25. 0.416 553.572 ↑ 4.3 206 1

Nested Loop (cost=22,357.75..34,879.84 rows=886 width=32) (actual time=481.854..553.572 rows=206 loops=1)

26. 22.009 548.830 ↑ 4.3 206 1

Hash Join (cost=22,357.33..30,816.87 rows=886 width=40) (actual time=480.555..548.830 rows=206 loops=1)

  • Hash Cond: (bo_1.secondary_release_id = re_1.id)
  • Join Filter: CASE WHEN (re_1.release_date IS NOT NULL) THEN ((re_1.release_date <= '2018-01-21'::date) AND (bo_1.booking_date >= (re_1.release_date - ('14 DAYS'::cstring)::interval))) ELSE (bo_1.booking_date >= '2018-01-15'::date) END
27. 46.929 46.929 ↑ 1.5 238,053 1

CTE Scan on playweek_bookings bo_1 (cost=0.00..7,086.52 rows=354,326 width=36) (actual time=0.026..46.929 rows=238,053 loops=1)

28. 2.212 479.892 ↓ 1.3 4,390 1

Hash (cost=22,313.60..22,313.60 rows=3,498 width=20) (actual time=479.892..479.892 rows=4,390 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 304kB
29. 327.595 477.680 ↓ 1.3 4,390 1

Hash Left Join (cost=1.25..22,313.60 rows=3,498 width=20) (actual time=0.076..477.680 rows=4,390 loops=1)

  • Hash Cond: ((re_1.title_id = dos_1.title_id) AND ((re_1.country_id)::text = dos_1.country_id))
  • Filter: (COALESCE(dos_1.distributor_id, (re_1.distributor_id)::text) = 'BUENA2'::text)
  • Rows Removed by Filter: 695174
30. 150.060 150.060 ↑ 1.0 699,564 1

Seq Scan on releases re_1 (cost=0.00..18,639.64 rows=699,564 width=31) (actual time=0.022..150.060 rows=699,564 loops=1)

31. 0.008 0.025 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=72) (actual time=0.025..0.025 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.017 0.017 ↑ 1.0 10 1

Seq Scan on distributors_overrides dos_1 (cost=0.00..1.10 rows=10 width=72) (actual time=0.014..0.017 rows=10 loops=1)

33. 4.326 4.326 ↑ 1.0 1 206

Index Only Scan using pk_releases on releases primary_release (cost=0.42..4.59 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=206)

  • Index Cond: (id = bo_1.release_id)
  • Heap Fetches: 206
34. 1.030 1.030 ↓ 0.0 0 206

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications fr_1 (cost=0.42..4.56 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=206)

  • Index Cond: (booking_id = bo_1.id)
  • Filter: ((justification_id)::text <> ALL ('{I,ND}'::text[]))
35. 5.562 5.562 ↑ 61.0 2 206

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_1 (cost=0.56..86.27 rows=122 width=42) (actual time=0.025..0.027 rows=2 loops=206)

  • Index Cond: (bo_1.id = booking_id)
36. 0.044 550.039 ↑ 36,141.7 3 1

GroupAggregate (cost=135,337.60..143,469.47 rows=108,425 width=104) (actual time=550.027..550.039 rows=3 loops=1)

  • Group Key: bo_2.theater_id, re_2.title_id, bo_2.booking_date
37. 0.041 549.995 ↑ 15,489.3 7 1

Sort (cost=135,337.60..135,608.66 rows=108,425 width=82) (actual time=549.995..549.995 rows=7 loops=1)

  • Sort Key: bo_2.theater_id, re_2.title_id, bo_2.booking_date
  • Sort Method: quicksort Memory: 25kB
38. 0.008 549.954 ↑ 15,489.3 7 1

Nested Loop Left Join (cost=22,359.16..121,079.33 rows=108,425 width=82) (actual time=498.832..549.954 rows=7 loops=1)

39. 0.010 549.910 ↑ 295.3 3 1

Nested Loop Left Join (cost=22,358.60..43,095.25 rows=886 width=56) (actual time=498.809..549.910 rows=3 loops=1)

40. 0.007 549.852 ↑ 295.3 3 1

Nested Loop (cost=22,358.18..39,015.21 rows=886 width=32) (actual time=498.770..549.852 rows=3 loops=1)

41. 0.017 548.543 ↑ 295.3 3 1

Nested Loop (cost=22,357.75..34,916.04 rows=886 width=40) (actual time=497.476..548.543 rows=3 loops=1)

42. 20.252 548.478 ↑ 295.3 3 1

Hash Join (cost=22,357.33..30,816.87 rows=886 width=48) (actual time=497.448..548.478 rows=3 loops=1)

  • Hash Cond: (bo_2.tertiary_release_id = re_2.id)
  • Join Filter: CASE WHEN (re_2.release_date IS NOT NULL) THEN ((re_2.release_date <= '2018-01-21'::date) AND (bo_2.booking_date >= (re_2.release_date - ('14 DAYS'::cstring)::interval))) ELSE (bo_2.booking_date >= '2018-01-15'::date) END
43. 44.629 44.629 ↑ 1.5 238,053 1

CTE Scan on playweek_bookings bo_2 (cost=0.00..7,086.52 rows=354,326 width=44) (actual time=0.009..44.629 rows=238,053 loops=1)

44. 2.387 483.597 ↓ 1.3 4,390 1

Hash (cost=22,313.60..22,313.60 rows=3,498 width=20) (actual time=483.597..483.597 rows=4,390 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 304kB
45. 329.518 481.210 ↓ 1.3 4,390 1

Hash Left Join (cost=1.25..22,313.60 rows=3,498 width=20) (actual time=0.083..481.210 rows=4,390 loops=1)

  • Hash Cond: ((re_2.title_id = dos_2.title_id) AND ((re_2.country_id)::text = dos_2.country_id))
  • Filter: (COALESCE(dos_2.distributor_id, (re_2.distributor_id)::text) = 'BUENA2'::text)
  • Rows Removed by Filter: 695174
46. 151.668 151.668 ↑ 1.0 699,564 1

Seq Scan on releases re_2 (cost=0.00..18,639.64 rows=699,564 width=31) (actual time=0.021..151.668 rows=699,564 loops=1)

47. 0.014 0.024 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=72) (actual time=0.024..0.024 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 0.010 0.010 ↑ 1.0 10 1

Seq Scan on distributors_overrides dos_2 (cost=0.00..1.10 rows=10 width=72) (actual time=0.008..0.010 rows=10 loops=1)

49. 0.048 0.048 ↑ 1.0 1 3

Index Only Scan using pk_releases on releases primary_release_1 (cost=0.42..4.63 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=3)

  • Index Cond: (id = bo_2.release_id)
  • Heap Fetches: 3
50. 1.302 1.302 ↑ 1.0 1 3

Index Only Scan using pk_releases on releases secondary_release (cost=0.42..4.63 rows=1 width=8) (actual time=0.434..0.434 rows=1 loops=3)

  • Index Cond: (id = bo_2.secondary_release_id)
  • Heap Fetches: 3
51. 0.048 0.048 ↓ 0.0 0 3

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications fr_2 (cost=0.42..4.61 rows=1 width=32) (actual time=0.016..0.016 rows=0 loops=3)

  • Index Cond: (booking_id = bo_2.id)
  • Filter: ((justification_id)::text <> ALL ('{I,ND}'::text[]))
52. 0.036 0.036 ↑ 61.0 2 3

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_2 (cost=0.56..86.80 rows=122 width=42) (actual time=0.010..0.012 rows=2 loops=3)

  • Index Cond: (bo_2.id = booking_id)
Planning time : 32.027 ms
Execution time : 4,779.284 ms