explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2JOS

Settings
# exclusive inclusive rows x rows loops node
1. 1,980.606 14,881.123 ↑ 1.0 2,199,216 1

Hash Left Join (cost=270,264.61..403,966.59 rows=2,227,678 width=41) (actual time=4,755.907..14,881.123 rows=2,199,216 loops=1)

  • Hash Cond: ((s.store_key = stock_sales.store_key) AND (s.article_key = stock_sales.article_key) AND (s.color_key = stock_sales.color_key) AND (s.size_key = stock_sales.size_key))
2. 8,150.972 8,386.558 ↑ 1.0 2,199,216 1

Bitmap Heap Scan on skus s (cost=41,704.94..124,722.31 rows=2,227,678 width=23) (actual time=240.102..8,386.558 rows=2,199,216 loops=1)

  • Recheck Cond: (date_max >= (now() - '28 days'::interval))
  • Heap Blocks: exact=26493
3. 235.586 235.586 ↑ 1.0 2,199,216 1

Bitmap Index Scan on skus_date_max_idx (cost=0.00..41,148.02 rows=2,227,678 width=0) (actual time=235.586..235.586 rows=2,199,216 loops=1)

  • Index Cond: (date_max >= (now() - '28 days'::interval))
4. 86.081 4,513.959 ↓ 1.0 205,378 1

Hash (cost=223,318.79..223,318.79 rows=202,644 width=18) (actual time=4,513.959..4,513.959 rows=205,378 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3324kB
5. 200.902 4,427.878 ↓ 1.0 205,378 1

GroupAggregate (cost=216,103.53..221,292.35 rows=202,644 width=18) (actual time=4,089.018..4,427.878 rows=205,378 loops=1)

  • Group Key: stock_sales.store_key, stock_sales.article_key, stock_sales.color_key, stock_sales.size_key
6. 532.720 4,226.976 ↓ 2.0 431,719 1

Sort (cost=216,103.53..216,630.60 rows=210,825 width=12) (actual time=4,089.007..4,226.976 rows=431,719 loops=1)

  • Sort Key: stock_sales.store_key, stock_sales.article_key, stock_sales.color_key, stock_sales.size_key
  • Sort Method: external merge Disk: 10144kB
7. 3,597.047 3,694.256 ↓ 2.0 431,719 1

Bitmap Heap Scan on stock_sales (cost=4,477.40..193,855.61 rows=210,825 width=12) (actual time=99.035..3,694.256 rows=431,719 loops=1)

  • Recheck Cond: ((date_key < now()) AND (date_key >= (now() - '28 days'::interval)))
  • Heap Blocks: exact=7820
8. 97.209 97.209 ↓ 2.0 431,719 1

Bitmap Index Scan on stock_sales_date_key_idx (cost=0.00..4,424.69 rows=210,825 width=0) (actual time=97.209..97.209 rows=431,719 loops=1)

  • Index Cond: ((date_key < now()) AND (date_key >= (now() - '28 days'::interval)))