explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ADwu

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.016 26,756.189 ↑ 1.0 150 1

Limit (cost=1,565,703.13..1,565,718.51 rows=150 width=292) (actual time=26,756.106..26,756.189 rows=150 loops=1)

2. 753.968 26,756.173 ↑ 1,566.1 150 1

WindowAgg (cost=1,565,703.13..1,589,781.92 rows=234,915 width=292) (actual time=26,756.104..26,756.173 rows=150 loops=1)

3. 6,158.226 26,002.205 ↓ 1.0 235,348 1

GroupAggregate (cost=1,565,703.13..1,584,496.33 rows=234,915 width=877) (actual time=19,729.808..26,002.205 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. 549.835 19,843.979 ↓ 1.0 235,351 1

Sort (cost=1,565,703.13..1,566,290.42 rows=234,915 width=792) (actual time=19,729.752..19,843.979 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: 167376kB
5. 3,263.598 19,294.144 ↓ 1.0 235,351 1

Nested Loop Left Join (cost=26,166.91..1,380,946.63 rows=234,915 width=792) (actual time=121.251..19,294.144 rows=235,351 loops=1)

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

Hash Right Join (cost=26,166.91..1,102,570.36 rows=234,915 width=713) (actual time=121.204..15,324.502 rows=235,348 loops=1)

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

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

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

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

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

Nested Loop Left Join (cost=0.00..362,677.56 rows=235,525 width=699) (actual time=0.022..5,045.885 rows=235,348 loops=1)

  • Join Filter: (attr.format33 @> ARRAY[format33.id])
  • Rows Removed by Join Filter: 22124677
10. 154.017 154.017 ↑ 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.007..154.017 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.003..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.001..0.008 rows=95 loops=1)

15. 706.033 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.011 0.011 ↑ 1.0 95 1

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

17. 48.272 120.466 ↓ 1.0 235,348 1

Hash (cost=20,477.47..20,477.47 rows=234,915 width=70) (actual time=120.466..120.466 rows=235,348 loops=1)

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

Seq Scan on bo_instance i (cost=0.00..20,477.47 rows=234,915 width=70) (actual time=0.009..72.194 rows=235,348 loops=1)

  • Filter: ((NOT is_deleted) AND (bo_class_id = 7))
  • Rows Removed by Filter: 13010
19. 706.021 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.023 0.023 ↑ 1.0 79 1

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

Planning time : 0.804 ms
Execution time : 26,860.847 ms