explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UOkl

Settings
# exclusive inclusive rows x rows loops node
1. 1.439 1,821.831 ↑ 1.0 150 1

Limit (cost=135,737.53..136,552.62 rows=150 width=284) (actual time=1,811.874..1,821.831 rows=150 loops=1)

2. 5.535 1,820.392 ↑ 1,551.2 150 1

Nested Loop Left Join (cost=135,737.53..1,400,074.56 rows=232,674 width=284) (actual time=1,811.873..1,820.392 rows=150 loops=1)

3. 0.113 1,814.107 ↑ 1,551.2 150 1

Nested Loop Left Join (cost=135,736.02..1,034,776.38 rows=232,674 width=805) (actual time=1,811.810..1,814.107 rows=150 loops=1)

4. 0.011 1,813.094 ↑ 1,551.2 150 1

Nested Loop Left Join (cost=135,734.51..676,458.42 rows=232,674 width=773) (actual time=1,811.800..1,813.094 rows=150 loops=1)

5. 0.271 1,812.033 ↑ 1,551.2 150 1

Merge Left Join (cost=135,733.00..318,140.46 rows=232,674 width=741) (actual time=1,811.768..1,812.033 rows=150 loops=1)

  • Merge Cond: (i.id = val.bo_instance_id)
6. 0.000 23.708 ↑ 1,551.2 150 1

Merge Left Join (cost=1,971.47..180,504.56 rows=232,674 width=709) (actual time=23.553..23.708 rows=150 loops=1)

  • Merge Cond: (i.id = t.instance_id)
7. 0.000 7.518 ↑ 1,551.2 150 1

Gather Merge (cost=1,000.86..178,900.79 rows=232,674 width=677) (actual time=5.974..7.518 rows=150 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 0.303 9.444 ↑ 1,065.4 91 3

Nested Loop Left Join (cost=0.84..151,044.41 rows=96,948 width=677) (actual time=2.793..3.148 rows=91 loops=3)

9. 8.595 8.595 ↑ 1,065.4 91 3

Parallel Index Scan Backward using bo_instance_pkey on bo_instance i (cost=0.42..66,154.97 rows=96,948 width=57) (actual time=2.777..2.865 rows=91 loops=3)

  • Filter: ((NOT is_deleted) AND (bo_class_id = 7))
  • Rows Removed by Filter: 4303
10. 0.546 0.546 ↑ 1.0 1 273

Index Scan using idx_attrib_touchstone_bo_instance_id on attrib_touchstone attr (cost=0.42..0.87 rows=1 width=628) (actual time=0.002..0.002 rows=1 loops=273)

  • Index Cond: (i.id = bo_instance_id)
11. 0.980 17.317 ↓ 1.1 4,075 1

Sort (cost=970.60..979.50 rows=3,557 width=40) (actual time=17.135..17.317 rows=4,075 loops=1)

  • Sort Key: t.instance_id DESC
  • Sort Method: quicksort Memory: 546kB
12. 0.356 16.337 ↓ 1.1 4,082 1

Subquery Scan on t (cost=636.88..760.80 rows=3,557 width=40) (actual time=5.227..16.337 rows=4,082 loops=1)

13. 10.153 15.981 ↓ 1.1 4,082 1

GroupAggregate (cost=636.88..725.23 rows=3,557 width=40) (actual time=5.226..15.981 rows=4,082 loops=1)

  • Group Key: x_boi_tag.bo_instance_id
14. 2.225 5.828 ↓ 2.0 11,711 1

Sort (cost=636.88..651.51 rows=5,852 width=282) (actual time=5.208..5.828 rows=11,711 loops=1)

  • Sort Key: x_boi_tag.bo_instance_id
  • Sort Method: quicksort Memory: 1192kB
15. 2.392 3.603 ↓ 2.0 11,711 1

Hash Join (cost=11.30..270.70 rows=5,852 width=282) (actual time=0.079..3.603 rows=11,711 loops=1)

  • Hash Cond: (x_boi_tag.tag_id = tag.id)
16. 1.149 1.149 ↓ 1.0 11,711 1

Seq Scan on x_boi_tag (cost=0.00..227.05 rows=11,705 width=16) (actual time=0.009..1.149 rows=11,711 loops=1)

17. 0.024 0.062 ↓ 2.0 79 1

Hash (cost=10.80..10.80 rows=40 width=282) (actual time=0.061..0.062 rows=79 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
18. 0.038 0.038 ↓ 2.0 79 1

Seq Scan on tag (cost=0.00..10.80 rows=40 width=282) (actual time=0.016..0.038 rows=79 loops=1)

  • Filter: (NOT is_deleted)
19. 73.120 1,788.054 ↑ 53.1 4,281 1

Sort (cost=133,761.53..134,330.27 rows=227,494 width=40) (actual time=1,787.672..1,788.054 rows=4,281 loops=1)

  • Sort Key: val.bo_instance_id DESC
  • Sort Method: external merge Disk: 32936kB
20. 16.405 1,714.934 ↓ 1.0 231,989 1

Subquery Scan on val (cost=95,487.51..107,296.72 rows=227,494 width=40) (actual time=563.011..1,714.934 rows=231,989 loops=1)

21. 1,080.670 1,698.529 ↓ 1.0 231,989 1

GroupAggregate (cost=95,487.51..105,021.78 rows=227,494 width=40) (actual time=563.010..1,698.529 rows=231,989 loops=1)

  • Group Key: x.bo_instance_id
22. 344.160 617.859 ↓ 1.0 689,794 1

Sort (cost=95,487.51..97,160.16 rows=669,060 width=20) (actual time=562.995..617.859 rows=689,794 loops=1)

  • Sort Key: x.bo_instance_id
  • Sort Method: external merge Disk: 21584kB
23. 91.693 273.699 ↓ 1.0 689,794 1

Hash Left Join (cost=13.48..17,026.51 rows=669,060 width=20) (actual time=0.114..273.699 rows=689,794 loops=1)

  • Hash Cond: (v.bo_class_attribute_id = boattr.id)
24. 123.854 181.956 ↓ 1.0 689,794 1

Hash Left Join (cost=3.55..15,226.31 rows=669,060 width=21) (actual time=0.060..181.956 rows=689,794 loops=1)

  • Hash Cond: (x.bo_attribute_value_id = v.id)
25. 58.070 58.070 ↓ 1.0 689,794 1

Seq Scan on x_ia_value_list x (cost=0.00..13,358.60 rows=669,060 width=16) (actual time=0.022..58.070 rows=689,794 loops=1)

26. 0.014 0.032 ↓ 1.2 81 1

Hash (cost=2.69..2.69 rows=69 width=21) (actual time=0.032..0.032 rows=81 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
27. 0.018 0.018 ↓ 1.2 81 1

Seq Scan on bo_attribute_value v (cost=0.00..2.69 rows=69 width=21) (actual time=0.005..0.018 rows=81 loops=1)

28. 0.023 0.050 ↑ 1.2 183 1

Hash (cost=7.19..7.19 rows=219 width=15) (actual time=0.050..0.050 rows=183 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
29. 0.027 0.027 ↑ 1.2 183 1

Seq Scan on bo_class_attribute boattr (cost=0.00..7.19 rows=219 width=15) (actual time=0.004..0.027 rows=183 loops=1)

30. 0.450 1.050 ↑ 1.0 1 150

Aggregate (cost=1.51..1.52 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=150)

31. 0.600 0.600 ↑ 1.0 1 150

Function Scan on json_array_elements (cost=0.00..1.50 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=150)

  • Filter: ((value #>> '{name}'::text[]) = 'format'::text)
  • Rows Removed by Filter: 2
32. 0.300 0.900 ↑ 1.0 1 150

Aggregate (cost=1.51..1.52 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=150)

33. 0.600 0.600 ↑ 1.0 1 150

Function Scan on json_array_elements json_array_elements_1 (cost=0.00..1.50 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=150)

  • Filter: ((value #>> '{name}'::text[]) = 'freq_units'::text)
  • Rows Removed by Filter: 2
34. 0.300 0.750 ↑ 1.0 1 150

Aggregate (cost=1.51..1.52 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=150)

35. 0.450 0.450 ↑ 1.0 1 150

Function Scan on json_array_elements json_array_elements_2 (cost=0.00..1.50 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=150)

  • Filter: ((value #>> '{name}'::text[]) = 'parameter_type'::text)
  • Rows Removed by Filter: 2
Planning time : 0.914 ms
Execution time : 1,828.056 ms