explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ohUN

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 26,167.082 ↑ 1.0 150 1

Limit (cost=1,566,370.22..1,566,385.59 rows=150 width=292) (actual time=26,167.018..26,167.082 rows=150 loops=1)

2. 718.304 26,167.069 ↑ 1,568.3 150 1

WindowAgg (cost=1,566,370.22..1,590,482.42 rows=235,241 width=292) (actual time=26,167.016..26,167.069 rows=150 loops=1)

3. 5,985.544 25,448.765 ↓ 1.0 235,348 1

GroupAggregate (cost=1,566,370.22..1,585,189.50 rows=235,241 width=877) (actual time=19,358.044..25,448.765 rows=235,348 loops=1)

  • Group Key: i.id, attr.data_norm, attr.r_ref, attr.frequency, attr.num_ports, attr.frequency_hz, attr.data
4. 485.593 19,463.221 ↓ 1.0 235,351 1

Sort (cost=1,566,370.22..1,566,958.32 rows=235,241 width=792) (actual time=19,358.011..19,463.221 rows=235,351 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: 167360kB
5. 3,162.106 18,977.628 ↓ 1.0 235,351 1

Nested Loop Left Join (cost=26,181.25..1,381,351.27 rows=235,241 width=792) (actual time=118.841..18,977.628 rows=235,351 loops=1)

  • Join Filter: (i.tags @> ARRAY[t.id])
  • Rows Removed by Join Filter: 18592488
6. 424.189 15,109.478 ↓ 1.0 235,348 1

Hash Right Join (cost=26,181.25..1,102,588.70 rows=235,241 width=713) (actual time=118.796..15,109.478 rows=235,348 loops=1)

  • Hash Cond: (attr.bo_instance_id = i.id)
7. 4,088.100 14,566.697 ↑ 1.0 235,348 1

Nested Loop Left Join (cost=0.00..1,033,930.19 rows=235,525 width=651) (actual time=0.043..14,566.697 rows=235,348 loops=1)

  • Join Filter: (attr.parameter_type32 @> ARRAY[parameter_type32.id])
  • Rows Removed by Join Filter: 22124677
8. 4,099.661 9,772.553 ↑ 1.0 235,348 1

Nested Loop Left Join (cost=0.00..698,303.88 rows=235,525 width=675) (actual time=0.035..9,772.553 rows=235,348 loops=1)

  • Join Filter: (attr.freq_units30 @> ARRAY[freq_units30.id])
  • Rows Removed by Join Filter: 22124677
9. 4,117.121 4,966.848 ↑ 1.0 235,348 1

Nested Loop Left Join (cost=0.00..362,677.56 rows=235,525 width=699) (actual time=0.026..4,966.848 rows=235,348 loops=1)

  • Join Filter: (attr.format33 @> ARRAY[format33.id])
  • Rows Removed by Join Filter: 22124677
10. 143.683 143.683 ↑ 1.0 235,348 1

Seq Scan on attrib_touchstone attr (cost=0.00..27,051.25 rows=235,525 width=723) (actual time=0.008..143.683 rows=235,348 loops=1)

11. 706.034 706.044 ↑ 1.0 95 235,348

Materialize (cost=0.00..3.43 rows=95 width=13) (actual time=0.000..0.003 rows=95 loops=235,348)

12. 0.010 0.010 ↑ 1.0 95 1

Seq Scan on bo_attribute_value format33 (cost=0.00..2.95 rows=95 width=13) (actual time=0.004..0.010 rows=95 loops=1)

13. 706.036 706.044 ↑ 1.0 95 235,348

Materialize (cost=0.00..3.43 rows=95 width=13) (actual time=0.000..0.003 rows=95 loops=235,348)

14. 0.008 0.008 ↑ 1.0 95 1

Seq Scan on bo_attribute_value freq_units30 (cost=0.00..2.95 rows=95 width=13) (actual time=0.002..0.008 rows=95 loops=1)

15. 706.034 706.044 ↑ 1.0 95 235,348

Materialize (cost=0.00..3.43 rows=95 width=13) (actual time=0.000..0.003 rows=95 loops=235,348)

16. 0.010 0.010 ↑ 1.0 95 1

Seq Scan on bo_attribute_value parameter_type32 (cost=0.00..2.95 rows=95 width=13) (actual time=0.002..0.010 rows=95 loops=1)

17. 47.907 118.592 ↓ 1.0 235,348 1

Hash (cost=20,483.74..20,483.74 rows=235,241 width=70) (actual time=118.592..118.592 rows=235,348 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3521kB
18. 70.685 70.685 ↓ 1.0 235,348 1

Seq Scan on bo_instance i (cost=0.00..20,483.74 rows=235,241 width=70) (actual time=0.011..70.685 rows=235,348 loops=1)

  • Filter: ((NOT is_deleted) AND (bo_class_id = 7))
  • Rows Removed by Filter: 13010
19. 706.022 706.044 ↑ 1.0 79 235,348

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

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 : 1.039 ms
Execution time : 26,259.462 ms