explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JWLF

Settings
# exclusive inclusive rows x rows loops node
1. 4,880.486 4,880.486 ↓ 10.0 10 1

CTE Scan on cte_custom_zaiko (cost=838,221.47..838,221.49 rows=1 width=140) (actual time=4,880.352..4,880.486 rows=10 loops=1)

2.          

CTE paths

3. 22.280 22.280 ↑ 500.0 2 1

Function Scan on get_custom_values_path paths (cost=0.25..32.75 rows=1,000 width=216) (actual time=22.246..22.280 rows=2 loops=1)

4.          

CTE working_days

5. 0.710 6.779 ↑ 4.4 62 1

GroupAggregate (cost=0.57..523,256.96 rows=271 width=72) (actual time=0.199..6.779 rows=62 loops=1)

  • Group Key: nnsc.ninushi_id, nnsc.customer_id
6. 0.052 6.069 ↑ 67,554.8 153 1

Nested Loop Left Join (cost=0.57..213,176.55 rows=10,335,878 width=72) (actual time=0.135..6.069 rows=153 loops=1)

7. 0.079 5.741 ↑ 749.0 138 1

Nested Loop Left Join (cost=0.57..6,458.55 rows=103,359 width=154) (actual time=0.129..5.741 rows=138 loops=1)

8. 3.264 5.352 ↑ 16.7 62 1

Nested Loop Semi Join (cost=0.56..4,390.54 rows=1,034 width=122) (actual time=0.112..5.352 rows=62 loops=1)

  • Join Filter: (nnsc.customer_id = (unnest(customer.parents_and_self)))
  • Rows Removed by Join Filter: 7960
9. 2.088 2.088 ↑ 1.0 2,708 1

Index Scan using ninushi_customer_ninushi_id_customer_id_idx on ninushi_customer nnsc (cost=0.28..318.48 rows=2,708 width=122) (actual time=0.025..2.088 rows=2,708 loops=1)

10. 0.000 0.000 ↑ 33.3 3 2,708

Materialize (cost=0.28..10.30 rows=100 width=4) (actual time=0.000..0.000 rows=3 loops=2,708)

11. 0.008 0.027 ↑ 33.3 3 1

ProjectSet (cost=0.28..8.80 rows=100 width=4) (actual time=0.024..0.027 rows=3 loops=1)

12. 0.019 0.019 ↑ 1.0 1 1

Index Scan using customer_pkey on customer (cost=0.28..8.30 rows=1 width=27) (actual time=0.018..0.019 rows=1 loops=1)

  • Index Cond: (id = 1677)
13. 0.310 0.310 ↑ 50.0 2 62

Function Scan on jsonb_array_elements cut_working_day (cost=0.01..1.01 rows=100 width=32) (actual time=0.004..0.005 rows=2 loops=62)

14. 0.276 0.276 ↓ 0.0 0 138

Function Scan on jsonb_array_elements pot_working_day (cost=0.01..1.01 rows=100 width=32) (actual time=0.002..0.002 rows=0 loops=138)

15.          

CTE cte_zaiko

16. 3,435.540 3,662.684 ↓ 318.9 38,268 1

Nested Loop Semi Join (cost=0.00..231,684.85 rows=120 width=1,187) (actual time=38.409..3,662.684 rows=38,268 loops=1)

  • Join Filter: (COALESCE(extract_custom_prices(zai.custom_prices, jsonb_array_to_text_array((paths_1.root_level_paths_price_by_nns_id -> (zai.ninushi_id)::text)), paths_1.nested_paths_price, paths_1.nested_cutoff), zai.price) > '0'::double precision)
  • Rows Removed by Join Filter: 194
17. 8.645 150.586 ↓ 106.0 38,279 1

Append (cost=0.00..103,817.78 rows=361 width=1,187) (actual time=15.763..150.586 rows=38,279 loops=1)

18. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on zaiko zai (cost=0.00..0.00 rows=1 width=802) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: ((auction_date >= '2020-03-16'::date) AND (id = orgid))
19. 130.430 141.938 ↓ 106.3 38,279 1

Bitmap Heap Scan on zaiko zai_1 (cost=1,331.97..103,817.78 rows=360 width=1,188) (actual time=15.759..141.938 rows=38,279 loops=1)

  • Recheck Cond: (auction_date >= '2020-03-16'::date)
  • Filter: (id = orgid)
  • Rows Removed by Filter: 36170
  • Heap Blocks: exact=11965
20. 11.508 11.508 ↓ 1.0 74,610 1

Bitmap Index Scan on zaiko_auction_date_idx (cost=0.00..1,331.88 rows=71,927 width=0) (actual time=11.508..11.508 rows=74,610 loops=1)

  • Index Cond: (auction_date >= '2020-03-16'::date)
21. 76.558 76.558 ↑ 1,000.0 1 38,279

CTE Scan on paths paths_1 (cost=0.00..20.00 rows=1,000 width=68) (actual time=0.002..0.002 rows=1 loops=38,279)

22.          

CTE cte_zaiko_publish

23. 6.672 50.011 ↓ 57.1 6,561 1

Hash Join (cost=8.81..83,239.46 rows=115 width=32) (actual time=6.983..50.011 rows=6,561 loops=1)

  • Hash Cond: (zps.ninushi_id = working_days.ninushi_id)
  • Join Filter: ((working_days.customer_id = zps.market_id) OR (working_days.customer_id = zps.customer_id))
  • Rows Removed by Join Filter: 5983
24. 2.943 36.446 ↑ 2.5 6,561 1

Nested Loop (cost=0.00..82,317.94 rows=16,133 width=32) (actual time=0.081..36.446 rows=6,561 loops=1)

25. 0.065 0.065 ↑ 500.0 2 1

CTE Scan on paths paths_2 (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.002..0.065 rows=2 loops=1)

26. 1.068 33.438 ↓ 126.2 3,280 2

Append (cost=0.00..82.04 rows=26 width=32) (actual time=0.086..16.719 rows=3,280 loops=2)

27. 0.008 0.008 ↓ 0.0 0 2

Seq Scan on zaiko_publish zps (cost=0.00..0.00 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=2)

  • Filter: ((paths_2.id_child_of_market = customer_id) AND (paths_2.market_id = market_id))
28. 32.362 32.362 ↓ 131.2 3,280 2

Index Scan using zaiko_publish_market_id_customer_id_idx on zaiko_publish zps_1 (cost=0.43..82.04 rows=25 width=32) (actual time=0.074..16.181 rows=3,280 loops=2)

  • Index Cond: ((market_id = paths_2.market_id) AND (customer_id = paths_2.id_child_of_market))
29. 0.036 6.893 ↑ 4.4 62 1

Hash (cost=5.42..5.42 rows=271 width=8) (actual time=6.893..6.893 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
30. 6.857 6.857 ↑ 4.4 62 1

CTE Scan on working_days (cost=0.00..5.42 rows=271 width=8) (actual time=0.202..6.857 rows=62 loops=1)

31.          

CTE cte_zaiko_published

32. 101.033 3,994.062 ↓ 61,963.0 61,963 1

Hash Join (cost=4.03..7.33 rows=1 width=802) (actual time=99.942..3,994.062 rows=61,963 loops=1)

  • Hash Cond: ((zai_2.group_id = cte_zaiko_publish.zaiko_group_id) AND (zai_2.ninushi_id = cte_zaiko_publish.ninushi_id))
33. 3,833.943 3,833.943 ↓ 318.9 38,268 1

CTE Scan on cte_zaiko zai_2 (cost=0.00..2.40 rows=120 width=802) (actual time=38.416..3,833.943 rows=38,268 loops=1)

34. 3.189 59.086 ↓ 57.1 6,561 1

Hash (cost=2.30..2.30 rows=115 width=8) (actual time=59.086..59.086 rows=6,561 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 321kB
35. 55.897 55.897 ↓ 57.1 6,561 1

CTE Scan on cte_zaiko_publish (cost=0.00..2.30 rows=115 width=8) (actual time=6.985..55.897 rows=6,561 loops=1)

36.          

CTE cte_custom_zaiko

37. 0.003 4,880.454 ↓ 10.0 10 1

Limit (cost=0.03..0.11 rows=1 width=356) (actual time=4,880.342..4,880.454 rows=10 loops=1)

38. 0.133 4,880.451 ↓ 10.0 10 1

GroupAggregate (cost=0.03..0.11 rows=1 width=356) (actual time=4,880.340..4,880.451 rows=10 loops=1)

  • Group Key: zai_3.auction_date, zai_3.ninushi_id, zai_3.other1, zai_3.other2, zai_3.color, zai_3.order_deadline, zai_3.region_id, ((zai_3.metadata -> 'prevent_order_detail_comment'::text)), zai_3.region_area, zai_3.catalog_id
39. 620.593 4,880.318 ↓ 65.0 65 1

Sort (cost=0.03..0.04 rows=1 width=268) (actual time=4,880.295..4,880.318 rows=65 loops=1)

  • Sort Key: zai_3.auction_date, zai_3.ninushi_id, zai_3.other1, zai_3.other2, zai_3.color, zai_3.order_deadline, zai_3.region_id, ((zai_3.metadata -> 'prevent_order_detail_comment'::text)), zai_3.region_area, zai_3.catalog_id
  • Sort Method: external merge Disk: 45968kB
40. 4,259.725 4,259.725 ↓ 61,963.0 61,963 1

CTE Scan on cte_zaiko_published zai_3 (cost=0.00..0.02 rows=1 width=268) (actual time=99.946..4,259.725 rows=61,963 loops=1)

Planning time : 2.466 ms
Execution time : 4,918.229 ms