explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4jMo

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 43.965 ↑ 1.0 30 1

Limit (cost=23,409.79..23,410.01 rows=30 width=84) (actual time=43.905..43.965 rows=30 loops=1)

2. 0.029 43.960 ↑ 3.6 30 1

Unique (cost=23,409.79..23,410.59 rows=107 width=84) (actual time=43.903..43.960 rows=30 loops=1)

3. 5.688 43.931 ↓ 3.4 360 1

Sort (cost=23,409.79..23,410.05 rows=107 width=84) (actual time=43.902..43.931 rows=360 loops=1)

  • Sort Key: dwv.stop_id, dwv.client_day, ((d.effective_date)::date) DESC, d.submission_date DESC, d.updated_at DESC
  • Sort Method: quicksort Memory: 1016kB
4. 2.822 38.243 ↓ 54.7 5,856 1

Hash Left Join (cost=2,172.54..23,406.18 rows=107 width=84) (actual time=13.155..38.243 rows=5,856 loops=1)

  • Hash Cond: (c.link_id = d.link_id)
  • Join Filter: ((d.effective_date <= c.effective_date) AND (d.effective_date <= dwv.client_day))
  • Rows Removed by Join Filter: 5584
5. 0.335 34.887 ↓ 10.7 1,144 1

Nested Loop Left Join (cost=2,109.92..23,328.56 rows=107 width=88) (actual time=12.597..34.887 rows=1,144 loops=1)

  • Join Filter: ((c.effective_date <= dwv.client_day) AND (c.from_item_id = dwv.item_id))
  • Rows Removed by Join Filter: 196
6. 24.473 31.872 ↓ 1.3 134 1

Bitmap Heap Scan on daily_well_values dwv (cost=1,959.57..23,162.82 rows=107 width=36) (actual time=10.131..31.872 rows=134 loops=1)

  • Recheck Cond: (client_day >= '2020-01-10'::date)
  • Filter: ((item_id IS NOT NULL) AND (item_id = '36074c49-3513-4035-8fe6-fb79d9032d20'::uuid))
  • Rows Removed by Filter: 42450
  • Heap Blocks: exact=12130
7. 7.399 7.399 ↓ 1.1 43,787 1

Bitmap Index Scan on idx_daily_well_values_client_day (cost=0.00..1,959.54 rows=41,483 width=0) (actual time=7.399..7.399 rows=43,787 loops=1)

  • Index Cond: (client_day >= '2020-01-10'::date)
8. 0.111 2.680 ↓ 10.0 10 134

Materialize (cost=150.35..163.88 rows=1 width=68) (actual time=0.018..0.020 rows=10 loops=134)

9. 0.196 2.569 ↓ 10.0 10 1

Subquery Scan on c (cost=150.35..163.87 rows=1 width=68) (actual time=2.457..2.569 rows=10 loops=1)

  • Filter: (c.from_item_id = '36074c49-3513-4035-8fe6-fb79d9032d20'::uuid)
  • Rows Removed by Filter: 1440
10. 0.198 2.373 ↓ 6.8 1,450 1

Unique (cost=150.35..161.22 rows=212 width=180) (actual time=2.056..2.373 rows=1,450 loops=1)

11. 1.419 2.175 ↑ 1.0 1,450 1

Sort (cost=150.35..153.97 rows=1,450 width=180) (actual time=2.055..2.175 rows=1,450 loops=1)

  • Sort Key: a.link_id, a.effective_date DESC, a.submission_date DESC, a.updated_at DESC
  • Sort Method: quicksort Memory: 252kB
12. 0.504 0.756 ↑ 1.0 1,450 1

Hash Join (cost=9.77..74.21 rows=1,450 width=180) (actual time=0.102..0.756 rows=1,450 loops=1)

  • Hash Cond: (a.link_id = b.id)
13. 0.165 0.165 ↑ 1.0 1,450 1

Seq Scan on stg_allocations a (cost=0.00..44.50 rows=1,450 width=68) (actual time=0.005..0.165 rows=1,450 loops=1)

14. 0.050 0.087 ↑ 1.0 212 1

Hash (cost=7.12..7.12 rows=212 width=32) (actual time=0.086..0.087 rows=212 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
15. 0.037 0.037 ↑ 1.0 212 1

Seq Scan on stg_network_links b (cost=0.00..7.12 rows=212 width=32) (actual time=0.005..0.037 rows=212 loops=1)

16. 0.293 0.534 ↑ 1.0 1,450 1

Hash (cost=44.50..44.50 rows=1,450 width=40) (actual time=0.534..0.534 rows=1,450 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 118kB
17. 0.241 0.241 ↑ 1.0 1,450 1

Seq Scan on stg_allocations d (cost=0.00..44.50 rows=1,450 width=40) (actual time=0.002..0.241 rows=1,450 loops=1)