explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jGyi

Settings
# exclusive inclusive rows x rows loops node
1. 201.750 59,037.136 ↓ 180.0 10,082 1

Nested Loop Left Join (cost=157.83..28,011.46 rows=56 width=383) (actual time=1.144..59,037.136 rows=10,082 loops=1)

2. 33.032 32,884.318 ↓ 190.2 10,082 1

Nested Loop Left Join (cost=157.12..14,036.70 rows=53 width=377) (actual time=0.922..32,884.318 rows=10,082 loops=1)

3. 27.257 649.378 ↓ 201.6 10,082 1

Nested Loop Left Join (cost=156.40..854.28 rows=50 width=371) (actual time=0.204..649.378 rows=10,082 loops=1)

4. 13.632 581.793 ↓ 201.6 10,082 1

Nested Loop Left Join (cost=156.25..845.02 rows=50 width=343) (actual time=0.195..581.793 rows=10,082 loops=1)

5. 11.731 437.095 ↓ 201.6 10,082 1

Nested Loop (cost=155.96..826.19 rows=50 width=339) (actual time=0.182..437.095 rows=10,082 loops=1)

6. 16.313 385.036 ↓ 201.6 10,082 1

Nested Loop (cost=155.81..815.66 rows=50 width=311) (actual time=0.151..385.036 rows=10,082 loops=1)

7. 17.288 338.477 ↓ 201.6 10,082 1

Nested Loop (cost=155.66..805.13 rows=50 width=283) (actual time=0.126..338.477 rows=10,082 loops=1)

8. 21.959 290.943 ↓ 201.6 10,082 1

Nested Loop (cost=155.51..794.60 rows=50 width=255) (actual time=0.091..290.943 rows=10,082 loops=1)

9. 190.370 208.492 ↓ 201.6 10,082 1

Hash Join (cost=155.35..784.19 rows=50 width=247) (actual time=0.085..208.492 rows=10,082 loops=1)

  • Hash Cond: ((e.endpoint_meta_id = em.id) AND (e.firmware_id = f.id))
10. 18.067 18.067 ↓ 1.0 10,082 1

Seq Scan on endpoint e (cost=0.00..529.17 rows=9,917 width=187) (actual time=0.004..18.067 rows=10,082 loops=1)

11. 0.003 0.055 ↑ 913.8 4 1

Hash (cost=100.53..100.53 rows=3,655 width=76) (actual time=0.055..0.055 rows=4 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 33kB
12. 0.015 0.052 ↑ 913.8 4 1

Hash Join (cost=60.56..100.53 rows=3,655 width=76) (actual time=0.049..0.052 rows=4 loops=1)

  • Hash Cond: (em.endpoint_software_version_id = esv.id)
13. 0.003 0.003 ↑ 89.5 19 1

Seq Scan on endpoint_meta em (cost=0.00..27.00 rows=1,700 width=12) (actual time=0.002..0.003 rows=19 loops=1)

14. 0.006 0.034 ↑ 430.0 1 1

Hash (cost=55.19..55.19 rows=430 width=76) (actual time=0.034..0.034 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.015 0.028 ↑ 430.0 1 1

Hash Join (cost=34.98..55.19 rows=430 width=76) (actual time=0.028..0.028 rows=1 loops=1)

  • Hash Cond: (f.endpoint_software_version_id = esv.id)
16. 0.002 0.002 ↑ 430.0 1 1

Seq Scan on firmware f (cost=0.00..14.30 rows=430 width=72) (actual time=0.001..0.002 rows=1 loops=1)

17. 0.008 0.011 ↑ 138.8 8 1

Hash (cost=21.10..21.10 rows=1,110 width=4) (actual time=0.011..0.011 rows=8 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 17kB
18. 0.003 0.003 ↑ 138.8 8 1

Seq Scan on endpoint_software_version esv (cost=0.00..21.10 rows=1,110 width=4) (actual time=0.002..0.003 rows=8 loops=1)

19. 60.492 60.492 ↑ 1.0 1 10,082

Index Scan using pk_endpoint_class on endpoint_class ec (cost=0.15..0.20 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=10,082)

  • Index Cond: (id = em.endpoint_class_id)
20. 30.246 30.246 ↑ 1.0 1 10,082

Index Scan using pk_endpoint_family_type on endpoint_family_type eft (cost=0.15..0.20 rows=1 width=36) (actual time=0.002..0.003 rows=1 loops=10,082)

  • Index Cond: (id = ec.endpoint_family_type_id)
21. 30.246 30.246 ↑ 1.0 1 10,082

Index Scan using pk_endpoint_device_type on endpoint_device_type edt (cost=0.15..0.20 rows=1 width=36) (actual time=0.002..0.003 rows=1 loops=10,082)

  • Index Cond: (id = ec.endpoint_device_type_id)
22. 40.328 40.328 ↑ 1.0 1 10,082

Index Scan using pk_endpoint_display_type on endpoint_display_type edis (cost=0.15..0.20 rows=1 width=36) (actual time=0.003..0.004 rows=1 loops=10,082)

  • Index Cond: (id = ec.endpoint_display_type_id)
23. 131.066 131.066 ↑ 1.0 1 10,082

Index Scan using uq_autoconfig_assignment_endpoint_id on autoconfig_assignment a (cost=0.29..0.37 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=10,082)

  • Index Cond: (e.id = endpoint_id)
24. 40.328 40.328 ↑ 1.0 1 10,082

Index Scan using pk_autoconfig_status_type on autoconfig_status_type t (cost=0.15..0.18 rows=1 width=36) (actual time=0.003..0.004 rows=1 loops=10,082)

  • Index Cond: (a.autoconfig_status_type_id = id)
25. 2,893.534 32,201.908 ↑ 1.0 1 10,082

Nested Loop (cost=0.71..263.64 rows=1 width=10) (actual time=2.420..3.194 rows=1 loops=10,082)

26. 12,370.614 12,370.614 ↑ 1.3 560 10,082

Index Scan using idx_endpoint_property_endpoint_id on endpoint_property epp_1 (cost=0.43..41.33 rows=711 width=14) (actual time=0.367..1.227 rows=560 loops=10,082)

  • Index Cond: (endpoint_id = e.id)
27. 16,937.760 16,937.760 ↓ 0.0 0 5,645,920

Index Scan using pk_property_meta on property_meta pm_1 (cost=0.28..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=5,645,920)

  • Index Cond: (id = epp_1.property_meta_id)
  • Filter: ((path)::text = 'urn:pcoip:firmware/pcoip_processor_state/serial'::text)
  • Rows Removed by Filter: 1
28. 3,085.092 25,951.068 ↑ 1.0 1 10,082

Nested Loop (cost=0.71..263.64 rows=1 width=10) (actual time=0.820..2.574 rows=1 loops=10,082)

29. 5,928.216 5,928.216 ↑ 1.3 560 10,082

Index Scan using idx_endpoint_property_endpoint_id on endpoint_property epp (cost=0.43..41.33 rows=711 width=14) (actual time=0.012..0.588 rows=560 loops=10,082)

  • Index Cond: (endpoint_id = e.id)
30. 16,937.760 16,937.760 ↓ 0.0 0 5,645,920

Index Scan using pk_property_meta on property_meta pm (cost=0.28..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=5,645,920)

  • Index Cond: (id = epp.property_meta_id)
  • Filter: ((path)::text = 'urn:pcoip:identity/software_build_state/build_id'::text)
  • Rows Removed by Filter: 1
Planning time : 13.712 ms
Execution time : 59,040.741 ms