explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aied : Optimization for: Optimization for: plan #RYau; plan #9HEQ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3,337.554 28,863.334 ↑ 1.0 1 1

Aggregate (cost=4,212,768.31..4,212,768.32 rows=1 width=58) (actual time=28,863.334..28,863.334 rows=1 loops=1)

2. 3,412.203 25,525.780 ↓ 10.3 3,227,145 1

Hash Join (cost=4,118,323.27..4,205,713.01 rows=313,568 width=58) (actual time=13,249.805..25,525.780 rows=3,227,145 loops=1)

  • Hash Cond: (ssd.channel_id = uc.channel_id)
3. 1,508.047 22,071.715 ↓ 10.5 3,227,145 1

Merge Join (cost=4,115,532.38..4,174,761.61 rows=307,998 width=70) (actual time=13,207.874..22,071.715 rows=3,227,145 loops=1)

  • Merge Cond: (up.product_id = ssd.product_id)
4. 42.913 42.913 ↑ 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.057..42.913 rows=92,904 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
5. 1,883.281 20,520.755 ↓ 11.3 3,227,145 1

Materialize (cost=4,115,510.73..4,167,020.21 rows=284,351 width=74) (actual time=13,207.807..20,520.755 rows=3,227,145 loops=1)

6. 3,207.283 18,637.474 ↓ 11.3 3,227,145 1

Merge Join (cost=4,115,510.73..4,166,309.33 rows=284,351 width=74) (actual time=13,207.796..18,637.474 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. 8,617.528 10,455.830 ↓ 1.0 3,227,145 1

Sort (cost=3,639,589.87..3,647,504.04 rows=3,165,667 width=66) (actual time=9,476.608..10,455.830 rows=3,227,145 loops=1)

  • Sort Key: ssd.product_id, ssd.channel_id
  • Sort Method: quicksort Memory: 414315kB
8. 1,582.958 1,838.302 ↓ 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=266.049..1,838.302 rows=3,227,145 loops=1)

  • Recheck Cond: ((sop_id = 10) AND (date = '2019-07-01'::date))
  • Heap Blocks: exact=45097
9. 255.344 255.344 ↓ 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=255.344..255.344 rows=3,227,145 loops=1)

  • Index Cond: ((sop_id = 10) AND (date = '2019-07-01'::date))
10. 3,981.061 4,974.361 ↑ 1.0 3,227,145 1

Sort (cost=475,918.64..483,991.16 rows=3,229,010 width=8) (actual time=3,731.169..4,974.361 rows=3,227,145 loops=1)

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

Index Only Scan using main_idx on sops_scenarios_products_channels_tree sspc (cost=0.56..126,819.71 rows=3,229,010 width=8) (actual time=0.087..993.300 rows=3,227,145 loops=1)

  • Heap Fetches: 0
12. 12.940 41.862 ↓ 1.0 40,006 1

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

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

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

14. 0.027 0.027 ↑ 1.0 1 1

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 17
15. 13.774 13.774 ↓ 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.038..13.774 rows=40,006 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
Planning time : 3.362 ms
Execution time : 28,934.651 ms