explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tbPU

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 618.728 ↓ 0.0 0 1

GroupAggregate (cost=39,953,989.75..41,123,126.98 rows=43,772 width=46) (actual time=618.728..618.728 rows=0 loops=1)

  • Group Key: ctg_label.id
2.          

CTE paths

3. 5.116 5.116 ↑ 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=5.116..5.116 rows=1 loops=1)

4. 0.028 618.726 ↓ 0.0 0 1

Sort (cost=39,953,957.00..40,343,523.50 rows=155,826,601 width=46) (actual time=618.726..618.726 rows=0 loops=1)

  • Sort Key: ctg_label.id
  • Sort Method: quicksort Memory: 25kB
5. 0.001 618.698 ↓ 0.0 0 1

Merge Join (cost=2,030,011.54..4,369,081.87 rows=155,826,601 width=46) (actual time=618.698..618.698 rows=0 loops=1)

  • Merge Cond: (zai.catalog_id = variety.id)
6. 0.042 618.697 ↓ 0.0 0 1

Sort (cost=95,445.13..95,545.33 rows=40,079 width=16) (actual time=618.697..618.697 rows=0 loops=1)

  • Sort Key: zai.catalog_id
  • Sort Method: quicksort Memory: 25kB
7. 0.015 618.655 ↓ 0.0 0 1

Hash Join (cost=1,152.38..92,380.98 rows=40,079 width=16) (actual time=618.655..618.655 rows=0 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)
8. 154.769 613.509 ↑ 728.9 33 1

Hash Join (cost=1,119.88..26,741.19 rows=24,054 width=270) (actual time=613.033..613.509 rows=33 loops=1)

  • Hash Cond: (zps.zaiko_group_id = zai.group_id)
9. 140.810 458.663 ↑ 1.0 1,160,484 1

Append (cost=0.00..20,870.74 rows=1,202,675 width=8) (actual time=0.342..458.663 rows=1,160,484 loops=1)

10. 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)

11. 317.844 317.844 ↑ 1.0 1,160,484 1

Seq Scan on zaiko_publish zps_1 (cost=0.00..20,870.74 rows=1,202,674 width=8) (actual time=0.332..317.844 rows=1,160,484 loops=1)

12. 0.004 0.077 ↑ 4.0 1 1

Hash (cost=1,119.83..1,119.83 rows=4 width=270) (actual time=0.077..0.077 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.001 0.073 ↑ 4.0 1 1

Append (cost=0.00..1,119.83 rows=4 width=270) (actual time=0.057..0.073 rows=1 loops=1)

14. 0.005 0.005 ↓ 0.0 0 1

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

  • Filter: ((id = orgid) AND (auction_date = '2019-06-07'::date))
15. 0.067 0.067 ↑ 3.0 1 1

Index Scan using zaiko_auction_date_idx on zaiko zai_1 (cost=0.42..1,119.83 rows=3 width=339) (actual time=0.052..0.067 rows=1 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 5.123 5.123 ↑ 1,000.0 1 1

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

18. 0.000 0.000 ↓ 0.0 0

Materialize (cost=1,934,566.41..1,938,453.61 rows=777,440 width=42) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Sort (cost=1,934,566.41..1,936,510.01 rows=777,440 width=42) (never executed)

  • Sort Key: variety.id
20. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=160.48..1,834,584.74 rows=777,440 width=42) (never executed)

  • Hash Cond: (variety.brand = flower.id)
21. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.14..1,806,812.79 rows=178,312 width=46) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..1,420.49 rows=17,833 width=35) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Seq Scan on catalog variety (cost=0.00..0.00 rows=1 width=40) (never executed)

  • Filter: (active = 1)
24. 0.000 0.000 ↓ 0.0 0

Seq Scan on catalog variety_1 (cost=0.00..1,327.25 rows=16,933 width=35) (never executed)

  • Filter: (active = 1)
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on catalog_brand variety_2 (cost=0.00..93.24 rows=899 width=40) (never executed)

  • Filter: (active = 1)
26. 0.000 0.000 ↓ 0.0 0

Append (cost=0.14..95.54 rows=570 width=38) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label (cost=0.14..1.60 rows=10 width=20) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
28. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_1 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
29. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_2 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
30. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_3 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
31. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_4 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
32. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_5 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
33. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_6 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
34. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_7 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
35. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_8 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
36. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_9 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
37. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_10 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
38. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_11 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
39. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_12 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
40. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_13 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
41. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_14 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
42. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_15 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
43. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_16 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
44. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_17 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
45. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_18 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
46. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_19 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
47. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_20 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
48. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_21 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
49. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_22 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
50. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_23 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
51. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_24 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
52. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_25 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
53. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_26 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
54. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_27 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
55. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_28 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
56. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_29 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
57. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_30 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
58. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_31 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
59. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_32 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
60. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_33 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
61. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_34 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
62. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_35 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
63. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_36 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
64. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_37 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
65. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_38 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
66. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_39 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
67. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_40 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
68. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_41 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
69. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_42 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
70. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_43 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
71. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_44 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
72. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_45 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
73. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_46 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
74. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_47 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
75. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_48 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
76. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_49 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
77. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_50 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
78. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_51 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
79. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_52 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
80. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_53 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
81. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_54 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
82. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_55 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
83. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_label_pkey on catalog_label ctg_label_56 (cost=0.15..1.68 rows=10 width=38) (never executed)

  • Index Cond: (id = ANY (variety.label_ids))
84. 0.000 0.000 ↓ 0.0 0

Hash (cost=149.44..149.44 rows=872 width=4) (never executed)

85. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..149.44 rows=872 width=4) (never executed)

86. 0.000 0.000 ↓ 0.0 0

Index Scan using catalog_brand_pkey on catalog_brand t0 (cost=0.28..8.29 rows=1 width=33) (never executed)

  • Index Cond: (id = 23674)
  • Filter: (active = 1)
87. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..140.95 rows=20 width=4) (never executed)

88. 0.000 0.000 ↓ 0.0 0

Seq Scan on catalog flower (cost=0.00..0.00 rows=1 width=4) (never executed)

  • Filter: ((active = 1) AND (id = ANY (t0.catalog_ids)))
89. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on catalog flower_1 (cost=42.95..80.35 rows=9 width=4) (never executed)

  • Recheck Cond: (id = ANY (t0.catalog_ids))
  • Filter: (active = 1)
90. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on catalog_pkey (cost=0.00..42.95 rows=10 width=0) (never executed)

  • Index Cond: (id = ANY (t0.catalog_ids))
91. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on catalog_brand flower_2 (cost=30.83..60.60 rows=10 width=4) (never executed)

  • Recheck Cond: (id = ANY (t0.catalog_ids))
  • Filter: (active = 1)
92. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on catalog_brand_pkey (cost=0.00..30.82 rows=10 width=0) (never executed)

  • Index Cond: (id = ANY (t0.catalog_ids))
Planning time : 23.591 ms
Execution time : 623.140 ms