explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RYau

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3,358.129 215,419.853 ↑ 1.0 1 1

Aggregate (cost=7,226,381.83..7,226,381.84 rows=1 width=58) (actual time=215,419.853..215,419.853 rows=1 loops=1)

2. 3,961.817 212,061.724 ↓ 10.1 3,227,145 1

Hash Join (cost=7,129,553.06..7,219,221.31 rows=318,244 width=58) (actual time=198,988.480..212,061.724 rows=3,227,145 loops=1)

  • Hash Cond: (ssd.channel_id = uc.channel_id)
3. 1,509.000 208,058.281 ↓ 10.3 3,227,145 1

Merge Join (cost=7,126,762.17..7,187,860.76 rows=312,458 width=70) (actual time=198,946.781..208,058.281 rows=3,227,145 loops=1)

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

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
5. 1,911.764 206,505.029 ↓ 11.2 3,227,145 1

Materialize (cost=7,126,741.20..7,180,069.29 rows=288,473 width=74) (actual time=198,946.706..206,505.029 rows=3,227,145 loops=1)

6. 3,288.273 204,593.265 ↓ 11.2 3,227,145 1

Merge Join (cost=7,126,741.20..7,179,348.11 rows=288,473 width=74) (actual time=198,946.692..204,593.265 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. 9,036.928 196,260.339 ↑ 1.1 3,227,145 1

Sort (cost=6,651,035.64..6,659,542.78 rows=3,402,856 width=66) (actual time=195,198.748..196,260.339 rows=3,227,145 loops=1)

  • Sort Key: ssd.product_id, ssd.channel_id
  • Sort Method: quicksort Memory: 414315kB
8. 187,223.411 187,223.411 ↑ 1.1 3,227,145 1

Seq Scan on sops_scenarios_details ssd (cost=0.00..6,281,854.44 rows=3,402,856 width=66) (actual time=130,419.376..187,223.411 rows=3,227,145 loops=1)

  • Filter: ((sop_id = 10) AND (date = '2019-07-01'::date))
  • Rows Removed by Filter: 211214235
9. 4,042.904 5,044.653 ↑ 1.0 3,227,145 1

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

  • Sort Key: sspc.product_level_4_id, sspc.channel_level_4_id
  • Sort Method: quicksort Memory: 249577kB
10. 1,001.749 1,001.749 ↑ 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.076..1,001.749 rows=3,227,145 loops=1)

  • Heap Fetches: 0
11. 12.856 41.626 ↓ 1.0 40,006 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 1563kB
12. 15.385 28.770 ↓ 1.0 40,006 1

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

13. 0.028 0.028 ↑ 1.0 1 1

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

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

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
Planning time : 3.234 ms
Execution time : 215,489.663 ms