explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tfFP

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 9,523.524 ↓ 0.0 0 1

HashAggregate (cost=968,658.79..968,658.80 rows=1 width=220) (actual time=9,523.524..9,523.524 rows=0 loops=1)

  • Group Key: zai.auction_date, zai.ninushi_id, zai.other1, zai.other2, zai.color, zai.order_deadline, zai.region_id, (zai.metadata -> 'prevent_order_detail_comment'::text), zai.region_area, zai.catalog_id
2.          

CTE paths

3. 19.886 19.886 ↑ 500.0 2 1

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

4.          

CTE working_days

5. 0.852 7.314 ↑ 4.4 62 1

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

  • Group Key: nnsc.ninushi_id, nnsc.customer_id
6. 0.102 6.462 ↑ 67,554.8 153 1

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

7. 0.102 6.084 ↑ 749.0 138 1

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

8. 3.330 5.610 ↑ 16.7 62 1

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

  • Join Filter: (nnsc.customer_id = (unnest(customer.parents_and_self)))
  • Rows Removed by Join Filter: 7960
9. 2.280 2.280 ↑ 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.023..2.280 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.010 0.031 ↑ 33.3 3 1

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

12. 0.021 0.021 ↑ 1.0 1 1

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

  • Index Cond: (id = 1677)
13. 0.372 0.372 ↑ 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.005..0.006 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. 17.787 343.005 ↓ 136.3 70,880 1

Append (cost=0.00..114,971.36 rows=520 width=1,187) (actual time=21.766..343.005 rows=70,880 loops=1)

17. 0.006 0.006 ↓ 0.0 0 1

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

  • Filter: ((auction_date >= '2020-01-16'::date) AND (auction_date <= '2020-04-16'::date) AND (id = orgid))
18. 310.965 325.212 ↓ 136.6 70,880 1

Bitmap Heap Scan on zaiko zai_2 (cost=2,183.03..114,971.36 rows=519 width=1,188) (actual time=21.759..325.212 rows=70,880 loops=1)

  • Recheck Cond: ((auction_date >= '2020-01-16'::date) AND (auction_date <= '2020-04-16'::date))
  • Filter: (id = orgid)
  • Rows Removed by Filter: 28124
  • Heap Blocks: exact=20949
19. 14.247 14.247 ↑ 1.0 99,004 1

Bitmap Index Scan on zaiko_auction_date_idx (cost=0.00..2,182.90 rows=103,847 width=0) (actual time=14.247..14.247 rows=99,004 loops=1)

  • Index Cond: ((auction_date >= '2020-01-16'::date) AND (auction_date <= '2020-04-16'::date))
20.          

CTE cte_zaiko_price

21. 8,012.778 8,668.463 ↓ 409.7 70,870 1

Nested Loop Semi Join (cost=0.00..184,113.60 rows=173 width=802) (actual time=22.152..8,668.463 rows=70,870 loops=1)

  • Join Filter: (COALESCE(extract_custom_prices(zai_3.custom_prices, jsonb_array_to_text_array((paths_1.root_level_paths_price_by_nns_id -> (zai_3.ninushi_id)::text)), paths_1.nested_paths_price, paths_1.nested_cutoff), zai_3.price) > '0'::double precision)
  • Rows Removed by Join Filter: 364
22. 584.805 584.805 ↓ 136.3 70,880 1

CTE Scan on cte_zaiko zai_3 (cost=0.00..10.40 rows=520 width=802) (actual time=21.770..584.805 rows=70,880 loops=1)

23. 70.880 70.880 ↑ 1,000.0 1 70,880

CTE Scan on paths paths_1 (cost=0.00..20.00 rows=1,000 width=68) (actual time=0.001..0.001 rows=1 loops=70,880)

24.          

CTE cte_zaiko_deadline

25. 254.783 9,496.073 ↓ 0.0 0 1

Nested Loop Semi Join (cost=0.00..63,039.41 rows=58 width=802) (actual time=9,496.073..9,496.073 rows=0 loops=1)

  • Join Filter: (COALESCE(COALESCE(CASE WHEN ((zai_4.custom_deadlines IS NULL) OR ((zai_4.custom_deadlines)::text = '[]'::text) OR ((zai_4.custom_deadlines)::text = '{}'::text)) THEN NULL::timestamp without time zone ELSE extract_custom_deadlines(zai_4.custom_deadlines, jsonb_array_to_text_array((paths_2.root_level_paths_deadline_by_nns_id -> (zai_4.ninushi_id)::text)), paths_2.nested_paths_deadline, 0) END, zai_4.order_deadline), zai_4.order_deadline) >= now())
  • Rows Removed by Join Filter: 141740
26. 9,028.680 9,028.680 ↓ 409.7 70,870 1

CTE Scan on cte_zaiko_price zai_4 (cost=0.00..3.46 rows=173 width=802) (actual time=22.158..9,028.680 rows=70,870 loops=1)

27. 212.610 212.610 ↑ 500.0 2 70,870

CTE Scan on paths paths_2 (cost=0.00..20.00 rows=1,000 width=64) (actual time=0.002..0.003 rows=2 loops=70,870)

28.          

CTE cte_zaiko_publish

29. 0.015 27.433 ↑ 115.0 1 1

Hash Join (cost=8.81..83,239.46 rows=115 width=32) (actual time=27.433..27.433 rows=1 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))
30. 0.005 19.966 ↑ 16,133.0 1 1

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

31. 19.888 19.888 ↑ 1,000.0 1 1

CTE Scan on paths paths_3 (cost=0.00..20.00 rows=1,000 width=8) (actual time=19.888..19.888 rows=1 loops=1)

32. 0.003 0.073 ↑ 26.0 1 1

Append (cost=0.00..82.04 rows=26 width=32) (actual time=0.073..0.073 rows=1 loops=1)

33. 0.009 0.009 ↓ 0.0 0 1

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

  • Filter: ((paths_3.id_child_of_market = customer_id) AND (paths_3.market_id = market_id))
34. 0.061 0.061 ↑ 25.0 1 1

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.061..0.061 rows=1 loops=1)

  • Index Cond: ((market_id = paths_3.market_id) AND (customer_id = paths_3.id_child_of_market))
35. 0.041 7.452 ↑ 4.4 62 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
36. 7.411 7.411 ↑ 4.4 62 1

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

37.          

CTE cte_zaiko_published

38. 0.011 9,523.521 ↓ 0.0 0 1

Hash Join (cost=2.03..5.20 rows=1 width=802) (actual time=9,523.521..9,523.521 rows=0 loops=1)

  • Hash Cond: ((cte_zaiko_publish.zaiko_group_id = zai_5.group_id) AND (cte_zaiko_publish.ninushi_id = zai_5.ninushi_id))
39. 27.435 27.435 ↑ 115.0 1 1

CTE Scan on cte_zaiko_publish (cost=0.00..2.30 rows=115 width=8) (actual time=27.435..27.435 rows=1 loops=1)

40. 0.001 9,496.075 ↓ 0.0 0 1

Hash (cost=1.16..1.16 rows=58 width=802) (actual time=9,496.075..9,496.075 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
41. 9,496.074 9,496.074 ↓ 0.0 0 1

CTE Scan on cte_zaiko_deadline zai_5 (cost=0.00..1.16 rows=58 width=802) (actual time=9,496.074..9,496.074 rows=0 loops=1)

42. 9,523.523 9,523.523 ↓ 0.0 0 1

CTE Scan on cte_zaiko_published zai (cost=0.00..0.02 rows=1 width=220) (actual time=9,523.523..9,523.523 rows=0 loops=1)

Planning time : 2.558 ms
Execution time : 9,567.401 ms