explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ppfp

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

Limit (cost=3,283,517.53..3,283,532.90 rows=150 width=292) (actual time=25,520.698..25,520.754 rows=150 loops=1)

2. 634.006 25,520.742 ↑ 4,604.5 150 1

WindowAgg (cost=3,283,517.53..3,354,312.02 rows=690,678 width=292) (actual time=25,520.696..25,520.742 rows=150 loops=1)

3. 5,562.896 24,886.736 ↑ 3.0 232,597 1

GroupAggregate (cost=3,283,517.53..3,338,771.77 rows=690,678 width=877) (actual time=19,236.281..24,886.736 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. 422.886 19,323.840 ↑ 3.0 232,600 1

Sort (cost=3,283,517.53..3,285,244.22 rows=690,678 width=792) (actual time=19,236.223..19,323.840 rows=232,600 loops=1)

  • Sort Key: i.id, attr.data_norm, attr.r_ref, attr.frequency, attr.num_ports, attr.frequency_hz, attr.data
  • Sort Method: external merge Disk: 165928kB
5. 2,980.214 18,900.954 ↑ 3.0 232,600 1

Nested Loop Left Join (cost=79,333.40..2,734,936.86 rows=690,678 width=792) (actual time=424.678..18,900.954 rows=232,600 loops=1)

  • Join Filter: (i.tags @> ARRAY[t.id])
  • Rows Removed by Join Filter: 18375159
6. 361.179 15,222.949 ↑ 3.0 232,597 1

Hash Right Join (cost=79,333.40..1,916,481.45 rows=690,678 width=732) (actual time=424.605..15,222.949 rows=232,597 loops=1)

  • Hash Cond: (attr.bo_instance_id = i.id)
7. 4,010.860 14,451.008 ↑ 2.0 232,597 1

Nested Loop Left Join (cost=0.00..1,748,580.94 rows=465,180 width=651) (actual time=0.499..14,451.008 rows=232,597 loops=1)

  • Join Filter: (attr.parameter_type32 @> ARRAY[parameter_type32.id])
  • Rows Removed by Join Filter: 21864118
8. 4,004.142 9,742.357 ↑ 2.0 232,597 1

Nested Loop Left Join (cost=0.00..1,183,384.23 rows=465,180 width=678) (actual time=0.486..9,742.357 rows=232,597 loops=1)

  • Join Filter: (attr.freq_units30 @> ARRAY[freq_units30.id])
  • Rows Removed by Join Filter: 21864118
9. 3,836.219 5,040.424 ↑ 2.0 232,597 1

Nested Loop Left Join (cost=0.00..618,187.51 rows=465,180 width=705) (actual time=0.475..5,040.424 rows=232,597 loops=1)

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

Seq Scan on attrib_touchstone attr (cost=0.00..52,990.80 rows=465,180 width=732) (actual time=0.451..273.817 rows=232,597 loops=1)

11. 930.375 930.388 ↓ 1.2 95 232,597

Materialize (cost=0.00..3.21 rows=81 width=13) (actual time=0.000..0.004 rows=95 loops=232,597)

12. 0.013 0.013 ↓ 1.2 95 1

Seq Scan on bo_attribute_value format33 (cost=0.00..2.81 rows=81 width=13) (actual time=0.007..0.013 rows=95 loops=1)

13. 697.782 697.791 ↓ 1.2 95 232,597

Materialize (cost=0.00..3.21 rows=81 width=13) (actual time=0.000..0.003 rows=95 loops=232,597)

14. 0.009 0.009 ↓ 1.2 95 1

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

15. 697.781 697.791 ↓ 1.2 95 232,597

Materialize (cost=0.00..3.21 rows=81 width=13) (actual time=0.000..0.003 rows=95 loops=232,597)

16. 0.010 0.010 ↓ 1.2 95 1

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

17. 50.376 410.762 ↑ 3.0 232,597 1

Hash (cost=60,581.93..60,581.93 rows=690,678 width=89) (actual time=410.762..410.762 rows=232,597 loops=1)

  • Buckets: 32768 Batches: 32 Memory Usage: 1000kB
18. 360.386 360.386 ↑ 3.0 232,597 1

Seq Scan on bo_instance i (cost=0.00..60,581.93 rows=690,678 width=89) (actual time=0.087..360.386 rows=232,597 loops=1)

  • Filter: ((NOT is_deleted) AND (bo_class_id = 7))
  • Rows Removed by Filter: 13003
19. 697.747 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.044 0.044 ↑ 1.0 79 1

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

Planning time : 2.705 ms
Execution time : 25,606.922 ms