explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qSis

Settings
# exclusive inclusive rows x rows loops node
1. 4.429 228,682.366 ↑ 9.2 144 1

HashAggregate (cost=6,679,316,969.49..6,679,316,986.05 rows=1,325 width=86) (actual time=228,682.315..228,682.366 rows=144 loops=1)

  • Group Key: t0.id, (t0.images ->> 's3url'::text)
2.          

CTE paths

3. 14.402 14.402 ↑ 1,000.0 1 1

Function Scan on get_custom_values_path paths_1 (cost=0.25..32.75 rows=1,000 width=216) (actual time=14.401..14.402 rows=1 loops=1)

4. 47.390 228,677.937 ↑ 140,857,458.1 1,886 1

Merge Join (cost=363,815.40..4,686,888,191.48 rows=265,657,166,035 width=86) (actual time=3,241.304..228,677.937 rows=1,886 loops=1)

  • Merge Cond: (variety.id = zai.catalog_id)
5. 40,484.140 225,414.042 ↑ 12,670.5 25,151 1

Nested Loop (cost=0.71..36,728,178.31 rows=318,676,957 width=148) (actual time=26.573..225,414.042 rows=25,151 loops=1)

  • Join Filter: (flower.id = ANY (t0.catalog_ids))
  • Rows Removed by Join Filter: 33297090
6. 15,084.925 19,952.238 ↑ 1.0 41,244,416 1

Nested Loop (cost=0.71..526,120.88 rows=41,561,275 width=187) (actual time=0.050..19,952.238 rows=41,244,416 loops=1)

7. 32.032 135.857 ↑ 1.0 31,128 1

Merge Append (cost=0.71..6,531.49 rows=31,367 width=8) (actual time=0.034..135.857 rows=31,128 loops=1)

  • Sort Key: variety.id
8. 0.007 0.007 ↓ 0.0 0 1

Index Scan using catalog_pkey on catalog variety (cost=0.12..8.14 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: (active = 1)
9. 92.095 92.095 ↑ 1.0 29,806 1

Index Scan using catalog_pkey on catalog variety_1 (cost=0.29..5,936.06 rows=30,041 width=8) (actual time=0.014..92.095 rows=29,806 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 2064
10. 11.723 11.723 ↑ 1.0 1,323 1

Index Scan using catalog_brand_pkey on catalog_brand variety_2 (cost=0.28..260.27 rows=1,325 width=8) (actual time=0.012..11.723 rows=1,323 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 46
11. 4,727.153 4,731.456 ↑ 1.0 1,325 31,128

Materialize (cost=0.00..76.76 rows=1,325 width=179) (actual time=0.000..0.152 rows=1,325 loops=31,128)

12. 4.303 4.303 ↑ 1.0 1,325 1

Seq Scan on catalog_brand t0 (cost=0.00..70.14 rows=1,325 width=179) (actual time=0.012..4.303 rows=1,325 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 46
13. 41,244.417 164,977.664 ↑ 3.0 1 41,244,416

Append (cost=0.00..0.80 rows=3 width=4) (actual time=0.003..0.004 rows=1 loops=41,244,416)

14. 0.000 0.000 ↓ 0.0 0 41,244,416

Seq Scan on catalog flower (cost=0.00..0.00 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=41,244,416)

  • Filter: ((active = 1) AND (variety.brand = id))
15. 82,488.832 82,488.832 ↑ 1.0 1 41,244,416

Index Scan using catalog_pkey on catalog flower_1 (cost=0.29..0.50 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=41,244,416)

  • Index Cond: (id = variety.brand)
  • Filter: (active = 1)
16. 41,244.415 41,244.415 ↓ 0.0 0 41,244,415

Index Scan using catalog_brand_pkey on catalog_brand flower_2 (cost=0.28..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=41,244,415)

  • Index Cond: (id = variety.brand)
  • Filter: (active = 1)
17. 0.852 3,216.505 ↑ 88.4 1,886 1

Materialize (cost=363,814.68..364,648.31 rows=166,725 width=16) (actual time=3,214.719..3,216.505 rows=1,886 loops=1)

18. 2.090 3,215.653 ↑ 88.4 1,886 1

Sort (cost=363,814.68..364,231.50 rows=166,725 width=16) (actual time=3,214.714..3,215.653 rows=1,886 loops=1)

  • Sort Key: zai.catalog_id
  • Sort Method: quicksort Memory: 137kB
19. 629.580 3,213.563 ↑ 88.4 1,886 1

Hash Join (cost=2,287.01..346,501.20 rows=166,725 width=16) (actual time=123.655..3,213.563 rows=1,886 loops=1)

  • Hash Cond: (zps.customer_id = paths.id_child_of_market)
  • Join Filter: (COALESCE(extract_custom_prices(zai.custom_prices, jsonb_array_to_text_array((paths.root_level_paths_price_by_nns_id -> (zai.ninushi_id)::text)), paths.nested_paths_price, paths.nested_cutoff), zai.price) > '0'::double precision)
  • Rows Removed by Join Filter: 16
20. 1,414.676 2,569.551 ↓ 27.6 2,757,499 1

Hash Join (cost=2,254.51..73,623.24 rows=100,035 width=366) (actual time=108.210..2,569.551 rows=2,757,499 loops=1)

  • Hash Cond: (zps.zaiko_group_id = zai.group_id)
21. 381.899 1,145.181 ↑ 1.0 3,334,500 1

Append (cost=0.00..57,864.00 rows=3,334,501 width=8) (actual time=0.082..1,145.181 rows=3,334,500 loops=1)

22. 0.009 0.009 ↓ 0.0 0 1

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

23. 763.273 763.273 ↑ 1.0 3,334,500 1

Seq Scan on zaiko_publish zps_1 (cost=0.00..57,864.00 rows=3,334,500 width=8) (actual time=0.072..763.273 rows=3,334,500 loops=1)

24. 4.500 9.694 ↓ 335.2 2,011 1

Hash (cost=2,254.43..2,254.43 rows=6 width=366) (actual time=9.694..9.694 rows=2,011 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1620kB
25. 0.376 5.194 ↓ 335.3 2,012 1

Append (cost=0.00..2,254.43 rows=6 width=366) (actual time=0.040..5.194 rows=2,012 loops=1)

26. 0.004 0.004 ↓ 0.0 0 1

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

  • Filter: ((id = orgid) AND (auction_date = '2019-06-07'::date))
27. 4.814 4.814 ↓ 402.4 2,012 1

Index Scan using zaiko_auction_date_idx on zaiko zai_1 (cost=0.42..2,254.43 rows=5 width=427) (actual time=0.035..4.814 rows=2,012 loops=1)

  • Index Cond: (auction_date = '2019-06-07'::date)
  • Filter: (id = orgid)
  • Rows Removed by Filter: 368
28. 0.008 14.432 ↑ 1,000.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
29. 14.424 14.424 ↑ 1,000.0 1 1

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

Planning time : 2.288 ms
Execution time : 228,682.649 ms