explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xu5Z

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 5,269.373 ↑ 5.0 21 1

Sort (cost=75,274.86..75,275.12 rows=104 width=44) (actual time=5,269.371..5,269.373 rows=21 loops=1)

  • Sort Key: (concat(f.year, CASE WHEN (f.week < 10) THEN '-W0'::text ELSE '-W'::text END, f.week))
  • Sort Method: quicksort Memory: 26kB
2.          

CTE f

3. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1)

4. 0.033 5,269.347 ↑ 5.0 21 1

Subquery Scan on f (cost=75,217.41..75,271.37 rows=104 width=44) (actual time=5,175.630..5,269.347 rows=21 loops=1)

5. 60.648 5,269.314 ↑ 5.0 21 1

GroupAggregate (cost=75,217.41..75,269.81 rows=104 width=16) (actual time=5,175.621..5,269.314 rows=21 loops=1)

  • Group Key: p.year, p.week
6. 111.786 5,208.666 ↓ 53.7 219,387 1

Sort (cost=75,217.41..75,227.63 rows=4,088 width=12) (actual time=5,171.542..5,208.666 rows=219,387 loops=1)

  • Sort Key: p.year, p.week
  • Sort Method: external merge Disk: 4,248kB
7. 0.000 5,096.880 ↓ 53.7 219,387 1

Nested Loop (cost=366.83..74,972.19 rows=4,088 width=12) (actual time=0.720..5,096.880 rows=219,387 loops=1)

8. 572.696 2,784.649 ↓ 141.8 579,852 1

Nested Loop Left Join (cost=366.40..59,304.93 rows=4,088 width=20) (actual time=0.634..2,784.649 rows=579,852 loops=1)

9. 184.198 1,052.249 ↓ 141.8 579,852 1

Nested Loop (cost=365.97..57,134.38 rows=4,088 width=16) (actual time=0.618..1,052.249 rows=579,852 loops=1)

  • Join Filter: CASE WHEN (array_length(f_1.groups, 1) IS NULL) THEN true ELSE (p.group_key = ANY (f_1.groups)) END
10. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on f f_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=1)

11. 163.348 868.047 ↓ 70.9 579,852 1

Hash Join (cost=365.97..56,929.93 rows=8,177 width=16) (actual time=0.611..868.047 rows=579,852 loops=1)

  • Hash Cond: (p.store_key = st.store_key)
12. 410.914 704.619 ↓ 36.6 579,852 1

Hash Join (cost=353.69..56,874.92 rows=15,836 width=16) (actual time=0.139..704.619 rows=579,852 loops=1)

  • Hash Cond: ((p.year = calendar.year) AND (p.week = calendar.week))
13. 293.580 293.580 ↑ 1.0 2,153,736 1

Seq Scan on predictions_stores_dims_weekly p (cost=0.00..45,212.70 rows=2,153,736 width=16) (actual time=0.005..293.580 rows=2,153,736 loops=1)

  • Filter: (org_key = 1)
14. 0.006 0.125 ↑ 6.2 22 1

Hash (cost=351.65..351.65 rows=136 width=4) (actual time=0.125..0.125 rows=22 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.053 0.119 ↑ 6.2 22 1

HashAggregate (cost=348.93..350.29 rows=136 width=4) (actual time=0.113..0.119 rows=22 loops=1)

  • Group Key: calendar.year, calendar.week
16. 0.023 0.066 ↓ 1.0 140 1

Bitmap Heap Scan on calendar (cost=6.07..348.23 rows=139 width=4) (actual time=0.049..0.066 rows=140 loops=1)

  • Recheck Cond: ((calendar_key = 0) AND (date_key >= (now() - '70 days'::interval)) AND (date_key <= (now() + '70 days'::interval)))
  • Heap Blocks: exact=2
17. 0.043 0.043 ↓ 1.0 140 1

Bitmap Index Scan on calendar_pkey (cost=0.00..6.04 rows=139 width=0) (actual time=0.043..0.043 rows=140 loops=1)

  • Index Cond: ((calendar_key = 0) AND (date_key >= (now() - '70 days'::interval)) AND (date_key <= (now() + '70 days'::interval)))
18. 0.017 0.080 ↑ 1.0 78 1

Hash (cost=11.29..11.29 rows=79 width=4) (actual time=0.080..0.080 rows=78 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
19. 0.063 0.063 ↑ 1.0 78 1

Seq Scan on stores st (cost=0.00..11.29 rows=79 width=4) (actual time=0.014..0.063 rows=78 loops=1)

  • Filter: ((org_key = 1) AND (store_status = 'open_store'::text))
  • Rows Removed by Filter: 75
20. 1,159.704 1,159.704 ↓ 0.0 0 579,852

Index Scan using stores_dims_weekly_pkey on stores_dims_weekly sg (cost=0.43..0.53 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=579,852)

  • Index Cond: ((org_key = p.org_key) AND (org_key = 1) AND (store_key = p.store_key) AND (group_key = p.group_key) AND (collection_key = p.collection_key) AND (year = p.year) AND (week = p.week))
21. 579.852 2,319.408 ↓ 0.0 0 579,852

Subquery Scan on ls (cost=0.43..3.82 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=579,852)

  • Filter: (ls.stock > 0)
  • Rows Removed by Filter: 0
22. 0.000 1,739.556 ↓ 0.0 0 579,852

Limit (cost=0.43..3.81 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=579,852)

23. 1,739.556 1,739.556 ↓ 0.0 0 579,852

Index Scan Backward using stores_dims_weekly_pkey on stores_dims_weekly (cost=0.43..13.96 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=579,852)

  • Index Cond: ((org_key = p.org_key) AND (store_key = p.store_key) AND (group_key = p.group_key) AND (collection_key = p.collection_key) AND (year <= p.year) AND (week <= p.week))