explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6AVv1

Settings
# exclusive inclusive rows x rows loops node
1. 52.809 15,168.286 ↓ 305.8 43,121 1

GroupAggregate (cost=268,248.92..268,254.91 rows=141 width=220) (actual time=15,087.834..15,168.286 rows=43,121 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. 26.961 26.961 ↑ 500.0 2 1

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

4.          

CTE cte_zaiko_1

5. 21.753 420.361 ↓ 136.3 70,880 1

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

6. 0.004 0.004 ↓ 0.0 0 1

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

  • Filter: ((auction_date >= '2020-01-16'::date) AND (auction_date <= '2020-04-16'::date) AND (id = orgid))
7. 378.758 398.604 ↓ 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=28.101..398.604 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
8. 19.846 19.846 ↑ 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=19.846..19.846 rows=99,004 loops=1)

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

CTE cte_zaiko

10. 140.349 2,831.492 ↓ 167.6 70,880 1

Nested Loop (cost=0.00..3,608.83 rows=423 width=804) (actual time=28.163..2,831.492 rows=70,880 loops=1)

11. 192.917 1,840.583 ↓ 136.3 70,880 1

Nested Loop (cost=0.00..3,180.60 rows=520 width=806) (actual time=28.149..1,840.583 rows=70,880 loops=1)

12. 726.226 726.226 ↓ 136.3 70,880 1

CTE Scan on cte_zaiko_1 zai_3 (cost=0.00..10.40 rows=520 width=802) (actual time=28.109..726.226 rows=70,880 loops=1)

13. 283.520 921.440 ↑ 3.0 1 70,880

Append (cost=0.00..6.07 rows=3 width=8) (actual time=0.008..0.013 rows=1 loops=70,880)

14. 0.000 0.000 ↓ 0.0 0 70,880

Seq Scan on catalog ctg_v (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=70,880)

  • Filter: (zai_3.catalog_id = id)
15. 425.280 425.280 ↑ 1.0 1 70,880

Index Scan using catalog_pkey on catalog ctg_v_1 (cost=0.29..5.05 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=70,880)

  • Index Cond: (id = zai_3.catalog_id)
16. 212.640 212.640 ↓ 0.0 0 70,880

Index Scan using catalog_brand_pkey on catalog_brand ctg_v_2 (cost=0.28..1.01 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=70,880)

  • Index Cond: (id = zai_3.catalog_id)
17. 212.640 850.560 ↑ 3.0 1 70,880

Append (cost=0.00..0.79 rows=3 width=6) (actual time=0.005..0.012 rows=1 loops=70,880)

18. 0.000 0.000 ↓ 0.0 0 70,880

Seq Scan on catalog ctg_f (cost=0.00..0.00 rows=1 width=6) (actual time=0.000..0.000 rows=0 loops=70,880)

  • Filter: (ctg_v.brand = id)
19. 354.400 354.400 ↑ 1.0 1 70,880

Index Scan using catalog_pkey on catalog ctg_f_1 (cost=0.29..0.49 rows=1 width=6) (actual time=0.004..0.005 rows=1 loops=70,880)

  • Index Cond: (id = ctg_v.brand)
20. 283.520 283.520 ↓ 0.0 0 70,880

Index Scan using catalog_brand_pkey on catalog_brand ctg_f_2 (cost=0.28..0.30 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=70,880)

  • Index Cond: (id = ctg_v.brand)
21.          

CTE cte_zaiko_price

22. 9,860.575 13,316.230 ↓ 502.6 70,870 1

Nested Loop Semi Join (cost=0.00..149,627.77 rows=141 width=804) (actual time=55.628..13,316.230 rows=70,870 loops=1)

  • Join Filter: (COALESCE(extract_custom_prices(zai_4.custom_prices, jsonb_array_to_text_array((paths_1.root_level_paths_price_by_nns_id -> (zai_4.ninushi_id)::text)), paths_1.nested_paths_price, paths_1.nested_cutoff), zai_4.price) > '0'::double precision)
  • Rows Removed by Join Filter: 364
23. 3,243.015 3,243.015 ↓ 167.6 70,880 1

CTE Scan on cte_zaiko zai_4 (cost=0.00..8.46 rows=423 width=804) (actual time=28.171..3,243.015 rows=70,880 loops=1)

24. 212.640 212.640 ↑ 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.003..0.003 rows=1 loops=70,880)

25. 1,276.130 15,115.477 ↓ 502.6 70,870 1

Sort (cost=8.21..8.56 rows=141 width=220) (actual time=15,087.825..15,115.477 rows=70,870 loops=1)

  • Sort 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
  • Sort Method: external merge Disk: 4448kB
26. 13,839.347 13,839.347 ↓ 502.6 70,870 1

CTE Scan on cte_zaiko_price zai (cost=0.00..3.17 rows=141 width=220) (actual time=55.641..13,839.347 rows=70,870 loops=1)

Planning time : 2.426 ms
Execution time : 15,257.433 ms