explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QDfW

Settings
# exclusive inclusive rows x rows loops node
1. 4.276 62.517 ↓ 0.0 0 1

HashAggregate (cost=8,134,749.84..8,134,751.84 rows=200 width=4) (actual time=62.517..62.517 rows=0 loops=1)

  • Group Key: pd.id
  • Filter: bool_and(((COALESCE(extract_custom_prices(pd.custom_prices, jsonb_array_to_text_array((paths.root_level_paths_deadline_by_nns_id -> (p.ninushi_id)::text)), paths.nested_paths_deadline, 0), pd.unit_price) = '0'::double precision) AND (COALESCE(extract_custom_prices(pd.custom_prices, jsonb_array_to_text_array((paths.root_level_paths_deadline_by_nns_id -> (p.ninushi_id)::text)), paths.nested_paths_deadline, 0), pd.unit_price) IS NOT NULL)))
  • Rows Removed by Filter: 14
2.          

CTE paths

3. 57.859 57.859 ↑ 45.5 22 1

Function Scan on get_custom_values_path paths_1 (cost=0.25..25.25 rows=1,000 width=212) (actual time=57.842..57.859 rows=22 loops=1)

4. 0.062 58.241 ↑ 8,178.1 84 1

Hash Join (cost=35.00..7,432,305.94 rows=686,962 width=271) (actual time=57.942..58.241 rows=84 loops=1)

  • Hash Cond: (((unnest(""*VALUES*"".column2)) = paths.market_uid) AND (""*VALUES*"".column1 = paths.uid_child_of_market))
5. 0.106 0.286 ↑ 280,392.9 98 1

ProjectSet (cost=0.00..143,598.82 rows=27,478,500 width=215) (actual time=0.045..0.286 rows=98 loops=1)

6. 0.037 0.180 ↑ 2,803.9 98 1

Nested Loop (cost=0.00..4,145.43 rows=274,785 width=243) (actual time=0.043..0.180 rows=98 loops=1)

7. 0.011 0.101 ↑ 934.6 14 1

Nested Loop (cost=0.00..700.56 rows=13,085 width=207) (actual time=0.030..0.101 rows=14 loops=1)

8. 0.001 0.027 ↑ 1.0 7 1

Append (cost=0.00..23.81 rows=7 width=8) (actual time=0.019..0.027 rows=7 loops=1)

9. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on proposal_panel pp (cost=0.00..0.00 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (proposal_id = 4617)
10. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on proposal_panel pp_1 (cost=0.00..1.38 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: (proposal_id = 4617)
  • Rows Removed by Filter: 30
11. 0.015 0.015 ↓ 1.4 7 1

Index Scan using proposal_panel_proposal_id_idx on proposal_panel pp_2 (cost=0.29..22.43 rows=5 width=8) (actual time=0.007..0.015 rows=7 loops=1)

  • Index Cond: (proposal_id = 4617)
12. 0.014 0.063 ↑ 16.0 2 7

Append (cost=0.00..96.36 rows=32 width=207) (actual time=0.007..0.009 rows=2 loops=7)

13. 0.000 0.000 ↓ 0.0 0 7

Seq Scan on proposal_detail pd (cost=0.00..0.00 rows=1 width=48) (actual time=0.000..0.000 rows=0 loops=7)

  • Filter: (pp.id = proposal_panel_id)
14. 0.014 0.014 ↓ 0.0 0 7

Index Scan using proposal_detail_proposal_panel_id_idx on proposal_detail pd_1 (cost=0.14..3.07 rows=4 width=64) (actual time=0.002..0.002 rows=0 loops=7)

  • Index Cond: (proposal_panel_id = pp.id)
15. 0.035 0.035 ↑ 13.5 2 7

Index Scan using proposal_detail_proposal_panel_id_idx on proposal_detail pd_2 (cost=0.42..93.29 rows=27 width=207) (actual time=0.003..0.005 rows=2 loops=7)

  • Index Cond: (proposal_panel_id = pp.id)
16. 0.019 0.042 ↑ 3.0 7 14

Materialize (cost=0.00..10.11 rows=21 width=44) (actual time=0.001..0.003 rows=7 loops=14)

17. 0.004 0.023 ↑ 3.0 7 1

Nested Loop (cost=0.00..10.01 rows=21 width=44) (actual time=0.011..0.023 rows=7 loops=1)

18. 0.005 0.005 ↑ 1.0 7 1

Values Scan on ""*VALUES*"" (cost=0.00..0.09 rows=7 width=36) (actual time=0.001..0.005 rows=7 loops=1)

19. 0.003 0.014 ↑ 3.0 1 7

Materialize (cost=0.00..9.67 rows=3 width=8) (actual time=0.001..0.002 rows=1 loops=7)

20. 0.001 0.011 ↑ 3.0 1 1

Append (cost=0.00..9.65 rows=3 width=8) (actual time=0.008..0.011 rows=1 loops=1)

21. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on proposal p (cost=0.00..0.00 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (id = 4617)
22. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on proposal p_1 (cost=0.00..1.35 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: (id = 4617)
  • Rows Removed by Filter: 28
23. 0.005 0.005 ↑ 1.0 1 1

Index Scan using proposal_pkey on proposal p_2 (cost=0.28..8.30 rows=1 width=8) (actual time=0.003..0.005 rows=1 loops=1)

  • Index Cond: (id = 4617)
24. 0.009 57.893 ↑ 45.5 22 1

Hash (cost=20.00..20.00 rows=1,000 width=72) (actual time=57.893..57.893 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
25. 57.884 57.884 ↑ 45.5 22 1

CTE Scan on paths (cost=0.00..20.00 rows=1,000 width=72) (actual time=57.844..57.884 rows=22 loops=1)

Planning time : 0.691 ms
Execution time : 62.618 ms