explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gwqf : pg11

Settings
# exclusive inclusive rows x rows loops node
1. 1,488.621 285,514.128 ↑ 1.0 97,497 1

GroupAggregate (cost=219,332.68..321,041.90 rows=98,261 width=152) (actual time=283,983.166..285,514.128 rows=97,497 loops=1)

  • Group Key: t.title_no
2. 299.485 284,025.507 ↓ 2.6 468,244 1

Sort (cost=219,332.68..219,779.96 rows=178,913 width=67) (actual time=283,982.868..284,025.507 rows=468,244 loops=1)

  • Sort Key: t.title_no
  • Sort Method: quicksort Memory: 73255kB
3. 231.453 283,726.022 ↓ 2.6 468,244 1

Hash Left Join (cost=200,763.20..203,723.50 rows=178,913 width=67) (actual time=1,617.253..283,726.022 rows=468,244 loops=1)

  • Hash Cond: ((s.country_id)::text = (subscription_countries.country_id)::text)
4. 281,602.252 283,494.477 ↓ 3.1 468,244 1

Hash Left Join (cost=200,572.80..203,110.85 rows=151,979 width=64) (actual time=1,617.130..283,494.477 rows=468,244 loops=1)

  • Hash Cond: (COALESCE(t.currency_type_no, 1) = from_ex.currency_type_no)
  • Join Filter: (s.earliest_release_date_of_title = from_ex.day)
  • Rows Removed by Join Filter: 4557718703
5. 184.602 1,553.624 ↓ 3.1 468,244 1

Merge Left Join (cost=156,874.48..159,013.59 rows=151,979 width=62) (actual time=1,272.594..1,553.624 rows=468,244 loops=1)

  • Merge Cond: (s.earliest_release_date_of_title = to_ex.day)
6. 234.786 1,359.574 ↓ 3.1 468,244 1

Sort (cost=142,753.65..143,133.60 rows=151,979 width=52) (actual time=1,265.200..1,359.574 rows=468,244 loops=1)

  • Sort Key: s.earliest_release_date_of_title
  • Sort Method: quicksort Memory: 71443kB
7. 91.573 1,124.788 ↓ 3.1 468,244 1

Hash Join (cost=127,261.55..129,673.19 rows=151,979 width=52) (actual time=886.227..1,124.788 rows=468,244 loops=1)

  • Hash Cond: (s.title_no = t.title_no)
8. 92.975 931.693 ↓ 3.1 468,244 1

Merge Left Join (cost=120,525.67..122,538.35 rows=151,979 width=44) (actual time=783.951..931.693 rows=468,244 loops=1)

  • Merge Cond: (s.exchange_rate_date = ex.day)
9. 177.375 828.603 ↓ 3.1 468,244 1

Sort (cost=106,404.84..106,784.79 rows=151,979 width=34) (actual time=774.822..828.603 rows=468,244 loops=1)

  • Sort Key: s.exchange_rate_date
  • Sort Method: quicksort Memory: 52782kB
10. 144.056 651.228 ↓ 3.1 468,244 1

Hash Join (cost=9.53..93,324.38 rows=151,979 width=34) (actual time=0.154..651.228 rows=468,244 loops=1)

  • Hash Cond: ((s.country_id)::text = (c.country_id)::text)
11. 507.053 507.053 ↓ 1.3 496,062 1

Seq Scan on rs_releases_summary s (cost=0.00..92,261.18 rows=392,063 width=34) (actual time=0.016..507.053 rows=496,062 loops=1)

  • Filter: ((language_no IS NULL) AND ((language_format_id)::text = 'DEFAULT'::text) AND ((visual_format_id)::text = 'DEFAULT'::text))
  • Rows Removed by Filter: 250055
12. 0.024 0.119 ↓ 1.6 107 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
13. 0.095 0.095 ↓ 1.6 107 1

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

  • Filter: (((is_supported)::text = 'Y'::text) AND ((is_reporting_srg)::text = 'Y'::text))
  • Rows Removed by Filter: 71
14. 4.287 10.115 ↓ 1.1 17,393 1

Sort (cost=14,120.83..14,160.52 rows=15,876 width=18) (actual time=9.123..10.115 rows=17,393 loops=1)

  • Sort Key: ex.day
  • Sort Method: quicksort Memory: 1584kB
15. 4.877 5.828 ↓ 1.1 17,393 1

Bitmap Heap Scan on exchange_rates_by_day ex (cost=587.47..13,013.12 rows=15,876 width=18) (actual time=1.073..5.828 rows=17,393 loops=1)

  • Recheck Cond: (currency_type_no = 1)
  • Heap Blocks: exact=1364
16. 0.951 0.951 ↓ 1.1 17,393 1

Bitmap Index Scan on exchange_rates_by_day_pkey (cost=0.00..583.50 rows=15,876 width=0) (actual time=0.951..0.951 rows=17,393 loops=1)

  • Index Cond: (currency_type_no = 1)
17. 24.238 101.522 ↑ 1.0 98,261 1

Hash (cost=5,507.61..5,507.61 rows=98,261 width=12) (actual time=101.522..101.522 rows=98,261 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4830kB
18. 77.284 77.284 ↑ 1.0 98,261 1

Seq Scan on ti_titles t (cost=0.00..5,507.61 rows=98,261 width=12) (actual time=0.023..77.284 rows=98,261 loops=1)

19. 5.391 9.448 ↓ 1.1 17,393 1

Sort (cost=14,120.83..14,160.52 rows=15,876 width=18) (actual time=7.373..9.448 rows=17,393 loops=1)

  • Sort Key: to_ex.day
  • Sort Method: quicksort Memory: 1584kB
20. 3.283 4.057 ↓ 1.1 17,393 1

Bitmap Heap Scan on exchange_rates_by_day to_ex (cost=587.47..13,013.12 rows=15,876 width=18) (actual time=0.892..4.057 rows=17,393 loops=1)

  • Recheck Cond: (currency_type_no = 1)
  • Heap Blocks: exact=1364
21. 0.774 0.774 ↓ 1.1 17,393 1

Bitmap Index Scan on exchange_rates_by_day_pkey (cost=0.00..583.50 rows=15,876 width=0) (actual time=0.774..0.774 rows=17,393 loops=1)

  • Index Cond: (currency_type_no = 1)
22. 180.824 338.601 ↑ 1.0 1,426,014 1

Hash (cost=25,873.14..25,873.14 rows=1,426,014 width=22) (actual time=338.601..338.601 rows=1,426,014 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 98372kB
23. 157.777 157.777 ↑ 1.0 1,426,014 1

Seq Scan on exchange_rates_by_day from_ex (cost=0.00..25,873.14 rows=1,426,014 width=22) (actual time=0.009..157.777 rows=1,426,014 loops=1)

24. 0.016 0.092 ↓ 1.2 110 1

Hash (cost=189.22..189.22 rows=95 width=3) (actual time=0.092..0.092 rows=110 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
25. 0.043 0.076 ↓ 1.2 110 1

Bitmap Heap Scan on subscription_countries (cost=5.14..189.22 rows=95 width=3) (actual time=0.045..0.076 rows=110 loops=1)

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

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

  • Index Cond: (subscription_no = 11279)