explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EG4L

Settings
# exclusive inclusive rows x rows loops node
1. 2.537 5,034.823 ↑ 12.6 25,469 1

Append (cost=137,790.08..428,674.20 rows=321,603 width=104) (actual time=3,700.945..5,034.823 rows=25,469 loops=1)

2.          

CTE playweek_bookings

3. 60.233 647.698 ↑ 1.5 238,053 1

Nested Loop (cost=93.25..12,834.93 rows=350,348 width=44) (actual time=2.787..647.698 rows=238,053 loops=1)

4. 5.309 5.894 ↓ 16.2 3,249 1

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

  • Group Key: th.theater_id
5. 0.585 0.585 ↓ 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.011..0.585 rows=3,249 loops=1)

6. 29.241 581.571 ↑ 1.3 73 3,249

Append (cost=0.42..62.75 rows=95 width=44) (actual time=0.042..0.179 rows=73 loops=3,249)

7. 552.330 552.330 ↑ 1.3 73 3,249

Index Scan using ix_bookings_20180101_theater_1 on bookings_20180101 bo_3 (cost=0.42..62.75 rows=95 width=44) (actual time=0.041..0.170 rows=73 loops=3,249)

  • Index Cond: (theater_id = th.theater_id)
  • Filter: ((booking_date <= '2018-01-21'::date) AND (booking_date >= ('2018-01-15'::date - ('14 DAYS'::cstring)::interval)))
  • Rows Removed by Filter: 35
8. 199.905 3,930.075 ↑ 4.2 25,260 1

GroupAggregate (cost=124,955.15..132,995.23 rows=107,201 width=104) (actual time=3,700.944..3,930.075 rows=25,260 loops=1)

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

Sort (cost=124,955.15..125,223.16 rows=107,201 width=82) (actual time=3,700.885..3,730.170 rows=113,990 loops=1)

  • Sort Key: bo.theater_id, re.title_id, bo.booking_date
  • Sort Method: external merge Disk: 9000kB
10. 75.059 3,464.094 ↓ 1.1 113,990 1

Nested Loop Left Join (cost=22,358.31..110,867.53 rows=107,201 width=82) (actual time=494.916..3,464.094 rows=113,990 loops=1)

11. 17.385 1,634.575 ↓ 33.4 29,241 1

Nested Loop Left Join (cost=22,357.75..34,680.93 rows=876 width=56) (actual time=494.449..1,634.575 rows=29,241 loops=1)

12. 120.386 1,470.985 ↓ 33.4 29,241 1

Hash Join (cost=22,357.33..30,721.89 rows=876 width=32) (actual time=494.393..1,470.985 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. 859.094 859.094 ↑ 1.5 238,053 1

CTE Scan on playweek_bookings bo (cost=0.00..7,006.96 rows=350,348 width=28) (actual time=2.790..859.094 rows=238,053 loops=1)

14. 2.686 491.505 ↓ 1.3 4,390 1

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

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

Hash Left Join (cost=1.25..22,313.60 rows=3,498 width=20) (actual time=0.074..488.819 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. 156.697 156.697 ↑ 1.0 699,564 1

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

17. 0.010 0.025 ↑ 1.0 10 1

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

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

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

19. 146.205 146.205 ↓ 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.005..0.005 rows=0 loops=29,241)

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

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

  • Index Cond: (bo.id = booking_id)
21. 0.912 561.510 ↑ 520.4 206 1

GroupAggregate (cost=129,478.98..137,519.06 rows=107,201 width=104) (actual time=560.575..561.510 rows=206 loops=1)

  • Group Key: bo_1.theater_id, re_1.title_id, bo_1.booking_date
22. 0.763 560.598 ↑ 238.2 450 1

Sort (cost=129,478.98..129,746.99 rows=107,201 width=82) (actual time=560.555..560.598 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.409 559.835 ↑ 238.2 450 1

Nested Loop Left Join (cost=22,358.74..115,391.36 rows=107,201 width=82) (actual time=485.084..559.835 rows=450 loops=1)

24. 0.277 554.070 ↑ 4.3 206 1

Nested Loop Left Join (cost=22,358.17..38,734.29 rows=876 width=56) (actual time=484.835..554.070 rows=206 loops=1)

25. 0.242 552.969 ↑ 4.3 206 1

Nested Loop (cost=22,357.75..34,739.25 rows=876 width=32) (actual time=484.813..552.969 rows=206 loops=1)

26. 21.292 548.401 ↑ 4.3 206 1

Hash Join (cost=22,357.33..30,721.89 rows=876 width=40) (actual time=483.541..548.401 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. 44.754 44.754 ↑ 1.5 238,053 1

CTE Scan on playweek_bookings bo_1 (cost=0.00..7,006.96 rows=350,348 width=36) (actual time=0.027..44.754 rows=238,053 loops=1)

28. 2.303 482.355 ↓ 1.3 4,390 1

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

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

Hash Left Join (cost=1.25..22,313.60 rows=3,498 width=20) (actual time=0.084..480.052 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. 155.170 155.170 ↑ 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.027..155.170 rows=699,564 loops=1)

31. 0.010 0.028 ↑ 1.0 10 1

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

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

Seq Scan on distributors_overrides dos_1 (cost=0.00..1.10 rows=10 width=72) (actual time=0.016..0.018 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. 0.824 0.824 ↓ 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.004..0.004 rows=0 loops=206)

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

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

  • Index Cond: (bo_1.id = booking_id)
36. 0.055 540.701 ↑ 35,733.7 3 1

GroupAggregate (cost=134,068.89..142,108.96 rows=107,201 width=104) (actual time=540.689..540.701 rows=3 loops=1)

  • Group Key: bo_2.theater_id, re_2.title_id, bo_2.booking_date
37. 0.025 540.646 ↑ 15,314.4 7 1

Sort (cost=134,068.89..134,336.89 rows=107,201 width=82) (actual time=540.645..540.646 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.009 540.621 ↑ 15,314.4 7 1

Nested Loop Left Join (cost=22,359.16..119,981.27 rows=107,201 width=82) (actual time=505.321..540.621 rows=7 loops=1)

39. 0.006 540.579 ↑ 292.0 3 1

Nested Loop Left Join (cost=22,358.60..42,862.22 rows=876 width=56) (actual time=505.298..540.579 rows=3 loops=1)

40. 0.004 540.543 ↑ 292.0 3 1

Nested Loop (cost=22,358.18..38,828.16 rows=876 width=32) (actual time=505.271..540.543 rows=3 loops=1)

41. 0.009 539.213 ↑ 292.0 3 1

Nested Loop (cost=22,357.75..34,775.02 rows=876 width=40) (actual time=503.953..539.213 rows=3 loops=1)

42. 20.389 539.168 ↑ 292.0 3 1

Hash Join (cost=22,357.33..30,721.89 rows=876 width=48) (actual time=503.925..539.168 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. 43.873 43.873 ↑ 1.5 238,053 1

CTE Scan on playweek_bookings bo_2 (cost=0.00..7,006.96 rows=350,348 width=44) (actual time=0.009..43.873 rows=238,053 loops=1)

44. 2.359 474.906 ↓ 1.3 4,390 1

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

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

Hash Left Join (cost=1.25..22,313.60 rows=3,498 width=20) (actual time=0.068..472.547 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. 149.835 149.835 ↑ 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.024..149.835 rows=699,564 loops=1)

47. 0.008 0.017 ↑ 1.0 10 1

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

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

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

49. 0.036 0.036 ↑ 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.012..0.012 rows=1 loops=3)

  • Index Cond: (id = bo_2.release_id)
  • Heap Fetches: 3
50. 1.326 1.326 ↑ 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.442..0.442 rows=1 loops=3)

  • Index Cond: (id = bo_2.secondary_release_id)
  • Heap Fetches: 3
51. 0.030 0.030 ↓ 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.010..0.010 rows=0 loops=3)

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

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

  • Index Cond: (bo_2.id = booking_id)
Planning time : 37.251 ms
Execution time : 5,088.373 ms