explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VPvV

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 150.349 ↑ 12,155.2 8 1

Hash Right Join (cost=14,666.24..18,985.54 rows=97,242 width=482) (actual time=150.346..150.349 rows=8 loops=1)

  • Hash Cond: ((sh1.sensor_id)::text = (s.sensor_id)::text)
2. 0.005 149.873 ↑ 75,927.0 6 1

Nested Loop (cost=13,842.42..15,480.95 rows=455,562 width=35) (actual time=149.855..149.873 rows=6 loops=1)

3. 82.457 149.832 ↑ 50.0 4 1

HashAggregate (cost=13,842.42..13,844.42 rows=200 width=39) (actual time=149.831..149.832 rows=4 loops=1)

  • Group Key: sensorhistory.sensor_id
4. 16.835 67.375 ↑ 1.0 455,561 1

Append (cost=0.00..11,564.61 rows=455,562 width=39) (actual time=0.003..67.375 rows=455,561 loops=1)

5. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on sensorhistory (cost=0.00..0.00 rows=1 width=524) (actual time=0.000..0.000 rows=0 loops=1)

6. 50.540 50.540 ↑ 1.0 455,561 1

Seq Scan on sensorhistory_0 (cost=0.00..11,564.61 rows=455,561 width=39) (actual time=0.003..50.540 rows=455,561 loops=1)

7. 0.024 0.036 ↑ 1.5 2 4

Append (cost=0.00..8.14 rows=3 width=205) (actual time=0.009..0.009 rows=2 loops=4)

8. 0.000 0.000 ↓ 0.0 0 4

Seq Scan on sensorhistory sh1 (cost=0.00..0.00 rows=1 width=528) (actual time=0.000..0.000 rows=0 loops=4)

  • Filter: ((max(sensorhistory.actiontime)) = actiontime)
9. 0.012 0.012 ↑ 1.0 2 4

Index Scan using sensoryhistory_0_actiontime_ix on sensorhistory_0 sh1_1 (cost=0.42..8.14 rows=2 width=43) (actual time=0.003..0.003 rows=2 loops=4)

  • Index Cond: (actiontime = (max(sensorhistory.actiontime)))
10. 0.004 0.462 ↑ 32.1 8 1

Hash (cost=820.60..820.60 rows=257 width=478) (actual time=0.462..0.462 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
11. 0.011 0.458 ↑ 32.1 8 1

Hash Join (cost=55.17..820.60 rows=257 width=478) (actual time=0.454..0.458 rows=8 loops=1)

  • Hash Cond: (smch.sensor_id = (s.sensor_id)::text)
12. 0.031 0.040 ↑ 32.1 8 1

Bitmap Heap Scan on sensormch smch (cost=8.08..769.98 rows=257 width=49) (actual time=0.038..0.040 rows=8 loops=1)

  • Filter: ((NOT isdeleted) AND (mch_id IS NOT NULL) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (mch_id IS NULL)))
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=1
13. 0.004 0.004 ↑ 34.3 15 1

Bitmap Index Scan on sensormch_isdeleted_ix (cost=0.00..8.02 rows=515 width=0) (actual time=0.004..0.004 rows=15 loops=1)

  • Index Cond: (isdeleted = false)
14.          

SubPlan (for Bitmap Heap Scan)

15. 0.000 0.000 ↓ 0.0 0

Seq Scan on ndmediachannel nd (cost=0.00..1.45 rows=1 width=0) (never executed)

  • Filter: ((NOT isdeleted) AND (smch.mch_id = ndmediachannel_id))
16. 0.005 0.005 ↑ 1.1 17 1

Seq Scan on ndmediachannel nd_1 (cost=0.00..1.36 rows=18 width=8) (actual time=0.003..0.005 rows=17 loops=1)

  • Filter: (NOT isdeleted)
  • Rows Removed by Filter: 19
17. 0.240 0.407 ↑ 1.0 1,204 1

Hash (cost=32.04..32.04 rows=1,204 width=429) (actual time=0.407..0.407 rows=1,204 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 149kB
18. 0.167 0.167 ↑ 1.0 1,204 1

Seq Scan on sensor s (cost=0.00..32.04 rows=1,204 width=429) (actual time=0.002..0.167 rows=1,204 loops=1)

  • Filter: (NOT isdeleted)
Planning time : 0.978 ms
Execution time : 150.565 ms