explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IfJs

Settings
# exclusive inclusive rows x rows loops node
1. 3,788.486 12,426.707 ↓ 3.0 558,732 1

Hash Join (cost=149,055.79..197,589.05 rows=186,239 width=737) (actual time=498.31..12,426.707 rows=558,732 loops=1)

  • Buffers: shared hit=4925092, temp read=20323 written=25939
2.          

CTE cdf

3. 0.010 0.040 ↑ 1.0 7 1

WindowAgg (cost=1.29..1.43 rows=7 width=32) (actual time=0.035..0.04 rows=7 loops=1)

  • Buffers: shared hit=1
4. 0.020 0.030 ↑ 1.0 7 1

Sort (cost=1.29..1.3 rows=7 width=24) (actual time=0.029..0.03 rows=7 loops=1)

  • Sort Key: a.item_order
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
5. 0.010 0.010 ↑ 1.0 7 1

Seq Scan on attribute a (cost=0..1.19 rows=7 width=24) (actual time=0.008..0.01 rows=7 loops=1)

  • Filter: (a.attribute_type = 'SALE'::enum.attribute_type)
  • Buffers: shared hit=1
6.          

CTE cdf_values

7. 2,434.965 7,828.758 ↓ 3.0 558,732 1

GroupAggregate (cost=0.85..119,559.43 rows=186,239 width=48) (actual time=0.111..7,828.758 rows=558,732 loops=1)

  • Group Key: s.sale_id
  • Buffers: shared hit=4909638
8. 1,579.607 5,393.793 ↓ 10.5 3,911,124 1

Merge Join (cost=0.85..105,125.9 rows=372,478 width=113) (actual time=0.071..5,393.793 rows=3,911,124 loops=1)

  • Buffers: shared hit=4909638
9. 641.025 1,784.813 ↓ 10.5 3,911,124 1

Nested Loop (cost=0.42..43,595.14 rows=372,478 width=40) (actual time=0.059..1,784.813 rows=3,911,124 loops=1)

  • Buffers: shared hit=527497
10. 585.056 585.056 ↓ 3.0 558,732 1

Index Scan using sale_pkey on sale s (cost=0.42..36,145.44 rows=186,239 width=16) (actual time=0.019..585.056 rows=558,732 loops=1)

  • Filter: upper_inf(s.active_range)
  • Buffers: shared hit=527496
11. 558.732 558.732 ↓ 3.5 7 558,732

CTE Scan on cdf a_1 (cost=0..0.16 rows=2 width=24) (actual time=0..0.001 rows=7 loops=558,732)

  • Filter: (a_1.row_number <= 12)
  • Buffers: shared hit=1
12. 2,029.373 2,029.373 ↓ 4.3 5,204,297 1

Index Scan using sale_attribute_pkey on sale_attribute sa (cost=0.43..47,335.66 rows=1,224,360 width=121) (actual time=0.008..2,029.373 rows=5,204,297 loops=1)

  • Buffers: shared hit=4382141
13. 8,140.434 8,140.434 ↓ 3.0 558,732 1

CTE Scan on cdf_values cv (cost=0..3,724.78 rows=186,239 width=48) (actual time=0.114..8,140.434 rows=558,732 loops=1)

  • Buffers: shared hit=4909638, temp written=5646
14. 272.035 497.787 ↓ 3.0 558,732 1

Hash (cost=22,437.95..22,437.95 rows=186,239 width=181) (actual time=497.787..497.787 rows=558,732 loops=1)

  • Buffers: shared hit=15454, temp written=11210
15. 225.752 225.752 ↓ 3.0 558,732 1

Seq Scan on sale c (cost=0..22,437.95 rows=186,239 width=181) (actual time=0.017..225.752 rows=558,732 loops=1)

  • Filter: upper_inf(c.active_range)
  • Buffers: shared hit=15454
Planning time : 4.892 ms
Execution time : 12,478.198 ms