explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UKr2

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 13,399.971 ↑ 1.0 10 1

Limit (cost=183,716,692.43..183,716,800.56 rows=10 width=709) (actual time=13,399.464..13,399.971 rows=10 loops=1)

2. 0.042 13,399.968 ↑ 4,020,203.4 10 1

Result (cost=183,716,692.43..618,410,543.08 rows=40,202,034 width=709) (actual time=13,399.463..13,399.968 rows=10 loops=1)

3. 6.416 13,399.336 ↑ 4,020,203.4 10 1

Sort (cost=183,716,692.43..183,817,197.52 rows=40,202,034 width=677) (actual time=13,399.335..13,399.336 rows=10 loops=1)

  • Sort Key: zai.auction_date, (min(zps.created_at)), ctgf.cutpot, ctgf.word, zai.other1, (CASE WHEN (ctgf.word = ctg.word) THEN ''::text ELSE ctg.word END), zai.color, zai.other2, reg.name, zai.region_area, (CASE extract_custom_prices(zai.custom_prices, '{direct_delivery}'::text[], '{}'::text[], 0) WHEN '1'::double precision THEN 1 ELSE 0 END) DESC
  • Sort Method: top-N heapsort Memory: 35kB
4. 40.474 13,392.920 ↑ 3,349.6 12,002 1

WindowAgg (cost=83,649,403.21..182,847,940.93 rows=40,202,034 width=677) (actual time=13,383.485..13,392.920 rows=12,002 loops=1)

5. 13.837 13,352.446 ↑ 3,349.6 12,002 1

Nested Loop Left Join (cost=83,649,403.21..182,345,415.51 rows=40,202,034 width=673) (actual time=5,017.594..13,352.446 rows=12,002 loops=1)

  • Join Filter: (ctg.id = cf.catalog_id)
6. 8,275.288 13,338.609 ↑ 3,349.6 12,002 1

GroupAggregate (cost=83,649,403.21..180,737,315.32 rows=40,202,034 width=765) (actual time=5,017.439..13,338.609 rows=12,002 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)), reg.name, zai.region_area, nns.nick, ctg.id, ctgf.id, (CASE extract_custom_prices(zai.custom_prices, '{direct_delivery}'::text[], '{}'::text[], 0) WHEN '1'::double precision THEN 1 ELSE 0 END)
7. 285.915 5,063.321 ↑ 1,366.5 29,420 1

Sort (cost=83,649,403.21..83,749,908.29 rows=40,202,034 width=960) (actual time=5,015.580..5,063.321 rows=29,420 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)), reg.name, zai.region_area, nns.nick, ctg.id, ctgf.id, (CASE extract_custom_prices(zai.custom_prices, '{direct_delivery}'::text[], '{}'::text[], 0) WHEN '1'::double precision THEN 1 ELSE 0 END)
  • Sort Method: external merge Disk: 28368kB
8. 1,671.075 4,777.406 ↑ 1,366.5 29,420 1

Hash Left Join (cost=22.78..10,966,348.51 rows=40,202,034 width=960) (actual time=1.040..4,777.406 rows=29,420 loops=1)

  • Hash Cond: (zai.region_id = reg.id)
9. 43.933 3,106.147 ↑ 1,366.5 29,420 1

Nested Loop (cost=11.18..208,485.29 rows=40,202,034 width=1,325) (actual time=0.686..3,106.147 rows=29,420 loops=1)

10. 30.776 2,885.694 ↑ 8.4 29,420 1

Nested Loop (cost=11.18..1,742.79 rows=246,873 width=1,299) (actual time=0.674..2,885.694 rows=29,420 loops=1)

11. 27.305 2,648.852 ↓ 19.4 29,438 1

Hash Join (cost=11.18..766.22 rows=1,516 width=1,049) (actual time=0.653..2,648.852 rows=29,438 loops=1)

  • Hash Cond: (zai.ninushi_id = nns.id)
12. 33.208 2,621.442 ↓ 9.3 29,438 1

Nested Loop (cost=0.78..716.08 rows=3,159 width=1,037) (actual time=0.536..2,621.442 rows=29,438 loops=1)

13. 39.090 2,382.168 ↓ 29,438.0 29,438 1

Nested Loop (cost=0.78..708.18 rows=1 width=453) (actual time=0.503..2,382.168 rows=29,438 loops=1)

14. 9.023 75.043 ↓ 29,455.0 29,455 1

Nested Loop (cost=0.28..707.65 rows=1 width=453) (actual time=0.081..75.043 rows=29,455 loops=1)

15. 0.390 2.027 ↓ 257.0 257 1

Nested Loop (cost=0.28..61.51 rows=1 width=12) (actual time=0.058..2.027 rows=257 loops=1)

16. 0.066 0.609 ↓ 51.4 257 1

Append (cost=0.00..20.42 rows=5 width=16) (actual time=0.044..0.609 rows=257 loops=1)

17. 0.008 0.008 ↓ 0.0 0 1

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

  • Filter: ((customer_id = 2340) AND (market_id = 2296))
18. 0.535 0.535 ↓ 64.2 257 1

Index Scan using zaiko_publish_market_id_customer_id_idx on zaiko_publish zps_1 (cost=0.43..20.42 rows=4 width=16) (actual time=0.035..0.535 rows=257 loops=1)

  • Index Cond: ((market_id = 2296) AND (customer_id = 2340))
19. 1.028 1.028 ↑ 1.0 1 257

Index Only Scan using ninushi_customer_ninushi_id_customer_id_idx on ninushi_customer nnsc (cost=0.28..7.50 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=257)

  • Index Cond: ((ninushi_id = zps.ninushi_id) AND (customer_id = 2296))
  • Heap Fetches: 257
20. 5.397 63.993 ↓ 57.5 115 257

Append (cost=0.00..646.12 rows=2 width=449) (actual time=0.010..0.249 rows=115 loops=257)

21. 0.000 0.000 ↓ 0.0 0 257

Seq Scan on zaiko zai_2 (cost=0.00..0.00 rows=1 width=64) (actual time=0.000..0.000 rows=0 loops=257)

  • Filter: ((auction_date >= '2019-04-01'::date) AND (orgid = id) AND (zps.zaiko_group_id = group_id))
22. 58.596 58.596 ↓ 115.0 115 257

Index Scan using zaiko_group_id_idx on zaiko zai_3 (cost=0.42..646.12 rows=1 width=449) (actual time=0.008..0.228 rows=115 loops=257)

  • Index Cond: (group_id = zps.zaiko_group_id)
  • Filter: ((auction_date >= '2019-04-01'::date) AND (orgid = id))
  • Rows Removed by Filter: 97
23. 2,268.035 2,268.035 ↑ 1.0 1 29,455

Function Scan on check_price (cost=0.51..0.52 rows=1 width=0) (actual time=0.077..0.077 rows=1 loops=29,455)

  • Filter: (check_price > '0'::double precision)
  • Rows Removed by Filter: 0
24. 58.876 206.066 ↑ 2.0 1 29,438

Append (cost=0.00..7.88 rows=2 width=608) (actual time=0.007..0.007 rows=1 loops=29,438)

25. 0.000 0.000 ↓ 0.0 0 29,438

Seq Scan on zaiko zai (cost=0.00..0.00 rows=1 width=340) (actual time=0.000..0.000 rows=0 loops=29,438)

  • Filter: (zai_2.orgid = id)
26. 147.190 147.190 ↑ 1.0 1 29,438

Index Scan using zaiko_pkey on zaiko zai_1 (cost=0.42..7.88 rows=1 width=608) (actual time=0.004..0.005 rows=1 loops=29,438)

  • Index Cond: (id = zai_2.orgid)
27. 0.031 0.105 ↑ 1.0 96 1

Hash (cost=9.20..9.20 rows=96 width=16) (actual time=0.105..0.105 rows=96 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
28. 0.074 0.074 ↑ 1.0 96 1

Seq Scan on ninushi nns (cost=0.00..9.20 rows=96 width=16) (actual time=0.008..0.074 rows=96 loops=1)

  • Filter: (active = 1)
29. 29.438 206.066 ↑ 3.0 1 29,438

Append (cost=0.00..0.61 rows=3 width=259) (actual time=0.005..0.007 rows=1 loops=29,438)

30. 0.000 0.000 ↓ 0.0 0 29,438

Seq Scan on catalog ctg (cost=0.00..0.00 rows=1 width=136) (actual time=0.000..0.000 rows=0 loops=29,438)

  • Filter: ((active = 1) AND (zai.catalog_id = id))
31. 117.752 117.752 ↑ 1.0 1 29,438

Index Scan using catalog_pkey on catalog ctg_1 (cost=0.29..0.32 rows=1 width=264) (actual time=0.003..0.004 rows=1 loops=29,438)

  • Index Cond: (id = zai.catalog_id)
  • Filter: (active = 1)
  • Rows Removed by Filter: 0
32. 58.876 58.876 ↓ 0.0 0 29,438

Index Scan using catalog_brand_pkey on catalog_brand ctg_2 (cost=0.28..0.30 rows=1 width=136) (actual time=0.002..0.002 rows=0 loops=29,438)

  • Index Cond: (id = zai.catalog_id)
  • Filter: (active = 1)
33. 29.420 176.520 ↑ 3.0 1 29,420

Append (cost=0.00..0.81 rows=3 width=30) (actual time=0.004..0.006 rows=1 loops=29,420)

34. 0.000 0.000 ↓ 0.0 0 29,420

Seq Scan on catalog ctgf (cost=0.00..0.00 rows=1 width=38) (actual time=0.000..0.000 rows=0 loops=29,420)

  • Filter: ((active = 1) AND (ctg.brand = id))
35. 88.260 88.260 ↑ 1.0 1 29,420

Index Scan using catalog_pkey on catalog ctgf_1 (cost=0.29..0.50 rows=1 width=30) (actual time=0.002..0.003 rows=1 loops=29,420)

  • Index Cond: (id = ctg.brand)
  • Filter: (active = 1)
36. 58.840 58.840 ↓ 0.0 0 29,420

Index Scan using catalog_brand_pkey on catalog_brand ctgf_2 (cost=0.28..0.30 rows=1 width=38) (actual time=0.002..0.002 rows=0 loops=29,420)

  • Index Cond: (id = ctg.brand)
  • Filter: (active = 1)
37. 0.093 0.184 ↑ 1.0 293 1

Hash (cost=7.93..7.93 rows=293 width=20) (actual time=0.184..0.184 rows=293 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
38. 0.091 0.091 ↑ 1.0 293 1

Seq Scan on region reg (cost=0.00..7.93 rows=293 width=20) (actual time=0.007..0.091 rows=293 loops=1)

39. 0.000 0.000 ↓ 0.0 0 12,002

Materialize (cost=0.00..18.84 rows=2 width=12) (actual time=0.000..0.000 rows=0 loops=12,002)

40. 0.150 0.150 ↓ 0.0 0 1

Seq Scan on catalog_favorite cf (cost=0.00..18.83 rows=2 width=12) (actual time=0.150..0.150 rows=0 loops=1)

  • Filter: (customer_member_id = 2835)
  • Rows Removed by Filter: 946
41.          

SubPlan (forResult)

42. 0.010 0.590 ↑ 1.0 1 10

Limit (cost=10.80..10.80 rows=1 width=70) (actual time=0.059..0.059 rows=1 loops=10)

43. 0.070 0.580 ↑ 2.0 1 10

Sort (cost=10.80..10.80 rows=2 width=70) (actual time=0.058..0.058 rows=1 loops=10)

  • Sort Key: (CASE WHEN ((ctg_f.represent > 0) AND (ctg_f.region_id = zai.region_id)) THEN 0 ELSE 1 END), (CASE WHEN ((ctg_f.represent > 0) AND (ctg_f.region_id IS NULL)) THEN 0 ELSE 1 END), ctg_f.id DESC
  • Sort Method: quicksort Memory: 25kB
44. 0.050 0.510 ↓ 2.5 5 10

Result (cost=0.00..10.79 rows=2 width=70) (actual time=0.017..0.051 rows=5 loops=10)

45. 0.020 0.460 ↓ 2.5 5 10

Append (cost=0.00..10.75 rows=2 width=84) (actual time=0.017..0.046 rows=5 loops=10)

46. 0.010 0.010 ↓ 0.0 0 10

Seq Scan on catalog_file ctg_f (cost=0.00..0.00 rows=1 width=72) (actual time=0.001..0.001 rows=0 loops=10)

  • Filter: (((zai.region_id = region_id) OR (region_id IS NULL) OR (zai.region_id IS NULL)) AND (catalog_id = ctg.id) AND (ninushi_id = zai.ninushi_id))
47. 0.430 0.430 ↓ 5.0 5 10

Index Scan using catalog_file_catalog_id_idx on catalog_file ctg_f_1 (cost=0.29..10.75 rows=1 width=96) (actual time=0.014..0.043 rows=5 loops=10)

  • Index Cond: (catalog_id = ctg.id)
  • Filter: (((zai.region_id = region_id) OR (region_id IS NULL) OR (zai.region_id IS NULL)) AND (ninushi_id = zai.ninushi_id))
Planning time : 7.579 ms
Execution time : 13,406.949 ms