explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UWts

Settings
# exclusive inclusive rows x rows loops node
1. 9,006.075 146,363.761 ↑ 2,296.5 416 1

GroupAggregate (cost=4,192,355.40..4,213,850.43 rows=955,335 width=19) (actual time=132,361.639..146,363.761 rows=416 loops=1)

  • Group Key: (date_trunc('month'::text, deals.created_at)), countries.iso
2. 24,403.691 137,357.686 ↓ 29.3 27,983,250 1

Sort (cost=4,192,355.40..4,194,743.73 rows=955,335 width=15) (actual time=132,359.046..137,357.686 rows=27,983,250 loops=1)

  • Sort Key: (date_trunc('month'::text, deals.created_at)), countries.iso
  • Sort Method: external merge Disk: 712000kB
3. 0.000 112,953.995 ↓ 29.3 27,983,250 1

Gather (cost=1,018.60..4,087,900.41 rows=955,335 width=15) (actual time=5.213..112,953.995 rows=27,983,250 loops=1)

  • Workers Planned: 5
  • Workers Launched: 4
4. 2,597.089 115,780.580 ↓ 29.3 5,596,650 5

Hash Join (cost=18.60..3,989,456.24 rows=191,067 width=15) (actual time=7.607..115,780.580 rows=5,596,650 loops=5)

  • Hash Cond: (users.registration_country_id = countries.id)
5. 17,629.733 113,183.267 ↓ 29.3 5,596,650 5

Nested Loop (cost=1.02..3,986,555.60 rows=191,067 width=16) (actual time=7.207..113,183.267 rows=5,596,650 loops=5)

6. 95,553.531 95,553.531 ↓ 29.3 5,596,650 5

Parallel Index Scan using idx_deals_13032019 on deals (cost=0.58..3,656,536.22 rows=191,067 width=12) (actual time=7.107..95,553.531 rows=5,596,650 loops=5)

  • Index Cond: ((date_trunc('month'::text, created_at) >= date_trunc('month'::text, (to_date('01122018'::text, 'ddmmyyyy'::text))::timestamp with time zone)) AND (date_trunc('month'::text, created_at) < date_trunc('month'::text, (to_date('01042019'::text, 'ddmmyyyy'::text))::timestamp with time zone)))
7. 0.003 0.003 ↑ 1.0 1 27,983,250

Index Only Scan using idx_users_13032019_01 on users (cost=0.43..1.73 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=27,983,250)

  • Index Cond: (id = deals.user_id)
  • Heap Fetches: 3384831
8. 0.056 0.224 ↑ 1.0 248 5

Hash (cost=14.48..14.48 rows=248 width=7) (actual time=0.224..0.224 rows=248 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
9. 0.168 0.168 ↑ 1.0 248 5

Seq Scan on countries (cost=0.00..14.48 rows=248 width=7) (actual time=0.018..0.168 rows=248 loops=5)