explain.depesz.com

PostgreSQL's explain analyze made readable

Result: prMI

Settings
# exclusive inclusive rows x rows loops node
1. 113.276 27,662.326 ↓ 199.2 10,159 1

Nested Loop Left Join (cost=157.83..23,767.16 rows=51 width=383) (actual time=1.129..27,662.326 rows=10,159 loops=1)

  • Buffers: shared hit=34,596,575 read=111,798
2. 8.579 27,518.573 ↓ 199.2 10,159 1

Nested Loop Left Join (cost=157.68..23,756.46 rows=51 width=355) (actual time=1.110..27,518.573 rows=10,159 loops=1)

  • Buffers: shared hit=34,576,257 read=111,798
3. 8.435 27,449.040 ↓ 199.2 10,159 1

Nested Loop Left Join (cost=157.39..23,737.33 rows=51 width=351) (actual time=1.091..27,449.040 rows=10,159 loops=1)

  • Buffers: shared hit=34,545,831 read=111,663
4. 14.987 14,528.516 ↓ 199.2 10,159 1

Nested Loop Left Join (cost=156.68..12,317.11 rows=51 width=345) (actual time=0.885..14,528.516 rows=10,159 loops=1)

  • Buffers: shared hit=17,281,849 read=87,783
5. 10.436 169.021 ↓ 199.2 10,159 1

Nested Loop (cost=155.96..896.88 rows=51 width=339) (actual time=0.124..169.021 rows=10,159 loops=1)

  • Buffers: shared hit=81,273 read=497
6. 6.724 138.267 ↓ 199.2 10,159 1

Nested Loop (cost=155.81..886.14 rows=51 width=311) (actual time=0.121..138.267 rows=10,159 loops=1)

  • Buffers: shared hit=60,955 read=497
7. 9.854 111.225 ↓ 199.2 10,159 1

Nested Loop (cost=155.66..875.40 rows=51 width=283) (actual time=0.117..111.225 rows=10,159 loops=1)

  • Buffers: shared hit=40,637 read=497
8. 15.032 81.053 ↓ 199.2 10,159 1

Nested Loop (cost=155.51..864.66 rows=51 width=255) (actual time=0.114..81.053 rows=10,159 loops=1)

  • Buffers: shared hit=20,319 read=497
9. 32.655 45.703 ↓ 199.2 10,159 1

Hash Join (cost=155.35..854.04 rows=51 width=247) (actual time=0.108..45.703 rows=10,159 loops=1)

  • Hash Cond: ((e.endpoint_meta_id = em.id) AND (e.firmware_id = f.id))
  • Buffers: shared hit=1 read=497
10. 12.968 12.968 ↑ 1.0 10,159 1

Seq Scan on endpoint e (cost=0.00..596.59 rows=10,159 width=187) (actual time=0.002..12.968 rows=10,159 loops=1)

  • Buffers: shared hit=1 read=494
11. 0.003 0.080 ↑ 913.8 4 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 33kB
  • Buffers: shared read=3
12. 0.010 0.077 ↑ 913.8 4 1

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

  • Hash Cond: (em.endpoint_software_version_id = esv.id)
  • Buffers: shared read=3
13. 0.019 0.019 ↑ 89.5 19 1

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

  • Buffers: shared read=1
14. 0.003 0.048 ↑ 430.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=2
15. 0.014 0.045 ↑ 430.0 1 1

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

  • Hash Cond: (f.endpoint_software_version_id = esv.id)
  • Buffers: shared read=2
16. 0.012 0.012 ↑ 430.0 1 1

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

  • Buffers: shared read=1
17. 0.005 0.019 ↑ 138.8 8 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 17kB
  • Buffers: shared read=1
18. 0.014 0.014 ↑ 138.8 8 1

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

  • Buffers: shared read=1
19. 20.318 20.318 ↑ 1.0 1 10,159

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

  • Index Cond: (id = em.endpoint_class_id)
  • Buffers: shared hit=20,318
20. 20.318 20.318 ↑ 1.0 1 10,159

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.002 rows=1 loops=10,159)

  • Index Cond: (id = ec.endpoint_family_type_id)
  • Buffers: shared hit=20,318
21. 20.318 20.318 ↑ 1.0 1 10,159

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

  • Index Cond: (id = ec.endpoint_device_type_id)
  • Buffers: shared hit=20,318
22. 20.318 20.318 ↑ 1.0 1 10,159

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

  • Index Cond: (id = ec.endpoint_display_type_id)
  • Buffers: shared hit=20,318
23. 0.000 14,344.508 ↑ 1.0 1 10,159

Nested Loop (cost=0.71..223.92 rows=1 width=10) (actual time=1.019..1.412 rows=1 loops=10,159)

  • Buffers: shared hit=17,200,576 read=87,286
24. 3,118.813 3,118.813 ↑ 1.1 560 10,159

Index Scan using idx_endpoint_property_endpoint_id on endpoint_property epp_1 (cost=0.43..40.06 rows=588 width=14) (actual time=0.051..0.307 rows=560 loops=10,159)

  • Index Cond: (endpoint_id = e.id)
  • Buffers: shared hit=113,138 read=87,286
25. 11,378.080 11,378.080 ↓ 0.0 0 5,689,040

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

  • Index Cond: (id = epp_1.property_meta_id)
  • Filter: ((path)::text = 'urn:pcoip:firmware/pcoip_processor_state/serial'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=17,087,438
26. 0.000 12,912.089 ↑ 1.0 1 10,159

Nested Loop (cost=0.71..223.92 rows=1 width=10) (actual time=0.341..1.271 rows=1 loops=10,159)

  • Buffers: shared hit=17,263,982 read=23,880
27. 1,757.507 1,757.507 ↑ 1.1 560 10,159

Index Scan using idx_endpoint_property_endpoint_id on endpoint_property epp (cost=0.43..40.06 rows=588 width=14) (actual time=0.008..0.173 rows=560 loops=10,159)

  • Index Cond: (endpoint_id = e.id)
  • Buffers: shared hit=176,544 read=23,880
28. 11,378.080 11,378.080 ↓ 0.0 0 5,689,040

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

  • Index Cond: (id = epp.property_meta_id)
  • Filter: ((path)::text = 'urn:pcoip:identity/software_build_state/build_id'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=17,087,438
29. 60.954 60.954 ↑ 1.0 1 10,159

Index Scan using uq_autoconfig_assignment_endpoint_id on autoconfig_assignment a (cost=0.29..0.37 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=10,159)

  • Index Cond: (e.id = endpoint_id)
  • Buffers: shared hit=30,426 read=135
30. 30.477 30.477 ↑ 1.0 1 10,159

Index Scan using pk_autoconfig_status_type on autoconfig_status_type t (cost=0.15..0.17 rows=1 width=36) (actual time=0.002..0.003 rows=1 loops=10,159)

  • Index Cond: (a.autoconfig_status_type_id = id)
  • Buffers: shared hit=20,318
Planning time : 7.727 ms
Execution time : 27,664.077 ms