explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ndg8

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 25,618.493 ↑ 1.0 150 1

Limit (cost=1,588,536.98..1,588,552.36 rows=150 width=292) (actual time=25,618.291..25,618.493 rows=150 loops=1)

2. 691.682 25,618.481 ↑ 1,548.0 150 1

WindowAgg (cost=1,588,536.98..1,612,338.10 rows=232,206 width=292) (actual time=25,618.290..25,618.481 rows=150 loops=1)

3. 5,931.830 24,926.799 ↓ 1.0 232,597 1

GroupAggregate (cost=1,588,536.98..1,607,113.46 rows=232,206 width=875) (actual time=18,892.613..24,926.799 rows=232,597 loops=1)

  • Group Key: i.id, attr.data_norm, attr.r_ref, attr.frequency, attr.num_ports, attr.frequency_hz, attr.data
4. 470.445 18,994.969 ↓ 1.0 232,600 1

Sort (cost=1,588,536.98..1,589,117.50 rows=232,206 width=790) (actual time=18,892.513..18,994.969 rows=232,600 loops=1)

  • Sort Key: i.id DESC, attr.data_norm, attr.r_ref, attr.frequency, attr.num_ports, attr.frequency_hz, attr.data
  • Sort Method: external merge Disk: 165944kB
5. 2,997.091 18,524.524 ↓ 1.0 232,600 1

Nested Loop Left Join (cost=25,872.58..1,405,931.57 rows=232,206 width=790) (actual time=105.350..18,524.524 rows=232,600 loops=1)

  • Join Filter: (i.tags @> ARRAY[t.id])
  • Rows Removed by Join Filter: 18375159
6. 330.604 14,829.642 ↓ 1.0 232,597 1

Hash Right Join (cost=25,872.58..1,130,765.47 rows=232,206 width=711) (actual time=105.306..14,829.642 rows=232,597 loops=1)

  • Hash Cond: (attr.bo_instance_id = i.id)
7. 3,813.347 14,393.910 ↑ 1.0 232,597 1

Nested Loop Left Join (cost=0.00..1,062,944.32 rows=232,597 width=649) (actual time=0.037..14,393.910 rows=232,597 loops=1)

  • Join Filter: (attr.parameter_type32 @> ARRAY[parameter_type32.id])
  • Rows Removed by Join Filter: 22794506
8. 3,867.664 9,650.175 ↑ 1.0 232,597 1

Nested Loop Left Join (cost=0.00..717,534.54 rows=232,597 width=673) (actual time=0.030..9,650.175 rows=232,597 loops=1)

  • Join Filter: (attr.freq_units30 @> ARRAY[freq_units30.id])
  • Rows Removed by Join Filter: 22794506
9. 4,055.451 4,852.123 ↑ 1.0 232,597 1

Nested Loop Left Join (cost=0.00..372,124.75 rows=232,597 width=697) (actual time=0.021..4,852.123 rows=232,597 loops=1)

  • Join Filter: (attr.format33 @> ARRAY[format33.id])
  • Rows Removed by Join Filter: 22794506
10. 98.881 98.881 ↑ 1.0 232,597 1

Seq Scan on attrib_touchstone attr (cost=0.00..26,714.97 rows=232,597 width=721) (actual time=0.006..98.881 rows=232,597 loops=1)

11. 697.780 697.791 ↑ 1.0 99 232,597

Materialize (cost=0.00..3.49 rows=99 width=13) (actual time=0.000..0.003 rows=99 loops=232,597)

12. 0.011 0.011 ↑ 1.0 99 1

Seq Scan on bo_attribute_value format33 (cost=0.00..2.99 rows=99 width=13) (actual time=0.004..0.011 rows=99 loops=1)

13. 930.380 930.388 ↑ 1.0 99 232,597

Materialize (cost=0.00..3.49 rows=99 width=13) (actual time=0.000..0.004 rows=99 loops=232,597)

14. 0.008 0.008 ↑ 1.0 99 1

Seq Scan on bo_attribute_value freq_units30 (cost=0.00..2.99 rows=99 width=13) (actual time=0.001..0.008 rows=99 loops=1)

15. 930.377 930.388 ↑ 1.0 99 232,597

Materialize (cost=0.00..3.49 rows=99 width=13) (actual time=0.000..0.004 rows=99 loops=232,597)

16. 0.011 0.011 ↑ 1.0 99 1

Seq Scan on bo_attribute_value parameter_type32 (cost=0.00..2.99 rows=99 width=13) (actual time=0.001..0.011 rows=99 loops=1)

17. 42.249 105.128 ↓ 1.0 232,597 1

Hash (cost=20,248.00..20,248.00 rows=232,206 width=70) (actual time=105.128..105.128 rows=232,597 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3484kB
18. 62.879 62.879 ↓ 1.0 232,597 1

Seq Scan on bo_instance i (cost=0.00..20,248.00 rows=232,206 width=70) (actual time=0.012..62.879 rows=232,597 loops=1)

  • Filter: ((NOT is_deleted) AND (bo_class_id = 7))
  • Rows Removed by Filter: 13003
19. 697.769 697.791 ↑ 1.0 79 232,597

Materialize (cost=0.00..2.19 rows=79 width=100) (actual time=0.000..0.003 rows=79 loops=232,597)

20. 0.022 0.022 ↑ 1.0 79 1

Seq Scan on tag t (cost=0.00..1.79 rows=79 width=100) (actual time=0.007..0.022 rows=79 loops=1)

Planning time : 0.550 ms
Execution time : 25,708.729 ms