explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F2pf

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.025 1,039.131 ↑ 1.2 30 1

Hash Left Join (cost=1,570.46..688,952.86 rows=36 width=132) (actual time=564.431..1,039.131 rows=30 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: 16
  • Buffers: shared hit=595,903
2. 0.021 1,039.028 ↑ 1.2 30 1

Hash Left Join (cost=1,046.97..688,428.69 rows=36 width=100) (actual time=564.342..1,039.028 rows=30 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: 16
  • Buffers: shared hit=595,887
3. 0.042 1,038.929 ↑ 1.2 30 1

Hash Left Join (cost=523.49..687,904.53 rows=36 width=68) (actual time=564.255..1,038.929 rows=30 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: 16
  • Buffers: shared hit=595,872
4. 1,038.729 1,038.729 ↑ 1.2 30 1

Seq Scan on compass.daily_well_values dwv (cost=0.00..687,380.37 rows=36 width=36) (actual time=564.072..1,038.729 rows=30 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) AND (dwv.item_id = '38a490cb-a330-4884-8acf-8f025f543886'::uuid))
  • Rows Removed by Filter: 3,786,876
  • Buffers: shared hit=595,856
5. 0.002 0.158 ↑ 1.0 1 1

Hash (cost=523.47..523.47 rows=1 width=56) (actual time=0.158..0.158 rows=1 loops=1)

  • Output: t.oil_percentage, t.from_item_id, t.effective_date
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=16
6. 0.006 0.156 ↑ 1.0 1 1

Subquery Scan on t (cost=520.52..523.47 rows=1 width=56) (actual time=0.153..0.156 rows=1 loops=1)

  • Output: t.oil_percentage, t.from_item_id, t.effective_date
  • Filter: (t.from_item_id = '38a490cb-a330-4884-8acf-8f025f543886'::uuid)
  • Buffers: shared hit=16
7. 0.001 0.150 ↑ 169.0 1 1

Unique (cost=520.52..521.36 rows=169 width=156) (actual time=0.149..0.150 rows=1 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=16
8. 0.019 0.149 ↑ 84.5 2 1

Sort (cost=520.52..520.94 rows=169 width=156) (actual time=0.149..0.149 rows=2 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: quicksort Memory: 25kB
  • Buffers: shared hit=16
9. 0.012 0.130 ↑ 84.5 2 1

Nested Loop (cost=5.08..514.26 rows=169 width=156) (actual time=0.114..0.130 rows=2 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=16
10. 0.091 0.091 ↑ 2.0 1 1

Seq Scan on compass.stg_network_links (cost=0.00..21.98 rows=2 width=64) (actual time=0.077..0.091 rows=1 loops=1)

  • Output: stg_network_links.id, stg_network_links.organization_id, stg_network_links.created_at, stg_network_links.updated_at, stg_network_links.effective_date, stg_network_links.submission_date, stg_network_links.tz, stg_network_links.from_item_id, stg_network_links.to_item_id, stg_network_links.products, stg_network_links.seq
  • Filter: ((stg_network_links.from_item_id = '38a490cb-a330-4884-8acf-8f025f543886'::uuid) AND (1 = ANY (stg_network_links.products)))
  • Rows Removed by Filter: 444
  • Buffers: shared hit=11
11. 0.008 0.027 ↑ 43.0 2 1

Bitmap Heap Scan on compass.stg_allocations al (cost=5.08..244.02 rows=86 width=50) (actual time=0.025..0.027 rows=2 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
  • Recheck Cond: (al.link_id = stg_network_links.id)
  • Heap Blocks: exact=2
  • Buffers: shared hit=5
12. 0.019 0.019 ↑ 43.0 2 1

Bitmap Index Scan on idx_stg_allocations_link_id (cost=0.00..5.06 rows=86 width=0) (actual time=0.019..0.019 rows=2 loops=1)

  • Index Cond: (al.link_id = stg_network_links.id)
  • Buffers: shared hit=3
13. 0.000 0.078 ↑ 1.0 1 1

Hash (cost=523.47..523.47 rows=1 width=56) (actual time=0.078..0.078 rows=1 loops=1)

  • Output: t_1.water_percentage, t_1.from_item_id, t_1.effective_date
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=15
14. 0.005 0.078 ↑ 1.0 1 1

Subquery Scan on t_1 (cost=520.52..523.47 rows=1 width=56) (actual time=0.076..0.078 rows=1 loops=1)

  • Output: t_1.water_percentage, t_1.from_item_id, t_1.effective_date
  • Filter: (t_1.from_item_id = '38a490cb-a330-4884-8acf-8f025f543886'::uuid)
  • Buffers: shared hit=15
15. 0.001 0.073 ↑ 169.0 1 1

Unique (cost=520.52..521.36 rows=169 width=156) (actual time=0.073..0.073 rows=1 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=15
16. 0.006 0.072 ↑ 169.0 1 1

Sort (cost=520.52..520.94 rows=169 width=156) (actual time=0.072..0.072 rows=1 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: quicksort Memory: 25kB
  • Buffers: shared hit=15
17. 0.005 0.066 ↑ 169.0 1 1

Nested Loop (cost=5.08..514.26 rows=169 width=156) (actual time=0.062..0.066 rows=1 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=15
18. 0.053 0.053 ↑ 2.0 1 1

Seq Scan on compass.stg_network_links stg_network_links_1 (cost=0.00..21.98 rows=2 width=64) (actual time=0.050..0.053 rows=1 loops=1)

  • Output: stg_network_links_1.id, stg_network_links_1.organization_id, stg_network_links_1.created_at, stg_network_links_1.updated_at, stg_network_links_1.effective_date, stg_network_links_1.submission_date, stg_network_links_1.tz, stg_network_links_1.from_item_id, stg_network_links_1.to_item_id, stg_network_links_1.products, stg_network_links_1.seq
  • Filter: ((stg_network_links_1.from_item_id = '38a490cb-a330-4884-8acf-8f025f543886'::uuid) AND (2 = ANY (stg_network_links_1.products)))
  • Rows Removed by Filter: 444
  • Buffers: shared hit=11
19. 0.002 0.008 ↑ 86.0 1 1

Bitmap Heap Scan on compass.stg_allocations al_1 (cost=5.08..244.02 rows=86 width=51) (actual time=0.008..0.008 rows=1 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
  • Recheck Cond: (al_1.link_id = stg_network_links_1.id)
  • Heap Blocks: exact=1
  • Buffers: shared hit=4
20. 0.006 0.006 ↑ 86.0 1 1

Bitmap Index Scan on idx_stg_allocations_link_id (cost=0.00..5.06 rows=86 width=0) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (al_1.link_id = stg_network_links_1.id)
  • Buffers: shared hit=3
21. 0.001 0.078 ↑ 1.0 1 1

Hash (cost=523.47..523.47 rows=1 width=56) (actual time=0.078..0.078 rows=1 loops=1)

  • Output: t_2.gas_percentage, t_2.from_item_id, t_2.effective_date
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=16
22. 0.002 0.077 ↑ 1.0 1 1

Subquery Scan on t_2 (cost=520.52..523.47 rows=1 width=56) (actual time=0.076..0.077 rows=1 loops=1)

  • Output: t_2.gas_percentage, t_2.from_item_id, t_2.effective_date
  • Filter: (t_2.from_item_id = '38a490cb-a330-4884-8acf-8f025f543886'::uuid)
  • Buffers: shared hit=16
23. 0.002 0.075 ↑ 169.0 1 1

Unique (cost=520.52..521.36 rows=169 width=156) (actual time=0.074..0.075 rows=1 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=16
24. 0.007 0.073 ↑ 84.5 2 1

Sort (cost=520.52..520.94 rows=169 width=156) (actual time=0.072..0.073 rows=2 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: quicksort Memory: 25kB
  • Buffers: shared hit=16
25. 0.005 0.066 ↑ 84.5 2 1

Nested Loop (cost=5.08..514.26 rows=169 width=156) (actual time=0.056..0.066 rows=2 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=16
26. 0.052 0.052 ↑ 2.0 1 1

Seq Scan on compass.stg_network_links stg_network_links_2 (cost=0.00..21.98 rows=2 width=64) (actual time=0.043..0.052 rows=1 loops=1)

  • Output: stg_network_links_2.id, stg_network_links_2.organization_id, stg_network_links_2.created_at, stg_network_links_2.updated_at, stg_network_links_2.effective_date, stg_network_links_2.submission_date, stg_network_links_2.tz, stg_network_links_2.from_item_id, stg_network_links_2.to_item_id, stg_network_links_2.products, stg_network_links_2.seq
  • Filter: ((stg_network_links_2.from_item_id = '38a490cb-a330-4884-8acf-8f025f543886'::uuid) AND (3 = ANY (stg_network_links_2.products)))
  • Rows Removed by Filter: 444
  • Buffers: shared hit=11
27. 0.003 0.009 ↑ 43.0 2 1

Bitmap Heap Scan on compass.stg_allocations al_2 (cost=5.08..244.02 rows=86 width=52) (actual time=0.008..0.009 rows=2 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
  • Recheck Cond: (al_2.link_id = stg_network_links_2.id)
  • Heap Blocks: exact=2
  • Buffers: shared hit=5
28. 0.006 0.006 ↑ 43.0 2 1

Bitmap Index Scan on idx_stg_allocations_link_id (cost=0.00..5.06 rows=86 width=0) (actual time=0.006..0.006 rows=2 loops=1)

  • Index Cond: (al_2.link_id = stg_network_links_2.id)
  • Buffers: shared hit=3