explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ci1t : Optimization for: JOIN Query; plan #kGP

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,127.249 704,298.327 ↑ 1,361.7 3,650,741 1

Nested Loop (cost=101,825.52..63,520,202.08 rows=4,971,090,669 width=52) (actual time=3,199.805..704,298.327 rows=3,650,741 loops=1)

2. 1,985.579 699,520.337 ↓ 28.9 3,650,741 1

Hash Left Join (cost=30.74..1,278,887.23 rows=126,141 width=47) (actual time=13.567..699,520.337 rows=3,650,741 loops=1)

  • Hash Cond: ((popstats.display_unit_id)::text = (display_unit.display_unit_id)::text)
3. 499.582 697,522.523 ↓ 28.9 3,650,741 1

Nested Loop (cost=0.98..1,277,276.57 rows=126,141 width=45) (actual time=1.311..697,522.523 rows=3,650,741 loops=1)

4. 0.045 0.045 ↓ 4.0 16 1

Index Scan using idx_campaign_data_sales_order_number on campaign_data (cost=0.29..14.73 rows=4 width=20) (actual time=0.012..0.045 rows=16 loops=1)

  • Index Cond: ((sales_order_number)::text = 'SO02764770'::text)
5. 697,022.896 697,022.896 ↓ 2.0 228,171 16

Index Scan using popstats_partial_campaign_id on popstats (cost=0.70..318,196.81 rows=111,865 width=45) (actual time=0.918..43,563.931 rows=228,171 loops=16)

  • Index Cond: ((campaign_id)::text = (campaign_data.campaign_id)::text)
6. 0.170 12.235 ↑ 1.0 878 1

Hash (cost=18.78..18.78 rows=878 width=20) (actual time=12.235..12.235 rows=878 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
7. 12.065 12.065 ↑ 1.0 878 1

Seq Scan on display_unit (cost=0.00..18.78 rows=878 width=20) (actual time=0.004..12.065 rows=878 loops=1)

8. 464.479 3,650.741 ↑ 39,409.0 1 3,650,741

Materialize (cost=101,794.79..102,780.01 rows=39,409 width=16) (actual time=0.001..0.001 rows=1 loops=3,650,741)

9. 0.001 3,186.262 ↑ 39,409.0 1 1

Subquery Scan on anon_1 (cost=101,794.79..102,582.97 rows=39,409 width=16) (actual time=3,186.232..3,186.262 rows=1 loops=1)

10. 24.742 3,186.261 ↑ 39,409.0 1 1

HashAggregate (cost=101,794.79..102,188.88 rows=39,409 width=16) (actual time=3,186.232..3,186.261 rows=1 loops=1)

  • Group Key: booked_plays.salesid, booked_plays.customerorder
11. 2,754.005 3,161.519 ↓ 1.0 47,268 1

Bitmap Heap Scan on booked_plays (cost=1,511.41..101,563.19 rows=46,320 width=16) (actual time=408.841..3,161.519 rows=47,268 loops=1)

  • Recheck Cond: ((salesid)::text = 'SO02764770'::text)
  • Heap Blocks: exact=2,129
12. 407.514 407.514 ↓ 1.0 47,268 1

Bitmap Index Scan on booked_plays_salesid (cost=0.00..1,499.83 rows=46,320 width=0) (actual time=407.514..407.514 rows=47,268 loops=1)

  • Index Cond: ((salesid)::text = 'SO02764770'::text)
Planning time : 27.738 ms