explain.depesz.com

PostgreSQL's explain analyze made readable

Result: scPK

Settings
# exclusive inclusive rows x rows loops node
1. 1.268 58,630.126 ↓ 7,501.0 7,501 1

Subquery Scan on vm (cost=71,181.60..71,181.62 rows=1 width=89) (actual time=58,608.833..58,630.126 rows=7,501 loops=1)

2. 19.304 58,628.858 ↓ 7,501.0 7,501 1

Limit (cost=71,181.60..71,181.61 rows=1 width=89) (actual time=58,608.830..58,628.858 rows=7,501 loops=1)

3. 0.000 58,609.554 ↓ 7,501.0 7,501 1

Sort (cost=71,181.60..71,181.61 rows=1 width=89) (actual time=58,608.829..58,609.554 rows=7,501 loops=1)

  • Sort Key: m.start_time DESC
  • Sort Method: quicksort Memory: 3561kB
4. 59.054 58,613.848 ↓ 11,960.0 11,960 1

Gather (cost=64,956.44..71,181.59 rows=1 width=89) (actual time=56,774.201..58,613.848 rows=11,960 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 1,671.758 58,554.794 ↓ 5,980.0 5,980 2

Nested Loop Semi Join (cost=63,956.44..70,181.49 rows=1 width=89) (actual time=56,738.026..58,554.794 rows=5,980 loops=2)

6. 50.634 56,882.758 ↓ 598.0 5,980 2

Nested Loop (cost=63,955.87..70,173.60 rows=10 width=97) (actual time=56,726.481..56,882.758 rows=5,980 loops=2)

  • Join Filter: (measurements.measurement_id = m.measurement_id)
7. 14.856 56,832.117 ↓ 598.0 5,980 2

Parallel Hash Join (cost=63,955.44..70,110.56 rows=10 width=8) (actual time=56,718.762..56,832.117 rows=5,980 loops=2)

  • Hash Cond: (measurements.measurement_id = measurement_files_1.measurement_id)
8. 99.457 99.457 ↑ 1.1 14,095 2

Parallel Index Scan using idx_measurements_start_time on measurements (cost=0.44..6,095.45 rows=16,018 width=4) (actual time=0.715..99.457 rows=14,095 loops=2)

  • Index Cond: ((start_time > '2019-02-04 00:00:00'::timestamp without time zone) AND (start_time < '2019-02-11 00:00:00'::timestamp without time zone))
9. 115.108 56,717.804 ↓ 105.4 259,488 2

Parallel Hash (cost=63,924.23..63,924.23 rows=2,462 width=4) (actual time=56,717.804..56,717.804 rows=259,488 loops=2)

  • Buckets: 524288 (originally 8192) Batches: 1 (originally 1) Memory Usage: 28480kB
10. 55,313.301 56,602.696 ↓ 105.4 259,488 2

Nested Loop (cost=21,787.10..63,924.23 rows=2,462 width=4) (actual time=52.032..56,602.696 rows=259,488 loops=2)

11. 1,219.350 1,289.182 ↓ 105.4 259,488 2

Parallel Bitmap Heap Scan on custom_data (cost=21,786.54..43,020.81 rows=2,462 width=4) (actual time=51.984..1,289.182 rows=259,488 loops=2)

  • Recheck Cond: (key = 'Wetter'::text)
  • Filter: (value ~~ 'kalt'::text)
  • Heap Blocks: exact=4624
12. 0.272 69.832 ↓ 0.0 0 1

BitmapAnd (cost=21,786.54..21,786.54 rows=5,908 width=0) (actual time=69.832..69.832 rows=0 loops=1)

13. 36.238 36.238 ↓ 1.1 518,976 1

Bitmap Index Scan on idx_custom_data_value (cost=0.00..10,891.67 rows=477,747 width=0) (actual time=36.238..36.238 rows=518,976 loops=1)

  • Index Cond: (value = 'kalt'::text)
14. 33.322 33.322 ↓ 1.1 518,976 1

Bitmap Index Scan on idx_custom_data_key (cost=0.00..10,891.67 rows=477,747 width=0) (actual time=33.322..33.322 rows=518,976 loops=1)

  • Index Cond: (key = 'Wetter'::text)
15. 0.213 0.213 ↑ 1.0 1 518,976

Index Scan using pkey_measurement_files on measurement_files measurement_files_1 (cost=0.56..8.49 rows=1 width=8) (actual time=0.213..0.213 rows=1 loops=518,976)

  • Index Cond: (file_id = custom_data.file_id)
16. 0.007 0.007 ↑ 1.0 1 11,960

Index Scan using pkey_measurements on measurements m (cost=0.44..6.29 rows=1 width=89) (actual time=0.007..0.007 rows=1 loops=11,960)

  • Index Cond: (measurement_id = measurement_files_1.measurement_id)
17. 0.278 0.278 ↑ 196.0 1 11,960

Index Scan using idx_measurement_files_measurement_id on measurement_files (cost=0.56..37.19 rows=196 width=4) (actual time=0.278..0.278 rows=1 loops=11,960)

  • Index Cond: (measurement_id = measurements.measurement_id)
  • Filter: (mount_point_id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))