explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pRwD : pg94

Settings
# exclusive inclusive rows x rows loops node
1. 1,811.076 4,429.536 ↑ 1.0 97,016 1

GroupAggregate (cost=315,041.09..412,417.30 rows=97,781 width=67) (actual time=2,583.222..4,429.536 rows=97,016 loops=1)

  • Group Key: t.title_no
2. 177.053 2,618.460 ↓ 2.7 465,548 1

Sort (cost=315,041.09..315,468.99 rows=171,158 width=67) (actual time=2,582.901..2,618.460 rows=465,548 loops=1)

  • Sort Key: t.title_no
  • Sort Method: quicksort Memory: 72941kB
3. 105.075 2,441.407 ↓ 2.7 465,548 1

Merge Right Join (cost=186,297.39..300,163.21 rows=171,158 width=67) (actual time=2,292.772..2,441.407 rows=465,548 loops=1)

  • Merge Cond: ((from_ex.currency_type_no = (COALESCE(t.currency_type_no, 1))) AND (from_ex.day = s.earliest_release_date_of_title))
4. 5.053 5.053 ↑ 82.0 17,373 1

Index Scan using exchange_rates_by_day_pkey on exchange_rates_by_day from_ex (cost=0.43..104,801.16 rows=1,423,998 width=22) (actual time=0.038..5.053 rows=17,373 loops=1)

5. 557.778 2,331.279 ↓ 2.7 465,548 1

Sort (cost=186,296.97..186,724.86 rows=171,158 width=65) (actual time=2,292.723..2,331.279 rows=465,548 loops=1)

  • Sort Key: (COALESCE(t.currency_type_no, 1)), s.earliest_release_date_of_title
  • Sort Method: quicksort Memory: 76111kB
6. 184.024 1,773.501 ↓ 2.7 465,548 1

Hash Join (cost=162,208.02..171,419.08 rows=171,158 width=65) (actual time=1,428.346..1,773.501 rows=465,548 loops=1)

  • Hash Cond: (s.title_no = t.title_no)
7. 161.223 1,506.705 ↓ 2.7 465,548 1

Hash Right Join (cost=155,458.95..161,246.85 rows=171,158 width=57) (actual time=1,345.475..1,506.705 rows=465,548 loops=1)

  • Hash Cond: ((subscription_countries.country_id)::text = (s.country_id)::text)
8. 0.105 0.138 ↓ 1.1 110 1

Bitmap Heap Scan on subscription_countries (cost=5.14..190.34 rows=96 width=3) (actual time=0.045..0.138 rows=110 loops=1)

  • Recheck Cond: (subscription_no = 11279)
  • Filter: can_access_srg
  • Heap Blocks: exact=8
9. 0.033 0.033 ↑ 1.0 110 1

Bitmap Index Scan on subscription_countries_pkey (cost=0.00..5.12 rows=111 width=0) (actual time=0.033..0.033 rows=110 loops=1)

  • Index Cond: (subscription_no = 11279)
10. 100.491 1,345.344 ↓ 3.1 465,548 1

Hash (cost=153,566.88..153,566.88 rows=150,954 width=54) (actual time=1,345.344..1,345.344 rows=465,548 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 38572kB
11. 125.210 1,244.853 ↓ 3.1 465,548 1

Merge Left Join (cost=150,607.14..153,566.88 rows=150,954 width=54) (actual time=1,063.817..1,244.853 rows=465,548 loops=1)

  • Merge Cond: (s.earliest_release_date_of_title = to_ex.day)
12. 161.394 1,096.373 ↓ 3.1 465,548 1

Sort (cost=136,313.60..136,690.98 rows=150,954 width=44) (actual time=1,056.009..1,096.373 rows=465,548 loops=1)

  • Sort Key: s.earliest_release_date_of_title
  • Sort Method: quicksort Memory: 63892kB
13. 87.891 934.979 ↓ 3.1 465,548 1

Merge Right Join (cost=121,150.20..123,328.72 rows=150,954 width=44) (actual time=797.205..934.979 rows=465,548 loops=1)

  • Merge Cond: (ex.day = s.exchange_rate_date)
14. 6.171 16.554 ↑ 1.0 17,372 1

Sort (cost=14,293.54..14,338.40 rows=17,942 width=18) (actual time=15.463..16.554 rows=17,372 loops=1)

  • Sort Key: ex.day
  • Sort Method: quicksort Memory: 1583kB
15. 10.383 10.383 ↑ 1.0 17,372 1

Bitmap Heap Scan on exchange_rates_by_day ex (cost=663.48..13,025.85 rows=17,942 width=18) (actual time=2.556..10.383 rows=17,372 loops=1)

  • Recheck Cond: (currency_type_no = 1)
  • Heap Blocks: exact=1347
  • -> Bitmap Index Scan on exchange_rates_by_day_pkey (cost=0.00..658.99 rows=17942 width=0) (actual time=2.342..2.342 rows=17372 loops
  • Index Cond: (currency_type_no = 1)
16. 170.906 830.534 ↓ 3.1 465,548 1

Sort (cost=106,856.66..107,234.04 rows=150,954 width=34) (actual time=781.737..830.534 rows=465,548 loops=1)

  • Sort Key: s.exchange_rate_date
  • Sort Method: quicksort Memory: 52555kB
17. 148.481 659.628 ↓ 3.1 465,548 1

Hash Join (cost=9.53..93,871.79 rows=150,954 width=34) (actual time=0.117..659.628 rows=465,548 loops=1)

  • Hash Cond: ((s.country_id)::text = (c.country_id)::text)
18. 511.059 511.059 ↓ 1.3 493,181 1

Seq Scan on rs_releases_summary s (cost=0.00..90,892.40 rows=389,418 width=34) (actual time=0.020..511.059 rows=493,181 loops=1)

  • Filter: ((language_no IS NULL) AND ((language_format_id)::text = 'DEFAULT'::text) AND ((visual_format_id)::text = 'DEFAULT'::tex
  • Rows Removed by Filter: 247547
19. 0.024 0.088 ↓ 1.6 107 1

Hash (cost=8.67..8.67 rows=69 width=3) (actual time=0.088..0.088 rows=107 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
20. 0.064 0.064 ↓ 1.6 107 1

Seq Scan on ti_countries c (cost=0.00..8.67 rows=69 width=3) (actual time=0.008..0.064 rows=107 loops=1)

  • Filter: (((is_supported)::text = 'Y'::text) AND ((is_reporting_srg)::text = 'Y'::text))
  • Rows Removed by Filter: 71
21. 18.674 23.270 ↓ 25.1 451,213 1

Sort (cost=14,293.54..14,338.40 rows=17,942 width=18) (actual time=7.802..23.270 rows=451,213 loops=1)

  • Sort Key: to_ex.day
  • Sort Method: quicksort Memory: 1583kB
22. 3.537 4.596 ↑ 1.0 17,372 1

Bitmap Heap Scan on exchange_rates_by_day to_ex (cost=663.48..13,025.85 rows=17,942 width=18) (actual time=1.188..4.596 rows=17,372 loops=1)

  • Recheck Cond: (currency_type_no = 1)
  • Heap Blocks: exact=1347
23. 1.059 1.059 ↑ 1.0 17,372 1

Bitmap Index Scan on exchange_rates_by_day_pkey (cost=0.00..658.99 rows=17,942 width=0) (actual time=1.059..1.059 rows=17,372 loops=1)

  • Index Cond: (currency_type_no = 1)
24. 25.980 82.772 ↑ 1.0 97,781 1

Hash (cost=5,526.81..5,526.81 rows=97,781 width=12) (actual time=82.772..82.772 rows=97,781 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 3789kB
25. 56.792 56.792 ↑ 1.0 97,781 1

Seq Scan on ti_titles t (cost=0.00..5,526.81 rows=97,781 width=12) (actual time=0.019..56.792 rows=97,781 loops=1)

Planning time : 6.982 ms
Execution time : 4,440.514 ms