explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6VG1 : Optimization for: plan #F2pf

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 10.320 1,740.273 ↑ 1.2 28,328 1

Hash Left Join (cost=23,845.57..704,648.99 rows=33,847 width=132) (actual time=672.813..1,740.273 rows=28,328 loops=1)

  • Output: dwv.stop_id, dwv.item_id, dwv.client_day, COALESCE(t.oil_percentage, '0'::numeric), COALESCE(t_1.water_percentage, '0'::numeric), COALESCE(t_2.gas_percentage, '0'::numeric)
  • Hash Cond: (dwv.item_id = t_2.from_item_id)
  • Join Filter: (dwv.client_day >= (t_2.effective_date)::date)
  • Rows Removed by Join Filter: 12,152
  • Buffers: shared hit=597,964 read=256, temp read=1,655 written=1,655
2. 7.973 1,611.190 ↑ 1.2 28,328 1

Hash Left Join (cost=15,773.47..694,410.90 rows=33,847 width=100) (actual time=554.018..1,611.190 rows=28,328 loops=1)

  • Output: dwv.stop_id, dwv.item_id, dwv.client_day, t.oil_percentage, t_1.water_percentage
  • Hash Cond: (dwv.item_id = t_1.from_item_id)
  • Join Filter: (dwv.client_day >= (t_1.effective_date)::date)
  • Rows Removed by Join Filter: 11,702
  • Buffers: shared hit=597,176 read=256, temp read=1,087 written=1,087
3. 8.656 1,501.300 ↑ 1.2 28,328 1

Hash Left Join (cost=7,863.64..684,335.09 rows=33,847 width=68) (actual time=452.075..1,501.300 rows=28,328 loops=1)

  • Output: dwv.stop_id, dwv.item_id, dwv.client_day, t.oil_percentage
  • Hash Cond: (dwv.item_id = t.from_item_id)
  • Join Filter: (dwv.client_day >= (t.effective_date)::date)
  • Rows Removed by Join Filter: 11,642
  • Buffers: shared hit=596,388 read=256, temp read=542 written=542
4. 1,040.698 1,040.698 ↑ 1.2 28,328 1

Seq Scan on compass.daily_well_values dwv (cost=0.00..674,305.46 rows=33,847 width=36) (actual time=0.108..1,040.698 rows=28,328 loops=1)

  • Output: dwv.stop_id, dwv.item_id, dwv.client_day
  • Filter: ((dwv.client_day >= '2020-01-01'::date) AND (dwv.client_day <= '2020-01-30'::date))
  • Rows Removed by Filter: 3,758,578
  • Buffers: shared hit=595,856
5. 0.095 451.946 ↑ 1.0 419 1

Hash (cost=7,858.22..7,858.22 rows=433 width=56) (actual time=451.946..451.946 rows=419 loops=1)

  • Output: t.oil_percentage, t.from_item_id, t.effective_date
  • Buckets: 1,024 Batches: 1 Memory Usage: 37kB
  • Buffers: shared hit=532 read=256, temp read=542 written=542
6. 0.077 451.851 ↑ 1.0 419 1

Subquery Scan on t (cost=7,676.46..7,858.22 rows=433 width=56) (actual time=441.808..451.851 rows=419 loops=1)

  • Output: t.oil_percentage, t.from_item_id, t.effective_date
  • Buffers: shared hit=532 read=256, temp read=542 written=542
7. 3.925 451.774 ↑ 1.0 419 1

Unique (cost=7,676.46..7,853.89 rows=433 width=156) (actual time=441.806..451.774 rows=419 loops=1)

  • Output: NULL::uuid, stg_network_links.from_item_id, NULL::uuid, al.effective_date, stg_network_links.products, (CASE WHEN (1 = ANY (stg_network_links.products)) THEN al.oil_percentage ELSE '0'::numeric END), al.link_id, ((al.effective_date)::date), al.submission_date, al.updated_at
  • Buffers: shared hit=532 read=256, temp read=542 written=542
8. 79.027 447.849 ↑ 1.0 35,162 1

Sort (cost=7,676.46..7,765.18 rows=35,487 width=156) (actual time=441.805..447.849 rows=35,162 loops=1)

  • Output: NULL::uuid, stg_network_links.from_item_id, NULL::uuid, al.effective_date, stg_network_links.products, (CASE WHEN (1 = ANY (stg_network_links.products)) THEN al.oil_percentage ELSE '0'::numeric END), al.link_id, ((al.effective_date)::date), al.submission_date, al.updated_at
  • Sort Key: al.link_id, ((al.effective_date)::date) DESC, stg_network_links.products, al.submission_date DESC, al.updated_at DESC
  • Sort Method: external merge Disk: 4,328kB
  • Buffers: shared hit=532 read=256, temp read=542 written=542
9. 21.191 368.822 ↑ 1.0 35,162 1

Hash Join (cost=26.11..2,201.53 rows=35,487 width=156) (actual time=1.523..368.822 rows=35,162 loops=1)

  • Output: NULL::uuid, stg_network_links.from_item_id, NULL::uuid, al.effective_date, stg_network_links.products, CASE WHEN (1 = ANY (stg_network_links.products)) THEN al.oil_percentage ELSE '0'::numeric END, al.link_id, (al.effective_date)::date, al.submission_date, al.updated_at
  • Hash Cond: (al.link_id = stg_network_links.id)
  • Buffers: shared hit=532 read=256
10. 347.409 347.409 ↑ 1.0 37,180 1

Seq Scan on compass.stg_allocations al (cost=0.00..1,148.81 rows=37,181 width=50) (actual time=1.287..347.409 rows=37,180 loops=1)

  • Output: al.id, al.organization_id, al.created_at, al.updated_at, al.effective_date, al.submission_date, al.tz, al.oil_percentage, al.gas_percentage, al.water_percentage, al.seq, al.link_id
  • Buffers: shared hit=521 read=256
11. 0.087 0.222 ↑ 1.0 419 1

Hash (cost=20.88..20.88 rows=419 width=64) (actual time=0.222..0.222 rows=419 loops=1)

  • Output: stg_network_links.from_item_id, stg_network_links.products, stg_network_links.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 48kB
  • Buffers: shared hit=11
12. 0.135 0.135 ↑ 1.0 419 1

Seq Scan on compass.stg_network_links (cost=0.00..20.88 rows=419 width=64) (actual time=0.007..0.135 rows=419 loops=1)

  • Output: stg_network_links.from_item_id, stg_network_links.products, stg_network_links.id
  • Filter: (1 = ANY (stg_network_links.products))
  • Rows Removed by Filter: 26
  • Buffers: shared hit=11
13. 0.101 101.917 ↑ 1.0 421 1

Hash (cost=7,904.42..7,904.42 rows=433 width=56) (actual time=101.917..101.917 rows=421 loops=1)

  • Output: t_1.water_percentage, t_1.from_item_id, t_1.effective_date
  • Buckets: 1,024 Batches: 1 Memory Usage: 37kB
  • Buffers: shared hit=788, temp read=545 written=545
14. 0.068 101.816 ↑ 1.0 421 1

Subquery Scan on t_1 (cost=7,721.38..7,904.42 rows=433 width=56) (actual time=91.541..101.816 rows=421 loops=1)

  • Output: t_1.water_percentage, t_1.from_item_id, t_1.effective_date
  • Buffers: shared hit=788, temp read=545 written=545
15. 3.978 101.748 ↑ 1.0 421 1

Unique (cost=7,721.38..7,900.09 rows=433 width=156) (actual time=91.539..101.748 rows=421 loops=1)

  • Output: NULL::uuid, stg_network_links_1.from_item_id, NULL::uuid, al_1.effective_date, stg_network_links_1.products, (CASE WHEN (2 = ANY (stg_network_links_1.products)) THEN al_1.water_percentage ELSE '0'::numeric END), al_1.link_id, ((al_1.effective_date)::date), al_1.submission_date, al_1.updated_at
  • Buffers: shared hit=788, temp read=545 written=545
16. 73.515 97.770 ↑ 1.0 35,352 1

Sort (cost=7,721.38..7,810.73 rows=35,741 width=156) (actual time=91.537..97.770 rows=35,352 loops=1)

  • Output: NULL::uuid, stg_network_links_1.from_item_id, NULL::uuid, al_1.effective_date, stg_network_links_1.products, (CASE WHEN (2 = ANY (stg_network_links_1.products)) THEN al_1.water_percentage ELSE '0'::numeric END), al_1.link_id, ((al_1.effective_date)::date), al_1.submission_date, al_1.updated_at
  • Sort Key: al_1.link_id, ((al_1.effective_date)::date) DESC, stg_network_links_1.products, al_1.submission_date DESC, al_1.updated_at DESC
  • Sort Method: external merge Disk: 4,352kB
  • Buffers: shared hit=788, temp read=545 written=545
17. 19.624 24.255 ↑ 1.0 35,352 1

Hash Join (cost=26.15..2,207.92 rows=35,741 width=156) (actual time=0.259..24.255 rows=35,352 loops=1)

  • Output: NULL::uuid, stg_network_links_1.from_item_id, NULL::uuid, al_1.effective_date, stg_network_links_1.products, CASE WHEN (2 = ANY (stg_network_links_1.products)) THEN al_1.water_percentage ELSE '0'::numeric END, al_1.link_id, (al_1.effective_date)::date, al_1.submission_date, al_1.updated_at
  • Hash Cond: (al_1.link_id = stg_network_links_1.id)
  • Buffers: shared hit=788
18. 4.394 4.394 ↑ 1.0 37,180 1

Seq Scan on compass.stg_allocations al_1 (cost=0.00..1,148.81 rows=37,181 width=51) (actual time=0.008..4.394 rows=37,180 loops=1)

  • Output: al_1.id, al_1.organization_id, al_1.created_at, al_1.updated_at, al_1.effective_date, al_1.submission_date, al_1.tz, al_1.oil_percentage, al_1.gas_percentage, al_1.water_percentage, al_1.seq, al_1.link_id
  • Buffers: shared hit=777
19. 0.090 0.237 ↑ 1.0 422 1

Hash (cost=20.88..20.88 rows=422 width=64) (actual time=0.237..0.237 rows=422 loops=1)

  • Output: stg_network_links_1.from_item_id, stg_network_links_1.products, stg_network_links_1.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 48kB
  • Buffers: shared hit=11
20. 0.147 0.147 ↑ 1.0 422 1

Seq Scan on compass.stg_network_links stg_network_links_1 (cost=0.00..20.88 rows=422 width=64) (actual time=0.011..0.147 rows=422 loops=1)

  • Output: stg_network_links_1.from_item_id, stg_network_links_1.products, stg_network_links_1.id
  • Filter: (2 = ANY (stg_network_links_1.products))
  • Rows Removed by Filter: 23
  • Buffers: shared hit=11
21. 0.107 118.763 ↓ 1.0 436 1

Hash (cost=8,066.69..8,066.69 rows=433 width=56) (actual time=118.763..118.763 rows=436 loops=1)

  • Output: t_2.gas_percentage, t_2.from_item_id, t_2.effective_date
  • Buckets: 1,024 Batches: 1 Memory Usage: 39kB
  • Buffers: shared hit=788, temp read=568 written=568
22. 0.085 118.656 ↓ 1.0 436 1

Subquery Scan on t_2 (cost=7,879.42..8,066.69 rows=433 width=56) (actual time=107.637..118.656 rows=436 loops=1)

  • Output: t_2.gas_percentage, t_2.from_item_id, t_2.effective_date
  • Buffers: shared hit=788, temp read=568 written=568
23. 4.079 118.571 ↓ 1.0 436 1

Unique (cost=7,879.42..8,062.36 rows=433 width=156) (actual time=107.635..118.571 rows=436 loops=1)

  • Output: NULL::uuid, stg_network_links_2.from_item_id, NULL::uuid, al_2.effective_date, stg_network_links_2.products, (CASE WHEN (3 = ANY (stg_network_links_2.products)) THEN al_2.gas_percentage ELSE '0'::numeric END), al_2.link_id, ((al_2.effective_date)::date), al_2.submission_date, al_2.updated_at
  • Buffers: shared hit=788, temp read=568 written=568
24. 85.641 114.492 ↑ 1.0 36,428 1

Sort (cost=7,879.42..7,970.89 rows=36,588 width=156) (actual time=107.633..114.492 rows=36,428 loops=1)

  • Output: NULL::uuid, stg_network_links_2.from_item_id, NULL::uuid, al_2.effective_date, stg_network_links_2.products, (CASE WHEN (3 = ANY (stg_network_links_2.products)) THEN al_2.gas_percentage ELSE '0'::numeric END), al_2.link_id, ((al_2.effective_date)::date), al_2.submission_date, al_2.updated_at
  • Sort Key: al_2.link_id, ((al_2.effective_date)::date) DESC, stg_network_links_2.products, al_2.submission_date DESC, al_2.updated_at DESC
  • Sort Method: external merge Disk: 4,536kB
  • Buffers: shared hit=788, temp read=568 written=568
25. 23.252 28.851 ↑ 1.0 36,428 1

Hash Join (cost=26.28..2,229.22 rows=36,588 width=156) (actual time=0.267..28.851 rows=36,428 loops=1)

  • Output: NULL::uuid, stg_network_links_2.from_item_id, NULL::uuid, al_2.effective_date, stg_network_links_2.products, CASE WHEN (3 = ANY (stg_network_links_2.products)) THEN al_2.gas_percentage ELSE '0'::numeric END, al_2.link_id, (al_2.effective_date)::date, al_2.submission_date, al_2.updated_at
  • Hash Cond: (al_2.link_id = stg_network_links_2.id)
  • Buffers: shared hit=788
26. 5.356 5.356 ↑ 1.0 37,180 1

Seq Scan on compass.stg_allocations al_2 (cost=0.00..1,148.81 rows=37,181 width=52) (actual time=0.012..5.356 rows=37,180 loops=1)

  • Output: al_2.id, al_2.organization_id, al_2.created_at, al_2.updated_at, al_2.effective_date, al_2.submission_date, al_2.tz, al_2.oil_percentage, al_2.gas_percentage, al_2.water_percentage, al_2.seq, al_2.link_id
  • Buffers: shared hit=777
27. 0.098 0.243 ↓ 1.0 437 1

Hash (cost=20.88..20.88 rows=432 width=64) (actual time=0.242..0.243 rows=437 loops=1)

  • Output: stg_network_links_2.from_item_id, stg_network_links_2.products, stg_network_links_2.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 50kB
  • Buffers: shared hit=11
28. 0.145 0.145 ↓ 1.0 437 1

Seq Scan on compass.stg_network_links stg_network_links_2 (cost=0.00..20.88 rows=432 width=64) (actual time=0.010..0.145 rows=437 loops=1)

  • Output: stg_network_links_2.from_item_id, stg_network_links_2.products, stg_network_links_2.id
  • Filter: (3 = ANY (stg_network_links_2.products))
  • Rows Removed by Filter: 8
  • Buffers: shared hit=11