explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Tjul

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

Limit (cost=1,566,753.98..1,566,769.35 rows=150 width=292) (actual time=26,179.392..26,179.454 rows=150 loops=1)

2. 737.789 26,179.441 ↑ 1,569.6 150 1

WindowAgg (cost=1,566,753.98..1,590,886.07 rows=235,435 width=292) (actual time=26,179.391..26,179.441 rows=150 loops=1)

3. 6,122.826 25,441.652 ↑ 1.0 235,348 1

GroupAggregate (cost=1,566,753.98..1,585,588.78 rows=235,435 width=877) (actual time=19,205.766..25,441.652 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. 510.121 19,318.826 ↑ 1.0 235,351 1

Sort (cost=1,566,753.98..1,567,342.57 rows=235,435 width=792) (actual time=19,205.730..19,318.826 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,204.871 18,808.705 ↑ 1.0 235,351 1

Nested Loop Left Join (cost=26,180.41..1,381,583.33 rows=235,435 width=792) (actual time=111.588..18,808.705 rows=235,351 loops=1)

  • Join Filter: (i.tags @> ARRAY[t.id])
  • Rows Removed by Join Filter: 18592488
6. 400.168 14,897.790 ↑ 1.0 235,348 1

Hash Right Join (cost=26,180.41..1,102,590.86 rows=235,435 width=713) (actual time=111.546..14,897.790 rows=235,348 loops=1)

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

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

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

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

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

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

  • Join Filter: (attr.format33 @> ARRAY[format33.id])
  • Rows Removed by Join Filter: 22124677
10. 123.914 123.914 ↑ 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.006..123.914 rows=235,348 loops=1)

11. 706.035 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.009 0.009 ↑ 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.009 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.035 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.009 0.009 ↑ 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.009 rows=95 loops=1)

17. 44.949 110.834 ↑ 1.0 235,348 1

Hash (cost=20,477.47..20,477.47 rows=235,435 width=70) (actual time=110.834..110.834 rows=235,348 loops=1)

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

Seq Scan on bo_instance i (cost=0.00..20,477.47 rows=235,435 width=70) (actual time=0.007..65.885 rows=235,348 loops=1)

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

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

Planning time : 0.637 ms
Execution time : 26,274.909 ms