#	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 / 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 / 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.	21.581	1,918.247	↓ 2.8	10,410	3 / 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 / 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 / 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 / 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: 39,143
8.	114.718	1,331.384	↓ 1.0	49,553	3 / 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 / 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: 7,969
10.	1,139.727	1,139.727	↑ 1.0	1	148,660 / 3	→ 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 / 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 / 3	→ Hash (cost=4.05..4.05 rows=105 width=122) (actual time=0.101..0.102 rows=105 loops=3) Buckets: 1,024 Batches: 1 Memory Usage: 24kB
13.	0.061	0.061	↑ 1.0	105	3 / 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 / 3	→ Hash (cost=5.31..5.31 rows=131 width=170) (actual time=0.089..0.089 rows=131 loops=3) Buckets: 1,024 Batches: 1 Memory Usage: 35kB
15.	0.044	0.044	↑ 1.0	131	3 / 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.	270.660	270.660	↑ 1.0	1	31,230 / 3	→ 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 / 3	→ Hash (cost=81.90..81.90 rows=1,690 width=348) (actual time=1.021..1.021 rows=1,700 loops=3) Buckets: 2,048 Batches: 1 Memory Usage: 532kB
18.	0.458	0.458	↓ 1.0	1,700	3 / 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 / 3	→ Hash (cost=33.56..33.56 rows=1,756 width=41) (actual time=0.657..0.657 rows=1,757 loops=3) Buckets: 2,048 Batches: 1 Memory Usage: 144kB
20.	0.250	0.250	↓ 1.0	1,757	3 / 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

Gather  (cost=1172.53..71832.14 rows=8996 width=2178) (actual time=10.847..2006.415 rows=31230 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Hash Left Join  (cost=172.52..69932.54 rows=3748 width=2178) (actual time=51.043..1946.290 rows=10410 loops=3)
        Hash Cond: ((devicedata0_.owner_eid)::text = (usergroupd6_.eid)::text)
        ->  Hash Left Join  (cost=117.02..69867.17 rows=3748 width=2137) (actual time=50.369..1931.271 rows=10410 loops=3)
              Hash Cond: ((devicedata0_.owner_eid)::text = (userdata5_.eid)::text)
              ->  Nested Loop Left Join  (cost=13.99..69754.28 rows=3748 width=1789) (actual time=49.335..1918.247 rows=10410 loops=3)
                    ->  Hash Left Join  (cost=13.57..67624.38 rows=3748 width=1645) (actual time=49.284..1626.006 rows=10410 loops=3)
                          Hash Cond: ((devicedata0_.active_software_version)::text = (devicesoft3_.eid)::text)
                          ->  Hash Left Join  (cost=6.62..67607.58 rows=3748 width=1475) (actual time=49.184..1616.573 rows=10410 loops=3)
                                Hash Cond: ((devicedata0_.data_profile_ref)::text = (dataprofil2_.eid)::text)
                                ->  Merge Left Join  (cost=1.26..67591.98 rows=3748 width=1353) (actual time=48.939..1603.847 rows=10410 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
                                      ->  Nested Loop Left Join  (cost=0.84..37873.90 rows=47804 width=665) (actual time=0.113..1331.384 rows=49553 loops=3)
                                            ->  Parallel Index Scan using idx_device_productiondeviceid on t_device devicedata0_  (cost=0.42..10759.62 rows=47804 width=239) (actual time=0.028..76.939 rows=49553 loops=3)
                                                  Filter: ((next IS NULL) AND ((registration_time IS NOT NULL) OR (registration_time IS NULL)))
                                                  Rows Removed by Filter: 7969
                                            ->  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=148660)
                                                  Index Cond: ((eid)::text = (devicedata0_.eid)::text)
                                      ->  Index Scan using t_production_device_pkey on t_production_device production1_  (cost=0.42..28031.82 rows=148660 width=688) (actual time=3.424..129.467 rows=148173 loops=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
                                      ->  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)
                          ->  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
                                ->  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)
                    ->  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=31230)
                          Index Cond: ((devicedata0_.eid)::text = (device_eid)::text)
              ->  Hash  (cost=81.90..81.90 rows=1690 width=348) (actual time=1.021..1.021 rows=1700 loops=3)
                    Buckets: 2048  Batches: 1  Memory Usage: 532kB
                    ->  Seq Scan on t_user userdata5_  (cost=0.00..81.90 rows=1690 width=348) (actual time=0.018..0.458 rows=1700 loops=3)
        ->  Hash  (cost=33.56..33.56 rows=1756 width=41) (actual time=0.657..0.657 rows=1757 loops=3)
              Buckets: 2048  Batches: 1  Memory Usage: 144kB
              ->  Seq Scan on t_usergroup usergroupd6_  (cost=0.00..33.56 rows=1756 width=41) (actual time=0.026..0.250 rows=1757 loops=3)
Planning time: 9.655 ms
Execution time: 2008.421 ms

Per node type stats

node type	count	sum of times	% of query
Gather	1	60.125 ms	3.0 %
Hash	4	1.056 ms	0.1 %
Hash Left Join	4	48.333 ms	2.4 %
Index Scan	3	1,539.854 ms	76.7 %
Merge Left Join	1	142.996 ms	7.1 %
Nested Loop Left Join	2	136.299 ms	6.8 %
Parallel Index Scan	1	76.939 ms	3.8 %
Seq Scan	4	0.813 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
t_dataprofile	1	0.061 ms	0.0 %
Seq Scan	1	0.061 ms	100.0 %
t_device	1	76.939 ms	3.8 %
Parallel Index Scan	1	76.939 ms	100.0 %
t_device_metadata	1	1,139.727 ms	56.8 %
Index Scan	1	1,139.727 ms	100.0 %
t_device_runtime	1	270.660 ms	13.5 %
Index Scan	1	270.660 ms	100.0 %
t_device_sw_version	1	0.044 ms	0.0 %
Seq Scan	1	0.044 ms	100.0 %
t_production_device	1	129.467 ms	6.5 %
Index Scan	1	129.467 ms	100.0 %
t_user	1	0.458 ms	0.0 %
Seq Scan	1	0.458 ms	100.0 %
t_usergroup	1	0.250 ms	0.0 %
Seq Scan	1	0.250 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

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

Color mode:

Visible columns:

Optimization path:

Per node type stats

Per table stats