explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I97R

Settings
# exclusive inclusive rows x rows loops node
1. 3,108.792 25,049.503 ↓ 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,259.258..25,049.503 rows=3,227,145 loops=1)

  • Hash Cond: (ssd.channel_id = uc.channel_id)
2. 1,486.036 21,899.038 ↓ 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,217.505..21,899.038 rows=3,227,145 loops=1)

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

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
4. 1,852.453 20,372.106 ↓ 11.3 3,227,145 1

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

5. 3,140.481 18,519.653 ↓ 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,217.431..18,519.653 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))
6. 8,586.646 10,418.842 ↓ 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,475.946..10,418.842 rows=3,227,145 loops=1)

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

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

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

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

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

  • Heap Fetches: 0
11. 12.947 41.673 ↓ 1.0 40,006 1

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

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

Nested Loop (cost=0.42..2,297.10 rows=39,503 width=8) (actual time=0.062..28.726 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.120 13.120 ↓ 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.032..13.120 rows=40,006 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
Planning time : 3.356 ms
Execution time : 25,728.773 ms