explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rDsd

Settings
# exclusive inclusive rows x rows loops node
1. 1.647 1,798.994 ↑ 1.0 150 1

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

2. 5.652 1,797.347 ↑ 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,788.829..1,797.347 rows=150 loops=1)

3. 0.117 1,790.945 ↑ 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,788.767..1,790.945 rows=150 loops=1)

4. 0.037 1,790.078 ↑ 1,551.2 150 1

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

5. 0.245 1,788.991 ↑ 1,551.2 150 1

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

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

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
8. 0.072 10.164 ↑ 1,031.4 94 3

Nested Loop Left Join (cost=0.84..151,044.41 rows=96,948 width=677) (actual time=2.985..3.388 rows=94 loops=3)

9. 9.249 9.249 ↑ 1,031.4 94 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.964..3.083 rows=94 loops=3)

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

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.003 rows=1 loops=281)

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

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

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

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

13. 17.249 25.825 ↓ 1.1 4,082 1

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

  • Group Key: x_boi_tag.bo_instance_id
14. 3.350 8.576 ↓ 2.0 11,711 1

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

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

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

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

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

17. 0.019 0.043 ↓ 2.0 79 1

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

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

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

  • Filter: (NOT is_deleted)
19. 72.517 1,754.140 ↑ 53.1 4,281 1

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

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

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

21. 1,061.987 1,666.019 ↓ 1.0 231,989 1

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

  • Group Key: x.bo_instance_id
22. 335.232 604.032 ↓ 1.0 689,794 1

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

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

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

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

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

  • Hash Cond: (x.bo_attribute_value_id = v.id)
25. 56.735 56.735 ↓ 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.019..56.735 rows=689,794 loops=1)

26. 0.016 0.040 ↓ 1.2 81 1

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

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

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

28. 0.034 0.073 ↑ 1.2 183 1

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

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

Seq Scan on bo_class_attribute boattr (cost=0.00..7.19 rows=219 width=15) (actual time=0.006..0.039 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.007..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.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)

33. 0.450 0.450 ↑ 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.003 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