explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HIVo : Optimization for: Optimization for: plan #H833; plan #sJ4D

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 60.125 2,006.415 ↓ 3.5 31,230 1

Gather (cost=1,172.53..71,832.14 rows=8,996 width=2,178) (actual time=10.847..2,006.415 rows=31,230 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 14.362 1,946.290 ↓ 2.8 10,410 3

Hash Left Join (cost=172.52..69,932.54 rows=3,748 width=2,178) (actual time=51.043..1,946.290 rows=10,410 loops=3)

  • Hash Cond: ((devicedata0_.owner_eid)::text = (usergroupd6_.eid)::text)
3. 12.003 1,931.271 ↓ 2.8 10,410 3

Hash Left Join (cost=117.02..69,867.17 rows=3,748 width=2,137) (actual time=50.369..1,931.271 rows=10,410 loops=3)

  • Hash Cond: ((devicedata0_.owner_eid)::text = (userdata5_.eid)::text)
4. 292.215 1,918.247 ↓ 2.8 10,410 3

Nested Loop Left Join (cost=13.99..69,754.28 rows=3,748 width=1,789) (actual time=49.335..1,918.247 rows=10,410 loops=3)

5. 9.344 1,626.006 ↓ 2.8 10,410 3

Hash Left Join (cost=13.57..67,624.38 rows=3,748 width=1,645) (actual time=49.284..1,626.006 rows=10,410 loops=3)

  • Hash Cond: ((devicedata0_.active_software_version)::text = (devicesoft3_.eid)::text)
6. 12.624 1,616.573 ↓ 2.8 10,410 3

Hash Left Join (cost=6.62..67,607.58 rows=3,748 width=1,475) (actual time=49.184..1,616.573 rows=10,410 loops=3)

  • Hash Cond: ((devicedata0_.data_profile_ref)::text = (dataprofil2_.eid)::text)
7. 142.996 1,603.847 ↓ 2.8 10,410 3

Merge Left Join (cost=1.26..67,591.98 rows=3,748 width=1,353) (actual time=48.939..1,603.847 rows=10,410 loops=3)

  • Merge Cond: (devicedata0_.production_device_id = production1_.id)
  • Filter: ((lower((devicemeta7_.device_name)::text) ~~ '%218%'::text) OR ((devicedata0_.eid)::text = '218'::text) OR ((production1_.common_name)::text = '218'::text) OR (lower((production1_.serial_number)::text) ~~ '%218%'::text))
  • Rows Removed by Filter: 39143
8. 1,254.422 1,331.384 ↓ 1.0 49,553 3

Nested Loop Left Join (cost=0.84..37,873.90 rows=47,804 width=665) (actual time=0.113..1,331.384 rows=49,553 loops=3)

9. 76.939 76.939 ↓ 1.0 49,553 3

Parallel Index Scan using idx_device_productiondeviceid on t_device devicedata0_ (cost=0.42..10,759.62 rows=47,804 width=239) (actual time=0.028..76.939 rows=49,553 loops=3)

  • Filter: ((next IS NULL) AND ((registration_time IS NOT NULL) OR (registration_time IS NULL)))
  • Rows Removed by Filter: 7969
10. 0.023 0.023 ↑ 1.0 1 148,660

Index Scan using t_device_metadata_pkey on t_device_metadata devicemeta7_ (cost=0.42..0.57 rows=1 width=426) (actual time=0.023..0.023 rows=1 loops=148,660)

  • Index Cond: ((eid)::text = (devicedata0_.eid)::text)
11. 129.467 129.467 ↑ 1.0 148,173 3

Index Scan using t_production_device_pkey on t_production_device production1_ (cost=0.42..28,031.82 rows=148,660 width=688) (actual time=3.424..129.467 rows=148,173 loops=3)

12. 0.041 0.102 ↑ 1.0 105 3

Hash (cost=4.05..4.05 rows=105 width=122) (actual time=0.101..0.102 rows=105 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
13. 0.061 0.061 ↑ 1.0 105 3

Seq Scan on t_dataprofile dataprofil2_ (cost=0.00..4.05 rows=105 width=122) (actual time=0.040..0.061 rows=105 loops=3)

14. 0.045 0.089 ↑ 1.0 131 3

Hash (cost=5.31..5.31 rows=131 width=170) (actual time=0.089..0.089 rows=131 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
15. 0.044 0.044 ↑ 1.0 131 3

Seq Scan on t_device_sw_version devicesoft3_ (cost=0.00..5.31 rows=131 width=170) (actual time=0.021..0.044 rows=131 loops=3)

16. 0.026 0.026 ↑ 1.0 1 31,230

Index Scan using t_device_runtime_pkey on t_device_runtime devicerunt4_ (cost=0.42..0.57 rows=1 width=144) (actual time=0.026..0.026 rows=1 loops=31,230)

  • Index Cond: ((devicedata0_.eid)::text = (device_eid)::text)
17. 0.563 1.021 ↓ 1.0 1,700 3

Hash (cost=81.90..81.90 rows=1,690 width=348) (actual time=1.021..1.021 rows=1,700 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 532kB
18. 0.458 0.458 ↓ 1.0 1,700 3

Seq Scan on t_user userdata5_ (cost=0.00..81.90 rows=1,690 width=348) (actual time=0.018..0.458 rows=1,700 loops=3)

19. 0.407 0.657 ↓ 1.0 1,757 3

Hash (cost=33.56..33.56 rows=1,756 width=41) (actual time=0.657..0.657 rows=1,757 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 144kB
20. 0.250 0.250 ↓ 1.0 1,757 3

Seq Scan on t_usergroup usergroupd6_ (cost=0.00..33.56 rows=1,756 width=41) (actual time=0.026..0.250 rows=1,757 loops=3)

Planning time : 9.655 ms
Execution time : 2,008.421 ms