explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HrDv

Settings
# exclusive inclusive rows x rows loops node
1. 26.001 38.051 ↑ 265.2 78 1

GroupAggregate (cost=10,931.38..32,343.46 rows=20,688 width=4) (actual time=12.470..38.051 rows=78 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: 99
2.          

CTE paths

3. 8.379 8.379 ↑ 1,000.0 1 1

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

4. 0.850 12.050 ↑ 116.9 177 1

Sort (cost=10,906.13..10,957.85 rows=20,688 width=617) (actual time=11.966..12.050 rows=177 loops=1)

  • Sort Key: pd.id
  • Sort Method: quicksort Memory: 1443kB
5. 0.021 11.200 ↑ 116.9 177 1

Nested Loop (cost=22.56..3,693.67 rows=20,688 width=617) (actual time=9.376..11.200 rows=177 loops=1)

6. 0.065 0.736 ↑ 1.8 177 1

Nested Loop (cost=0.00..290.74 rows=323 width=553) (actual time=0.179..0.736 rows=177 loops=1)

7. 0.000 0.107 ↑ 2.3 3 1

Append (cost=0.00..18.93 rows=7 width=8) (actual time=0.094..0.107 rows=3 loops=1)

8. 0.020 0.020 ↓ 0.0 0 1

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

  • Filter: (proposal_id = 12717)
9. 0.042 0.042 ↓ 0.0 0 1

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

  • Filter: (proposal_id = 12717)
  • Rows Removed by Filter: 7
10. 0.045 0.045 ↑ 1.7 3 1

Index Scan using proposal_panel_proposal_id_idx on proposal_panel pp_2 (cost=0.29..17.84 rows=5 width=8) (actual time=0.032..0.045 rows=3 loops=1)

  • Index Cond: (proposal_id = 12717)
11. 0.030 0.564 ↑ 1.1 59 3

Append (cost=0.00..38.16 rows=67 width=553) (actual time=0.038..0.188 rows=59 loops=3)

12. 0.021 0.021 ↓ 0.0 0 3

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

  • Filter: (pp.id = proposal_panel_id)
13. 0.015 0.015 ↓ 0.0 0 3

Index Scan using proposal_detail_proposal_panel_id_idx on proposal_detail pd_1 (cost=0.14..3.35 rows=20 width=99) (actual time=0.005..0.005 rows=0 loops=3)

  • Index Cond: (proposal_panel_id = pp.id)
14. 0.498 0.498 ↓ 1.3 59 3

Index Scan using proposal_detail_proposal_panel_id_idx on proposal_detail pd_2 (cost=0.42..34.81 rows=46 width=553) (actual time=0.022..0.166 rows=59 loops=3)

  • Index Cond: (proposal_panel_id = pp.id)
15. 0.096 10.443 ↑ 64.0 1 177

Materialize (cost=22.56..3,144.68 rows=64 width=72) (actual time=0.052..0.059 rows=1 loops=177)

16. 0.003 10.347 ↑ 64.0 1 1

Nested Loop (cost=22.56..3,144.36 rows=64 width=72) (actual time=9.192..10.347 rows=1 loops=1)

17. 0.294 10.238 ↑ 21.0 1 1

Hash Join (cost=22.56..3,134.18 rows=21 width=68) (actual time=9.086..10.238 rows=1 loops=1)

  • Hash Cond: ((pb.customer_id + 20000000) = paths.market_uid)
18. 0.193 1.552 ↓ 2.1 1,822 1

Append (cost=0.00..3,107.13 rows=854 width=8) (actual time=0.685..1.552 rows=1,822 loops=1)

19. 0.019 0.019 ↓ 0.0 0 1

Seq Scan on proposal_publish pb (cost=0.00..0.00 rows=1 width=8) (actual time=0.019..0.019 rows=0 loops=1)

  • Filter: (proposal_id = 12717)
20. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on proposal_publish pb_1 (cost=0.00..23.38 rows=5 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (proposal_id = 12717)
21. 0.873 1.339 ↓ 2.1 1,822 1

Bitmap Heap Scan on proposal_publish pb_2 (cost=19.00..3,083.76 rows=848 width=8) (actual time=0.665..1.339 rows=1,822 loops=1)

  • Recheck Cond: (proposal_id = 12717)
  • Heap Blocks: exact=37
22. 0.466 0.466 ↓ 4.3 3,644 1

Bitmap Index Scan on proposal_publish_proposal_id_idx (cost=0.00..18.79 rows=848 width=0) (actual time=0.466..0.466 rows=3,644 loops=1)

  • Index Cond: (proposal_id = 12717)
23. 0.005 8.392 ↑ 5.0 1 1

Hash (cost=22.50..22.50 rows=5 width=72) (actual time=8.392..8.392 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
24. 8.387 8.387 ↑ 5.0 1 1

CTE Scan on paths (cost=0.00..22.50 rows=5 width=72) (actual time=8.386..8.387 rows=1 loops=1)

  • Filter: (market_uid = uid_child_of_market)
25. 0.004 0.106 ↑ 3.0 1 1

Materialize (cost=0.00..9.41 rows=3 width=8) (actual time=0.104..0.106 rows=1 loops=1)

26. 0.001 0.102 ↑ 3.0 1 1

Append (cost=0.00..9.39 rows=3 width=8) (actual time=0.101..0.102 rows=1 loops=1)

27. 0.013 0.013 ↓ 0.0 0 1

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

  • Filter: (id = 12717)
28. 0.040 0.040 ↓ 0.0 0 1

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

  • Filter: (id = 12717)
  • Rows Removed by Filter: 7
29. 0.048 0.048 ↑ 1.0 1 1

Index Scan using proposal_pkey on proposal p_2 (cost=0.29..8.30 rows=1 width=8) (actual time=0.047..0.048 rows=1 loops=1)

  • Index Cond: (id = 12717)
Planning time : 1.295 ms
Execution time : 38.295 ms