explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KQVW

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

Limit (cost=84.12..206.52 rows=1 width=885) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Unique (cost=84.12..206.52 rows=1 width=885) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=84.12..206.51 rows=1 width=885) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=83.97..96.23 rows=1 width=1,860) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=83.69..93.52 rows=1 width=1,849) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=81.19..84.73 rows=1 width=1,836) (actual rows= loops=)

  • Join Filter: (mcp.product_url_id = pu.id)
7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=60.15..63.63 rows=3 width=1,840) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=60.02..62.95 rows=3 width=1,828) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=59.88..62.26 rows=3 width=1,014) (actual rows= loops=)

  • Join Filter: (mcpl.ch_product_list_id = btu.batch_id)
10. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=59.60..59.67 rows=3 width=1,018) (actual rows= loops=)

  • Merge Cond: (mcp.id = submission_details.product)
11. 0.000 0.000 ↓ 0.0

Sort (cost=56.86..56.87 rows=3 width=1,010) (actual rows= loops=)

  • Sort Key: mcp.id
12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=36.04..56.83 rows=3 width=1,010) (actual rows= loops=)

  • Join Filter: ((mcp.imported_data_id IS NULL) OR (NOT (hashed SubPlan 14)))
13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.27..6.11 rows=5 width=12) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on ch_master_catalog_product_list mcpl (cost=0.00..1.43 rows=1 width=12) (actual rows= loops=)

  • Filter: (customer = 1)
15. 0.000 0.000 ↓ 0.0

Index Only Scan using ch_master_catalog_products_lists_1 on ch_master_catalog_products_lists mcpls (cost=0.27..4.53 rows=15 width=8) (actual rows= loops=)

  • Index Cond: (product_list_id = mcpl.id)
16. 0.000 0.000 ↓ 0.0

Index Scan using ch_master_catalog_products_pkey on ch_master_catalog_products mcp (cost=0.27..3.03 rows=1 width=1,002) (actual rows= loops=)

  • Index Cond: (id = mcpls.mc_product_id)
17.          

SubPlan (forNested Loop)

18. 0.000 0.000 ↓ 0.0

Seq Scan on ch_master_catalog_removed_items (cost=0.00..30.40 rows=2,040 width=8) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Sort (cost=2.75..2.77 rows=8 width=12) (actual rows= loops=)

  • Sort Key: submission_details.product
20. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=1.41..2.63 rows=8 width=12) (actual rows= loops=)

  • Hash Cond: (submission_details.id = ANY_subquery.max)
21. 0.000 0.000 ↓ 0.0

Seq Scan on submission_details (cost=0.00..1.10 rows=10 width=16) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=1.31..1.31 rows=8 width=4) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Subquery Scan on ANY_subquery (cost=1.15..1.31 rows=8 width=4) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1.15..1.23 rows=8 width=8) (actual rows= loops=)

  • Group Key: submission_details_1.product
25. 0.000 0.000 ↓ 0.0

Seq Scan on submission_details submission_details_1 (cost=0.00..1.10 rows=10 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Index Scan using batch_to_urls_imported_data_id on batch_to_urls btu (cost=0.28..0.85 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (imported_data_id = mcp.imported_data_id)
27. 0.000 0.000 ↓ 0.0

Index Scan using imported_data_id_index on ch_product_change cpc_text (cost=0.14..0.22 rows=1 width=818) (actual rows= loops=)

  • Index Cond: (imported_data_id = mcp.imported_data_id)
  • Filter: ((type = 1) AND (status = 2))
28. 0.000 0.000 ↓ 0.0

Index Scan using imported_data_id_index on ch_product_change cpc (cost=0.14..0.22 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (imported_data_id = mcp.imported_data_id)
  • Filter: (status = 2)
29. 0.000 0.000 ↓ 0.0

Materialize (cost=21.03..21.06 rows=1 width=4) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Unique (cost=21.03..21.04 rows=1 width=4) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Sort (cost=21.03..21.04 rows=1 width=4) (actual rows= loops=)

  • Sort Key: pu.id
32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..21.02 rows=1 width=4) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..20.71 rows=1 width=12) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Seq Scan on taxonomy_column_values tcv_8 (cost=0.00..12.40 rows=1 width=4) (actual rows= loops=)

  • Filter: ((taxonomy_value_id = ANY ('{261,73}'::integer[])) AND (taxonomy_column_id = 8))
35. 0.000 0.000 ↓ 0.0

Index Scan using product_url_product_id on product_url pu (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (product_id = tcv_8.product_id)
36. 0.000 0.000 ↓ 0.0

Index Only Scan using product_pkey on product p (cost=0.28..0.31 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = pu.product_id)
37. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2.50..8.78 rows=1 width=17) (actual rows= loops=)

  • Hash Cond: (c.id = mcc.ch_customer)
38. 0.000 0.000 ↓ 0.0

Seq Scan on customers c (cost=0.00..5.59 rows=259 width=17) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=2.49..2.49 rows=1 width=8) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on ch_master_catalog_customer mcc (cost=0.00..2.49 rows=1 width=8) (actual rows= loops=)

  • Filter: (id = 1)
41. 0.000 0.000 ↓ 0.0

Index Scan using crawler_list_imported_data_id on crawler_list cl (cost=0.28..2.70 rows=1 width=15) (actual rows= loops=)

  • Index Cond: (imported_data_id = mcp.imported_data_id)
42. 0.000 0.000 ↓ 0.0

Index Scan using ch_product_upc_vendor_stock_number_upc_index on ch_product_upc_vendor_stock_number puvsn (cost=0.15..0.43 rows=3 width=64) (actual rows= loops=)

  • Index Cond: (lpad((mcp.upc)::text, 12, '0'::text) = (upc)::text)
43.          

SubPlan (forNested Loop Left Join)

44. 0.000 0.000 ↓ 0.0

Limit (cost=1.20..1.21 rows=1 width=175) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Sort (cost=1.20..1.21 rows=1 width=175) (actual rows= loops=)

  • Sort Key: cpc_img.id DESC
46. 0.000 0.000 ↓ 0.0

Seq Scan on ch_product_change cpc_img (cost=0.00..1.19 rows=1 width=175) (actual rows= loops=)

  • Filter: ((imported_data_id = mcp.imported_data_id) AND (type = 2) AND (status = 2))
47. 0.000 0.000 ↓ 0.0

Limit (cost=1.20..1.21 rows=1 width=36) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Sort (cost=1.20..1.21 rows=1 width=36) (actual rows= loops=)

  • Sort Key: cpc_img_1.id DESC
49. 0.000 0.000 ↓ 0.0

Seq Scan on ch_product_change cpc_img_1 (cost=0.00..1.19 rows=1 width=36) (actual rows= loops=)

  • Filter: ((imported_data_id = mcp.imported_data_id) AND (type = 2) AND (status = 2))
50. 0.000 0.000 ↓ 0.0

Aggregate (cost=7.42..7.43 rows=1 width=32) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..7.42 rows=1 width=1,086) (actual rows= loops=)

  • Join Filter: (cpci_img.ch_product_change_id = cpc_img_2.id)
52. 0.000 0.000 ↓ 0.0

Seq Scan on ch_product_change cpc_img_2 (cost=0.00..1.19 rows=1 width=4) (actual rows= loops=)

  • Filter: ((imported_data_id = mcp.imported_data_id) AND (type = 2) AND (status = 2))
53. 0.000 0.000 ↓ 0.0

Seq Scan on ch_product_change_image cpci_img (cost=0.00..6.19 rows=3 width=1,090) (actual rows= loops=)

  • Filter: ((NOT is_library_only) AND (media_type = 1))
54. 0.000 0.000 ↓ 0.0

Aggregate (cost=20.92..20.93 rows=1 width=32) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Subquery Scan on y (cost=20.83..20.89 rows=5 width=642) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Sort (cost=20.83..20.84 rows=5 width=622) (actual rows= loops=)

  • Sort Key: mcpi_img.id
57. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ch_master_catalog_product_images mcpi_img (cost=4.31..20.77 rows=5 width=622) (actual rows= loops=)

  • Recheck Cond: (mc_product_id = mcp.id)
  • Filter: (media_type = 1)
58. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on mc_product_id (cost=0.00..4.31 rows=5 width=0) (actual rows= loops=)

  • Index Cond: (mc_product_id = mcp.id)
59. 0.000 0.000 ↓ 0.0

Limit (cost=0.15..3.37 rows=1 width=4) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Index Scan using ch_master_catalog_product_local_to_ch_unique_idx on ch_master_catalog_product_local_to_ch mcplc (cost=0.15..32.33 rows=10 width=4) (actual rows= loops=)

  • Index Cond: (local_product_id = mcp.id)
61. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.82..22.34 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (btu3.batch_id = mcpl3.ch_product_list_id)
  • Filter: ((btu3.batch_id IS NOT NULL) OR (mcpl3.type = 2))
62. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.54..14.02 rows=1 width=12) (actual rows= loops=)

  • Join Filter: (mcpls3.product_list_id = mcpl3.id)
63. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.54..12.59 rows=1 width=8) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Index Only Scan using ch_master_catalog_products_lists_pkey on ch_master_catalog_products_lists mcpls3 (cost=0.27..4.29 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (mc_product_id = mcp.id)
65. 0.000 0.000 ↓ 0.0

Index Scan using ch_master_catalog_products_pkey on ch_master_catalog_products mcp3 (cost=0.27..8.29 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = mcp.id)
66. 0.000 0.000 ↓ 0.0

Seq Scan on ch_master_catalog_product_list mcpl3 (cost=0.00..1.43 rows=1 width=12) (actual rows= loops=)

  • Filter: (customer = mcpl.customer)
67. 0.000 0.000 ↓ 0.0

Index Scan using batch_to_urls_imported_data_id on batch_to_urls btu3 (cost=0.28..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (imported_data_id = mcp3.imported_data_id)
68. 0.000 0.000 ↓ 0.0

Aggregate (cost=1.17..1.18 rows=1 width=8) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Seq Scan on ch_product_change cpc_status (cost=0.00..1.17 rows=1 width=4) (actual rows= loops=)

  • Filter: ((imported_data_id = mcp.imported_data_id) AND (status = 2))
70. 0.000 0.000 ↓ 0.0

Aggregate (cost=10.88..10.89 rows=1 width=8) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ch_master_catalog_product_edit_history mcpeh (cost=4.29..10.88 rows=2 width=8) (actual rows= loops=)

  • Recheck Cond: (mc_product_id = mcp.id)
72. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ch_master_catalog_product_edit_history_mc_product_id_index (cost=0.00..4.29 rows=2 width=0) (actual rows= loops=)

  • Index Cond: (mc_product_id = mcp.id)
73. 0.000 0.000 ↓ 0.0

Limit (cost=3.64..3.64 rows=1 width=226) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Sort (cost=3.64..3.64 rows=1 width=226) (actual rows= loops=)

  • Sort Key: cpc_user.updated_date DESC
75. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.15..3.63 rows=1 width=226) (actual rows= loops=)

  • Hash Cond: (u.id = cpc_user.user_id)
76. 0.000 0.000 ↓ 0.0

Seq Scan on users u (cost=0.00..2.34 rows=34 width=440) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Hash (cost=1.14..1.14 rows=1 width=12) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Seq Scan on ch_product_change cpc_user (cost=0.00..1.14 rows=1 width=12) (actual rows= loops=)

  • Filter: (imported_data_id = mcp.imported_data_id)
79. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.20..8.21 rows=1 width=32) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Sort (cost=8.18..8.18 rows=1 width=40) (actual rows= loops=)

  • Sort Key: mcsc.schedule_at
81. 0.000 0.000 ↓ 0.0

Index Scan using ch_master_catalog_scheduled_content_status_index on ch_master_catalog_scheduled_content mcsc (cost=0.14..8.17 rows=1 width=40) (actual rows= loops=)

  • Index Cond: ((status)::text = 'created'::text)
  • Filter: ((product_id = mcp.id) AND (schedule_at >= now()))
82. 0.000 0.000 ↓ 0.0

Aggregate (cost=1.13..1.14 rows=1 width=8) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Seq Scan on submission_details submission_details_2 (cost=0.00..1.12 rows=1 width=8) (actual rows= loops=)

  • Filter: (product = mcp.id)
84. 0.000 0.000 ↓ 0.0

Aggregate (cost=20.77..20.78 rows=1 width=8) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on ch_master_catalog_product_images mcpi_img_um (cost=4.31..20.77 rows=1 width=0) (actual rows= loops=)

  • Recheck Cond: (mc_product_id = mcp.id)
  • Filter: (s3_sync_status = ANY ('{1,3}'::integer[]))
86. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on mc_product_id (cost=0.00..4.31 rows=5 width=0) (actual rows= loops=)

  • Index Cond: (mc_product_id = mcp.id)
87. 0.000 0.000 ↓ 0.0

Aggregate (cost=7.42..7.43 rows=1 width=8) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..7.41 rows=3 width=0) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Seq Scan on ch_product_change cpc_img_um (cost=0.00..1.19 rows=1 width=0) (actual rows= loops=)

  • Filter: ((imported_data_id = mcp.imported_data_id) AND (type = 2) AND (status = 2))
90. 0.000 0.000 ↓ 0.0

Seq Scan on ch_product_change_image cpci_img_um (cost=0.00..6.19 rows=3 width=0) (actual rows= loops=)

  • Filter: (s3_sync_status = ANY ('{1,3}'::integer[]))