explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9HEQ : Optimization for: plan #RYau

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5,143.123 53,629.992 ↑ 1.0 1 1

Aggregate (cost=4,211,686.71..4,211,686.72 rows=1 width=58) (actual time=53,629.992..53,629.992 rows=1 loops=1)

2. 4,476.438 48,486.869 ↓ 10.5 3,227,145 1

Hash Join (cost=4,118,107.99..4,204,772.55 rows=307,295 width=58) (actual time=31,806.669..48,486.869 rows=3,227,145 loops=1)

  • Hash Cond: (ssd.channel_id = uc.channel_id)
3. 2,164.547 43,957.708 ↓ 10.7 3,227,145 1

Merge Join (cost=4,115,317.10..4,174,384.45 rows=301,837 width=70) (actual time=31,753.859..43,957.708 rows=3,227,145 loops=1)

  • Merge Cond: (up.product_id = ssd.product_id)
4. 60.092 60.092 ↑ 1.0 92,904 1

Index Only Scan using users_products_pkey on users_products up (cost=0.43..3,593.59 rows=95,838 width=8) (actual time=0.060..60.092 rows=92,904 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
5. 2,758.976 41,733.069 ↓ 11.6 3,227,145 1

Materialize (cost=4,115,295.43..4,166,721.25 rows=278,663 width=74) (actual time=31,753.787..41,733.069 rows=3,227,145 loops=1)

6. 4,530.191 38,974.093 ↓ 11.6 3,227,145 1

Merge Join (cost=4,115,295.43..4,166,024.59 rows=278,663 width=74) (actual time=31,753.775..38,974.093 rows=3,227,145 loops=1)

  • Merge Cond: ((ssd.product_id = sspc.product_level_4_id) AND (ssd.channel_id = sspc.channel_level_4_id))
7. 10,493.009 27,475.694 ↓ 1.0 3,227,145 1

Sort (cost=3,639,589.87..3,647,504.04 rows=3,165,667 width=66) (actual time=26,272.227..27,475.694 rows=3,227,145 loops=1)

  • Sort Key: ssd.product_id, ssd.channel_id
  • Sort Method: quicksort Memory: 414315kB
8. 16,605.922 16,982.685 ↓ 1.0 3,227,145 1

Bitmap Heap Scan on sops_scenarios_details ssd (cost=67,208.66..3,297,791.57 rows=3,165,667 width=66) (actual time=388.495..16,982.685 rows=3,227,145 loops=1)

  • Recheck Cond: ((sop_id = 10) AND (date = '2019-07-01'::date))
  • Heap Blocks: exact=45097
9. 376.763 376.763 ↓ 1.0 3,227,145 1

Bitmap Index Scan on sops_scenarios_details_sop_id_date_idx1 (cost=0.00..66,417.24 rows=3,165,667 width=0) (actual time=376.763..376.763 rows=3,227,145 loops=1)

  • Index Cond: ((sop_id = 10) AND (date = '2019-07-01'::date))
10. 5,322.124 6,968.208 ↑ 1.0 3,227,145 1

Sort (cost=475,703.34..483,771.74 rows=3,227,358 width=8) (actual time=5,481.531..6,968.208 rows=3,227,145 loops=1)

  • Sort Key: sspc.product_level_4_id, sspc.channel_level_4_id
  • Sort Method: quicksort Memory: 249577kB
11. 1,646.084 1,646.084 ↑ 1.0 3,227,145 1

Index Only Scan using main_idx on sops_scenarios_products_channels_tree sspc (cost=0.56..126,794.93 rows=3,227,358 width=8) (actual time=0.127..1,646.084 rows=3,227,145 loops=1)

  • Heap Fetches: 0
12. 16.404 52.723 ↓ 1.0 40,006 1

Hash (cost=2,297.10..2,297.10 rows=39,503 width=8) (actual time=52.723..52.723 rows=40,006 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1563kB
13. 18.845 36.319 ↓ 1.0 40,006 1

Nested Loop (cost=0.42..2,297.10 rows=39,503 width=8) (actual time=0.092..36.319 rows=40,006 loops=1)

14. 0.034 0.034 ↑ 1.0 1 1

Seq Scan on users u (cost=0.00..2.35 rows=1 width=4) (actual time=0.032..0.034 rows=1 loops=1)

  • Filter: (id = 1)
  • Rows Removed by Filter: 17
15. 17.440 17.440 ↓ 1.0 40,006 1

Index Only Scan using users_channels_pkey on users_channels uc (cost=0.42..1,899.72 rows=39,503 width=8) (actual time=0.055..17.440 rows=40,006 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
Planning time : 6.001 ms
Execution time : 53,703.665 ms