explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rwgZ

Settings
# exclusive inclusive rows x rows loops node
1. 5,699,416.529 5,699,416.529 ↓ 43.0 43 1

Subquery Scan on dqb (cost=1,254.42..1,254.44 rows=1 width=123) (actual time=5,699,142.202..5,699,416.529 rows=43 loops=1)

  • Output: dqb.card_value, dqb.product_id, dqb.is_no_longer_available, dqb.is_owned, dqb.id, dqb.site_id, dqb.details, dqb.listed_secondary_value, dqb.oos_marketplace_owned_value, dqb.no_reviews_value, dqb.no_ly_sales_data_value, dqb.no_current_sales_data_value, dqb.marketplace_owned_top_ten_value, dqb.marketplace_value, dqb.in_store_only_value, dqb.pickup_only_value, dqb.online_only_value, dqb.only_one_keyword_value, dqb.positive_value, dqb.negative_value, dqb.undefined_value, dqb.inla_value, dqb.owned_value, dqb.missing_association_value, dqb.not_found_in_master_catalog_value, dqb.indeterminate_value
  • Buffers: shared hit=4324942540 read=522, temp read=1125720 written=8053
2.          

CTE products

3. 42.453 491.900 ↓ 12,874.0 12,874 1

Unique (cost=1,057.99..1,058.00 rows=1 width=227) (actual time=407.333..491.900 rows=12,874 loops=1)

  • Output: p_1.id, p_1.site_id, p_1.id, pu.id, pu.url, s.name
  • Buffers: shared hit=9515, temp read=503 written=503
4. 425.531 449.447 ↓ 12,874.0 12,874 1

Sort (cost=1,057.99..1,058.00 rows=1 width=227) (actual time=407.331..449.447 rows=12,874 loops=1)

  • Output: p_1.id, p_1.site_id, p_1.id, pu.id, pu.url, s.name
  • Sort Key: p_1.id, pu.url
  • Sort Method: external sort Disk: 4024kB
  • Buffers: shared hit=9515, temp read=503 written=503
5. 5.017 23.916 ↓ 12,874.0 12,874 1

Nested Loop (cost=1.84..1,057.98 rows=1 width=227) (actual time=0.160..23.916 rows=12,874 loops=1)

  • Output: p_1.id, p_1.site_id, p_1.id, pu.id, pu.url, s.name
  • Join Filter: (p_1.id = pu.product_id)
  • Buffers: shared hit=9515
6. 0.064 3.204 ↓ 43.0 43 1

Nested Loop (cost=1.41..1,053.83 rows=1 width=26) (actual time=0.138..3.204 rows=43 loops=1)

  • Output: p_1.id, p_1.site_id, s.name, tcv_power_sku.product_id
  • Inner Unique: true
  • Buffers: shared hit=1221
7. 0.066 2.968 ↓ 43.0 43 1

Nested Loop (cost=1.14..1,053.20 rows=1 width=12) (actual time=0.123..2.968 rows=43 loops=1)

  • Output: p_1.id, p_1.site_id, tcv_power_sku.product_id
  • Inner Unique: true
  • Buffers: shared hit=1092
8. 0.557 2.558 ↓ 5.4 43 1

Nested Loop (cost=0.84..1,050.62 rows=8 width=16) (actual time=0.099..2.558 rows=43 loops=1)

  • Output: p_1.id, p_1.site_id, tcv_power_sku.product_id, tcv_power_sku.taxonomy_value_id
  • Inner Unique: true
  • Join Filter: (tcv_power_sku.taxonomy_column_id = tc_power_sku.id)
  • Rows Removed by Join Filter: 730
  • Buffers: shared hit=963
9. 0.321 2.001 ↓ 48.3 773 1

Nested Loop (cost=0.84..1,047.60 rows=16 width=20) (actual time=0.062..2.001 rows=773 loops=1)

  • Output: p_1.id, p_1.site_id, tcv_power_sku.product_id, tcv_power_sku.taxonomy_column_id, tcv_power_sku.taxonomy_value_id
  • Buffers: shared hit=962
10. 0.580 0.580 ↑ 1.0 100 1

Index Scan using product_pkey on public.product p_1 (cost=0.42..164.84 rows=100 width=8) (actual time=0.043..0.580 rows=100 loops=1)

  • Output: p_1.id, p_1.site_id, p_1.reseller_id, p_1.secondary_id, p_1.upc, p_1.vsn, p_1.map, p_1.msrp
  • Index Cond: (p_1.id = ANY ('{29984,29222,26078,24480,24369,24332,24318,24292,24026,23716,19392,19216,17368,17358,11702,11694,11628,11626,11559,11525,11523,11500,11456,8488,5819,5067,4931,4900,4796,3775,3704,3514,3493,3468,3417,3376,3156,3010,3004,2897,2787,991,760,1083211,1082948,1082947,1082946,1082945,1082464,1082463,1082462,1082053,1081089,1081016,1080995,1080205,1080204,1080203,1080202,1080201,1080200,1080187,1080186,1080185,1080184,1080183,1080182,1080181,1080180,1080179,1080178,1080177,1080176,1080175,1080174,1080173,1080172,1080171,1080170,1080169,1080168,1080167,1080166,1080163,1079679,1079678,1079354,1079353,1079352,1079346,1079343,1079341,1079340,1079339,1079142,1079140,1079139,1079138,1079137,1079136}'::integer[]))
  • Buffers: shared hit=370
11. 1.100 1.100 ↑ 1.1 8 100

Index Scan using idx_taxonomy_column_values_product_id on public.taxonomy_column_values tcv_power_sku (cost=0.42..8.74 rows=9 width=12) (actual time=0.005..0.011 rows=8 loops=100)

  • Output: tcv_power_sku.id, tcv_power_sku.product_id, tcv_power_sku.taxonomy_column_id, tcv_power_sku.taxonomy_value_id, tcv_power_sku.originating_from_taxonomy_column_id
  • Index Cond: (tcv_power_sku.product_id = p_1.id)
  • Buffers: shared hit=592
12. 0.000 0.000 ↑ 8.0 1 773

Materialize (cost=0.00..1.21 rows=8 width=4) (actual time=0.000..0.000 rows=1 loops=773)

  • Output: tc_power_sku.id
  • Buffers: shared hit=1
13. 0.009 0.009 ↑ 8.0 1 1

Seq Scan on public.taxonomy_columns tc_power_sku (cost=0.00..1.17 rows=8 width=4) (actual time=0.007..0.009 rows=1 loops=1)

  • Output: tc_power_sku.id
  • Filter: tc_power_sku.is_for_power_sku
  • Rows Removed by Filter: 16
  • Buffers: shared hit=1
14. 0.344 0.344 ↑ 1.0 1 43

Index Scan using taxonomy_values_pkey on public.taxonomy_values tv_power_sku (cost=0.29..0.32 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=43)

  • Output: tv_power_sku.id, tv_power_sku.name
  • Index Cond: (tv_power_sku.id = tcv_power_sku.taxonomy_value_id)
  • Filter: (lower(tv_power_sku.name) = 'yes'::text)
  • Buffers: shared hit=129
15. 0.172 0.172 ↑ 1.0 1 43

Index Scan using sites_id_pkey on public.sites s (cost=0.27..0.63 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=43)

  • Output: s.name, s.id
  • Index Cond: (s.id = p_1.site_id)
  • Buffers: shared hit=129
16. 15.695 15.695 ↓ 7.0 299 43

Index Scan using product_url_product_id on public.product_url pu (cost=0.43..3.61 rows=43 width=205) (actual time=0.014..0.365 rows=299 loops=43)

  • Output: pu.id, pu.url, pu.product_id, pu.image_url, pu.title, pu.description, pu.model, pu.upc, pu.sku, pu.updated_at, pu.customer_uploaded_upc
  • Index Cond: (pu.product_id = tcv_power_sku.product_id)
  • Buffers: shared hit=8294
17.          

CTE products_rsri

18. 980,706.718 5,678,460.339 ↓ 668.0 668 1

Nested Loop (cost=0.00..2.83 rows=1 width=437) (actual time=2.214..5,678,460.339 rows=668 loops=1)

  • Output: rsri.id, rsri.site_id, rsri.search_items_brands_relation_id, rsri.ranking, rsri.date_of_upload, rsri.best_seller_ranking, rsri.search_term_in_title, rsri.is_out_of_stock, rsri.is_in_store_only, rsri.currency, rsri.results_per_page, rsri.price, rsri.url_id, rsri.ranking_by_price, rsri.prime, rsri.seller_category_id, rsri.pickup_only, rsri.shipping, rsri.department, rsri.ranking_product_category_id, rsri.deliver_in, rsri.price_details_in_cart, rsri.no_longer_available, rsri.price_subscribe_save, rsri.zip_code_id, rsri.is_on_first_page, rsri.additional, rsri.search_term_id, rsri.brand_id, rsri.is_sponsored, rsri.is_low_stock, rsri._old_search_term_id, rsri._old_search_term_id2, rsri.crawled_at, rsri.imported_at
  • Join Filter: (rsri.url_id = p_2.url_id)
  • Rows Removed by Join Filter: 4388681562
  • Buffers: shared hit=4324930182, temp read=502 written=501
19. 121.139 121.139 ↓ 12,874.0 12,874 1

CTE Scan on products p_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..121.139 rows=12,874 loops=1)

  • Output: p_2.id, p_2.site_id, p_2.product_id, p_2.url_id, p_2.url, p_2.site_name
  • Buffers: temp read=502 written=501
20. 603,880.718 4,697,632.482 ↓ 170,447.5 340,895 12,874

Append (cost=0.00..2.78 rows=2 width=436) (actual time=0.014..364.893 rows=340,895 loops=12,874)

  • Buffers: shared hit=4324930182
21. 0.000 0.000 ↓ 0.0 0 12,874

Seq Scan on public.ranking_search_results_items rsri (cost=0.00..0.00 rows=1 width=311) (actual time=0.000..0.000 rows=0 loops=12,874)

  • Output: rsri.id, rsri.site_id, rsri.search_items_brands_relation_id, rsri.ranking, rsri.date_of_upload, rsri.best_seller_ranking, rsri.search_term_in_title, rsri.is_out_of_stock, rsri.is_in_store_only, rsri.currency, rsri.results_per_page, rsri.price, rsri.url_id, rsri.ranking_by_price, rsri.prime, rsri.seller_category_id, rsri.pickup_only, rsri.shipping, rsri.department, rsri.ranking_product_category_id, rsri.deliver_in, rsri.price_details_in_cart, rsri.no_longer_available, rsri.price_subscribe_save, rsri.zip_code_id, rsri.is_on_first_page, rsri.additional, rsri.search_term_id, rsri.brand_id, rsri.is_sponsored, rsri.is_low_stock, rsri._old_search_term_id, rsri._old_search_term_id2, rsri.crawled_at, rsri.imported_at
  • Filter: (rsri.date_of_upload = '2019-12-30'::date)
22. 4,093,751.764 4,093,751.764 ↓ 340,895.0 340,895 12,874

Index Scan using ranking_search_results_items_2019_4_date_of_upload_url_id_idx on public.ranking_search_results_items_2019_4 rsri_1 (cost=0.56..2.78 rows=1 width=560) (actual time=0.013..317.986 rows=340,895 loops=12,874)

  • Output: rsri_1.id, rsri_1.site_id, rsri_1.search_items_brands_relation_id, rsri_1.ranking, rsri_1.date_of_upload, rsri_1.best_seller_ranking, rsri_1.search_term_in_title, rsri_1.is_out_of_stock, rsri_1.is_in_store_only, rsri_1.currency, rsri_1.results_per_page, rsri_1.price, rsri_1.url_id, rsri_1.ranking_by_price, rsri_1.prime, rsri_1.seller_category_id, rsri_1.pickup_only, rsri_1.shipping, rsri_1.department, rsri_1.ranking_product_category_id, rsri_1.deliver_in, rsri_1.price_details_in_cart, rsri_1.no_longer_available, rsri_1.price_subscribe_save, rsri_1.zip_code_id, rsri_1.is_on_first_page, rsri_1.additional, rsri_1.search_term_id, rsri_1.brand_id, rsri_1.is_sponsored, rsri_1.is_low_stock, rsri_1._old_search_term_id, rsri_1._old_search_term_id2, rsri_1.crawled_at, rsri_1.imported_at
  • Index Cond: (rsri_1.date_of_upload = '2019-12-30'::date)
  • Buffers: shared hit=4324930182
23.          

CTE products_marketplace

24. 3.405 317.747 ↓ 7.6 2,220 1

Nested Loop (cost=0.59..186.44 rows=294 width=32) (actual time=2.029..317.747 rows=2,220 loops=1)

  • Output: product_marketplace.id, product_marketplace.rsri_id, product_marketplace.price, product_marketplace.currency, product_marketplace.marketplace_id, product_marketplace.first_party_owned, product_marketplace.buy_box_owned, product_marketplace.left_in_stock, product_marketplace.condition
  • Buffers: shared hit=2843 read=522
25. 1.511 1.718 ↓ 668.0 668 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.475..1.718 rows=668 loops=1)

  • Output: products_rsri.id
  • Group Key: products_rsri.id
26. 0.207 0.207 ↓ 668.0 668 1

CTE Scan on products_rsri (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.207 rows=668 loops=1)

  • Output: products_rsri.id, products_rsri.site_id, products_rsri.search_items_brands_relation_id, products_rsri.ranking, products_rsri.date_of_upload, products_rsri.best_seller_ranking, products_rsri.search_term_in_title, products_rsri.is_out_of_stock, products_rsri.is_in_store_only, products_rsri.currency, products_rsri.results_per_page, products_rsri.price, products_rsri.url_id, products_rsri.ranking_by_price, products_rsri.prime, products_rsri.seller_category_id, products_rsri.pickup_only, products_rsri.shipping, products_rsri.department, products_rsri.ranking_product_category_id, products_rsri.deliver_in, products_rsri.price_details_in_cart, products_rsri.no_longer_available, products_rsri.price_subscribe_save, products_rsri.zip_code_id, products_rsri.is_on_first_page, products_rsri.additional, products_rsri.search_term_id, products_rsri.brand_id, products_rsri.is_sponsored, products_rsri.is_low_stock, products_rsri._old_search_term_id, products_rsri._old_search_term_id2, products_rsri.crawled_at, products_rsri.imported_at
27. 312.624 312.624 ↑ 98.0 3 668

Index Scan using rsri_id_index on public.product_marketplace (cost=0.57..183.46 rows=294 width=32) (actual time=0.449..0.468 rows=3 loops=668)

  • Output: product_marketplace.id, product_marketplace.rsri_id, product_marketplace.price, product_marketplace.currency, product_marketplace.marketplace_id, product_marketplace.first_party_owned, product_marketplace.buy_box_owned, product_marketplace.left_in_stock, product_marketplace.condition
  • Index Cond: (product_marketplace.rsri_id = products_rsri.id)
  • Buffers: shared hit=2843 read=522
28.          

CTE card_values

29. 11,706.699 5,690,496.726 ↓ 2,222.0 2,222 1

Nested Loop (cost=0.03..7.09 rows=1 width=7) (actual time=5,678,466.926..5,690,496.726 rows=2,222 loops=1)

  • Output: CASE WHEN (rsri_2.id IS NOT NULL) THEN true ELSE false END, p_3.product_id, rsri_2.no_longer_available, (((NOT rsri_2.is_out_of_stock) OR (NOT COALESCE(((rsri_2.additional ->> 'is_first_party_owned'::text))::boolean, false))) AND (COALESCE(((rsri_2.additional ->> 'is_first_party_owned'::text))::boolean, false) OR COALESCE(((rsri_2.additional ->> 'is_sold_by_1p'::text))::boolean, true)) AND (COALESCE(pmp.first_party_owned, true) OR COALESCE(((rsri_2.additional ->> 'is_first_party_owned'::text))::boolean, false)))
  • Join Filter: (rsri_2.url_id = p_3.url_id)
  • Rows Removed by Join Filter: 28603806
  • Buffers: shared hit=4324933025 read=522, temp read=1118168 written=502
30. 5,678,790.027 5,678,790.027 ↓ 2,222.0 2,222 1

Hash Right Join (cost=0.03..7.02 rows=1 width=43) (actual time=5,678,465.881..5,678,790.027 rows=2,222 loops=1)