explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TCNc

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=2,685,493,373.08..2,685,661,821.43 rows=3,368,967 width=401) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=2,685,493,373.08..2,685,501,795.50 rows=3,368,967 width=401) (actual rows= loops=)

  • Sort Key: product.legacy_cod, item.catalog_item_id, product.product_name, item.owner_id, product.product_description, class_card.category_id, class_card.legacy_cod, class_card.category_name, (COALESCE((CASE WHEN (count(1) > 0) THEN array_remove(ARRAY[CASE bool_or(availability_configuration_1.include_sunday) WHEN CASE_TEST_EXPR THEN 0 ELSE NULL::integer END, CASE bool_or(availability_configuration_1.include_monday) WHEN CASE_TEST_EXPR THEN 1 ELSE NULL::integer END, CASE bool_or(availability_configuration_1.include_tuesday) WHEN CASE_TEST_EXPR THEN 2 ELSE NULL::integer END, CASE bool_or(availability_configuration_1.include_wednesday) WHEN CASE_TEST_EXPR THEN 3 ELSE NULL::integer END, CASE bool_or(availability_configuration_1.include_thursday) WHEN CASE_TEST_EXPR THEN 4 ELSE NULL::integer END, CASE bool_or(availability_configuration_1.include_friday) WHEN CASE_TEST_EXPR THEN 5 ELSE NULL::integer END, CASE bool_or(availability_configuration_1.include_saturday) WHEN CASE_TEST_EXPR THEN 6 ELSE NULL::integer END], NULL::integer) ELSE NULL::integer[] END), (array_remove(ARRAY[CASE bool_or(availability_configuration.include_sunday) WHEN CASE_TEST_EXPR THEN 0 ELSE NULL::integer END, CASE bool_or(availability_configuration.include_monday) WHEN CASE_TEST_EXPR THEN 1 ELSE NULL::integer END, CASE bool_or(availability_configuration.include_tuesday) WHEN CASE_TEST_EXPR THEN 2 ELSE NULL::integer END, CASE bool_or(availability_configuration.include_wednesday) WHEN CASE_TEST_EXPR THEN 3 ELSE NULL::integer END, CASE bool_or(availability_configuration.include_thursday) WHEN CASE_TEST_EXPR THEN 4 ELSE NULL::integer END, CASE bool_or(availability_configuration.include_friday) WHEN CASE_TEST_EXPR THEN 5 ELSE NULL::integer END, CASE bool_or(availability_configuration.include_saturday) WHEN CASE_TEST_EXPR THEN 6 ELSE NULL::integer END], NULL::integer)), '{0,1,2,3,4,5,6}'::integer[])), ((((array_agg("substring"((legacy_config.chave)::text, 6))) || (array_agg("substring"((legacy_config_1.chave)::text, 6)))) || CASE WHEN (item.promotion_status = 'S'::bpchar) THEN '{MKT::HIGHLIGHT}'::text[] ELSE '{}'::text[] END)), (sum(cg.min_group_price)), ((product.product_photo)::text), rp1.promo_original_price, rp1.run_price, (COALESCE((CASE WHEN (count(1) > 0) THEN array_agg((times_1_1.times)::time without time zone) ELSE NULL::time without time zone[] END), (array_agg((times_1.times)::time without time zone)))), (((setweight(to_tsvector('portuguese'::regconfig, (product.product_name)::text), 'A'::"char") || setweight(to_tsvector('portuguese'::regconfig, (COALESCE(product.product_description, ''::character varying))::text), 'B'::"char")) || setweight(to_tsvector('portuguese'::regconfig, (COALESCE(class_card.category_name, ''::character varying))::text), 'C'::"char"))), taxonomy.taxonomy_level_1, taxonomy.taxonomy_level_2, catalog.catalog_group
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=946,642.32..2,684,683,959.29 rows=3,368,967 width=401) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=946,549.44..2,368,176,687.63 rows=3,368,967 width=476) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=946,544.89..2,351,845,620.09 rows=3,368,967 width=444) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=946,529.78..2,299,930,556.02 rows=3,368,967 width=412) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=946,312.22..1,565,950,474.22 rows=3,368,967 width=396) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=945,884.45..123,788,186.05 rows=3,368,967 width=380) (actual rows= loops=)

  • Hash Cond: (product.taxonomy_id = taxonomy.taxonomy_id)
  • Join Filter: (product.taxonomy_classifier_score >= 0.4)
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=945,066.94..123,337,141.05 rows=3,368,967 width=342) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=945,066.37..106,896,582.09 rows=3,368,967 width=334) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=945,056.33..72,044,532.13 rows=3,368,967 width=302) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=945,038.57..11,183,981.21 rows=3,368,967 width=270) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash Join (cost=945,038.00..4,010,446.36 rows=5,200,983 width=120) (actual rows= loops=)

  • Hash Cond: (item.catalog_id = catalog.catalog_id)
14. 0.000 0.000 ↓ 0.0

Hash Join (cost=798,366.89..3,844,703.73 rows=7,264,957 width=120) (actual rows= loops=)

  • Hash Cond: (item.category_id = class_card.category_id)
15. 0.000 0.000 ↓ 0.0

Seq Scan on catalog_item item (cost=0.00..3,018,409.77 rows=10,638,769 width=82) (actual rows= loops=)

  • Filter: ((status = 'A'::bpchar) AND ((operation_modes IS NULL) OR ('DELIVERY'::ifood_catalog.operationmodes = ANY (operation_modes))))
16. 0.000 0.000 ↓ 0.0

Hash (cost=509,775.89..509,775.89 rows=2,815,522 width=38) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on category class_card (cost=0.00..509,775.89 rows=2,815,522 width=38) (actual rows= loops=)

  • Filter: (((violation_classifier_score IS NULL) OR (violation_classifier_score < 0.9)) AND (status = 'A'::bpchar))
18. 0.000 0.000 ↓ 0.0

Hash (cost=90,890.61..90,890.61 rows=544,200 width=32) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on catalog (cost=0.00..90,890.61 rows=544,200 width=32) (actual rows= loops=)

  • Filter: (status = 'A'::bpchar)
20. 0.000 0.000 ↓ 0.0

Index Scan using product_product_id_pkey on product (cost=0.57..1.38 rows=1 width=150) (actual rows= loops=)

  • Index Cond: (product_id = item.product_id)
  • Filter: (((violation_classifier_score IS NULL) OR (violation_classifier_score < 0.9)) AND (status = 'A'::bpchar))
21. 0.000 0.000 ↓ 0.0

Aggregate (cost=17.76..17.87 rows=1 width=32) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.69..17.73 rows=2 width=7) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.12..16.12 rows=2 width=32) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Index Scan using availability_product_id_fk_idx on availability a (cost=0.56..6.78 rows=2 width=16) (actual rows= loops=)

  • Index Cond: (product_id = product.product_id)
25. 0.000 0.000 ↓ 0.0

Index Scan using availability_option_availability_option_id_pkey on availability_option ao (cost=0.56..4.67 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (availability_option_id = a.availability_option_id)
  • Filter: (status = 'A'::bpchar)
26. 0.000 0.000 ↓ 0.0

Index Scan using availability_configuration_availability_option_id_fk_idx on availability_configuration (cost=0.56..0.70 rows=1 width=23) (actual rows= loops=)

  • Index Cond: (availability_option_id = ao.availability_option_id)
27. 0.000 0.000 ↓ 0.0

Aggregate (cost=10.04..10.15 rows=1 width=32) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.56..10.02 rows=1 width=7) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.00..9.22 rows=1 width=32) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Index Scan using availability_category_id_fk_idx on availability a_1 (cost=0.44..4.54 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (category_id = class_card.category_id)
31. 0.000 0.000 ↓ 0.0

Index Scan using availability_option_availability_option_id_pkey on availability_option ao_1 (cost=0.56..4.67 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (availability_option_id = a_1.availability_option_id)
  • Filter: (status = 'A'::bpchar)
32. 0.000 0.000 ↓ 0.0

Index Scan using availability_configuration_availability_option_id_fk_idx on availability_configuration availability_configuration_1 (cost=0.56..0.70 rows=1 width=23) (actual rows= loops=)

  • Index Cond: (availability_option_id = ao_1.availability_option_id)
33. 0.000 0.000 ↓ 0.0

Limit (cost=0.57..4.68 rows=1 width=16) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Index Scan Backward using run_price_product_id_owner_id_effective_start_date_idx on run_price rp1 (cost=0.57..4.68 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((product_id = product.product_id) AND (owner_id = item.owner_id) AND (effective_start_date <= now()))
35. 0.000 0.000 ↓ 0.0

Hash (cost=743.92..743.92 rows=718 width=80) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Subquery Scan on taxonomy (cost=528.52..743.92 rows=718 width=80) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

CTE Scan on r (cost=528.52..672.12 rows=718 width=80) (actual rows= loops=)

38.          

CTE r

39. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.14..528.52 rows=718 width=60) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Index Scan using taxonomy_parent_taxonomy_id_idx on taxonomy t (cost=0.14..5.00 rows=8 width=60) (actual rows= loops=)

  • Index Cond: (parent_taxonomy_id IS NULL)
41. 0.000 0.000 ↓ 0.0

Merge Join (cost=29.92..37.99 rows=71 width=60) (actual rows= loops=)

  • Merge Cond: (t_1.parent_taxonomy_id = r_1.taxonomy_id)
42. 0.000 0.000 ↓ 0.0

Sort (cost=11.39..11.59 rows=79 width=44) (actual rows= loops=)

  • Sort Key: t_1.parent_taxonomy_id
43. 0.000 0.000 ↓ 0.0

Seq Scan on taxonomy t_1 (cost=0.00..8.90 rows=79 width=44) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Sort (cost=18.53..18.73 rows=80 width=48) (actual rows= loops=)

  • Sort Key: r_1.taxonomy_id
45. 0.000 0.000 ↓ 0.0

WorkTable Scan on r r_1 (cost=0.00..16.00 rows=80 width=48) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Aggregate (cost=427.77..427.87 rows=1 width=32) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.73..417.77 rows=2,000 width=8) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.69..17.73 rows=2 width=16) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.12..16.13 rows=2 width=32) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Index Scan using availability_product_id_fk_idx on availability a_2 (cost=0.56..6.78 rows=2 width=16) (actual rows= loops=)

  • Index Cond: (product_id = item.product_id)
51. 0.000 0.000 ↓ 0.0

Index Scan using availability_option_availability_option_id_pkey on availability_option ao_2 (cost=0.56..4.68 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (availability_option_id = a_2.availability_option_id)
  • Filter: ((status <> 'D'::bpchar) AND (status = 'A'::bpchar))
52. 0.000 0.000 ↓ 0.0

Index Scan using availability_configuration_availability_option_id_fk_idx on availability_configuration availability_configuration_2 (cost=0.56..0.70 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (availability_option_id = ao_2.availability_option_id)
53. 0.000 0.000 ↓ 0.0

Function Scan on generate_series times_1 (cost=0.04..100.04 rows=1,000 width=8) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Aggregate (cost=217.56..217.67 rows=1 width=32) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.60..210.06 rows=1,000 width=8) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.56..10.02 rows=1 width=16) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.00..9.22 rows=1 width=32) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Index Scan using availability_category_id_fk_idx on availability a_3 (cost=0.44..4.54 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (category_id = item.category_id)
59. 0.000 0.000 ↓ 0.0

Index Scan using availability_option_availability_option_id_pkey on availability_option ao_3 (cost=0.56..4.68 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (availability_option_id = a_3.availability_option_id)
  • Filter: ((status <> 'D'::bpchar) AND (status = 'A'::bpchar))
60. 0.000 0.000 ↓ 0.0

Index Scan using availability_configuration_availability_option_id_fk_idx on availability_configuration availability_configuration_3 (cost=0.56..0.70 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (availability_option_id = ao_3.availability_option_id)
61. 0.000 0.000 ↓ 0.0

Function Scan on generate_series times_1_1 (cost=0.04..100.04 rows=1,000 width=8) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Aggregate (cost=15.11..15.21 rows=1 width=32) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Index Scan using legacy_config_product_id_idx on legacy_config (cost=0.43..15.08 rows=6 width=20) (actual rows= loops=)

  • Index Cond: (product_id = product.product_id)
64. 0.000 0.000 ↓ 0.0

Aggregate (cost=4.55..4.65 rows=1 width=32) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Index Scan using legacy_config_catalog_item_id_idx on legacy_config legacy_config_1 (cost=0.43..4.54 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (catalog_item_id = item.catalog_item_id)
66. 0.000 0.000 ↓ 0.0

Aggregate (cost=92.88..92.98 rows=1 width=32) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.12..92.84 rows=13 width=3) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Index Scan using cgm_product_id_fk_idx on customization_group_member cgm (cost=0.56..32.10 rows=13 width=16) (actual rows= loops=)

  • Index Cond: (product_id = product.product_id)
  • Filter: (status <> 'D'::bpchar)
69. 0.000 0.000 ↓ 0.0

Index Scan using customization_group_customization_group_id_pkey on customization_group cg (cost=0.56..4.67 rows=1 width=19) (actual rows= loops=)

  • Index Cond: (customization_group_id = cgm.customization_group_id)
  • Filter: (status <> 'D'::bpchar)