explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7WvT : Optimization for: Optimization for: Optimization for: plan #ykf1; plan #Kkls; plan #hRGP

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3,256.241 34,539.588 ↓ 3.0 558,732 1

GroupAggregate (cost=2.28..119,562.77 rows=186,244 width=48) (actual time=3.575..34,539.588 rows=558,732 loops=1)

  • Group Key: s.sale_id
  • Buffers: shared hit=4872807 read=36842 written=246
2.          

CTE cdf

3. 0.027 0.435 ↑ 1.0 7 1

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

  • Buffers: shared hit=3 read=1
4. 0.068 0.408 ↑ 1.0 7 1

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

  • Sort Key: a_1.item_order
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3 read=1
5. 0.340 0.340 ↑ 1.0 7 1

Seq Scan on attribute a_1 (cost=0..1.19 rows=7 width=24) (actual time=0.331..0.34 rows=7 loops=1)

  • Filter: (a_1.attribute_type = 'SALE'::enum.attribute_type)
  • Buffers: shared read=1
6. 2,071.410 31,283.347 ↓ 10.5 3,911,124 1

Merge Join (cost=0.85..105,127.43 rows=372,488 width=113) (actual time=0.475..31,283.347 rows=3,911,124 loops=1)

  • Buffers: shared hit=4872799 read=36842 written=246
7. 423.045 24,809.198 ↓ 10.5 3,911,124 1

Nested Loop (cost=0.42..43,595.62 rows=372,488 width=40) (actual time=0.448..24,809.198 rows=3,911,124 loops=1)

  • Buffers: shared hit=509908 read=17592 written=110
8. 23,268.689 23,268.689 ↓ 3.0 558,732 1

Index Scan using sale_pkey on sale s (cost=0.42..36,145.72 rows=186,244 width=16) (actual time=0.019..23,268.689 rows=558,732 loops=1)

  • Filter: upper_inf(s.active_range)
  • Buffers: shared hit=509905 read=17591 written=110
9. 1,117.464 1,117.464 ↓ 3.5 7 558,732

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

  • Filter: (a.row_number <= 12)
  • Buffers: shared hit=3 read=1
10. 4,402.739 4,402.739 ↓ 4.3 5,204,297 1

Index Scan using sale_attribute_pkey on sale_attribute sa (cost=0.43..47,336.26 rows=1,224,400 width=121) (actual time=0.021..4,402.739 rows=5,204,297 loops=1)

  • Buffers: shared hit=4362891 read=19250 written=136
Planning time : 24.153 ms
Execution time : 34,600.775 ms