explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 53vW

Settings
# exclusive inclusive rows x rows loops node
1. 0.046 2,198.466 ↑ 1.0 10 1

Sort (cost=1,039,383.70..1,039,383.73 rows=10 width=317) (actual time=2,198.465..2,198.466 rows=10 loops=1)

  • Output: p.product_id, p.title, p.upc, p.reseller_id, p.url, p.value, p.positive_value, p.details, ((upper("substring"((s.name)::text, 1, 1)) || "substring"((s.name)::text, 2, length((s.name)::text)))), s.high_ratings_disabled, p.positive, p.negative, p.not_found_in_master_catalog, p.inla, p.undefined, mp.mc_product_id, mp.mc_customer_id, mp.customer_name, (CASE WHEN (p.inla_value = 1) THEN 3 WHEN (p.undefined_value = 1) THEN 4 WHEN (p.positive_value = 1) THEN 2 ELSE 1 END)
  • Sort Key: (CASE WHEN (p.inla_value = 1) THEN 3 WHEN (p.undefined_value = 1) THEN 4 WHEN (p.positive_value = 1) THEN 2 ELSE 1 END), p.value, p.title
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=121946 read=60272, temp read=13382 written=13396
2.          

CTE distinct_products

3. 18.881 1,850.472 ↑ 19.5 7,507 1

Unique (cost=1,034,481.03..1,035,211.46 rows=146,086 width=297) (actual time=1,788.765..1,850.472 rows=7,507 loops=1)

  • Output: p_1.id, p_1.site_id, p_1.reseller_id, pu.url, pu.id, (CASE WHEN ((COALESCE(pu.upc, (pu.customer_uploaded_upc)::text) <> ''::text) AND (COALESCE(pu.upc, (pu.customer_uploaded_upc)::text) IS NOT NULL)) THEN (replace(COALESCE(pu.upc, (pu.customer_uploaded_upc)::text), ';'::text, '
  • Buffers: shared hit=120065 read=60178, temp read=13382 written=13396
4. 435.981 1,831.591 ↓ 1.0 146,644 1

Sort (cost=1,034,481.03..1,034,846.24 rows=146,086 width=297) (actual time=1,788.763..1,831.591 rows=146,644 loops=1)

  • Output: p_1.id, p_1.site_id, p_1.reseller_id, pu.url, pu.id, (CASE WHEN ((COALESCE(pu.upc, (pu.customer_uploaded_upc)::text) <> ''::text) AND (COALESCE(pu.upc, (pu.customer_uploaded_upc)::text) IS NOT NULL)) THEN (replace(COALESCE(pu.upc, (pu.customer_uploaded_upc)::text), ';'::text, '
  • Sort Key: p_1.id, pu.updated_at DESC, pu.id DESC
  • Sort Method: external sort Disk: 52872kB
  • Buffers: shared hit=120065 read=60178, temp read=13382 written=13396
5. 203.254 1,395.610 ↓ 1.0 146,644 1

WindowAgg (cost=49,272.01..1,009,956.94 rows=146,086 width=297) (actual time=1,112.469..1,395.610 rows=146,644 loops=1)

  • Output: p_1.id, p_1.site_id, p_1.reseller_id, pu.url, pu.id, CASE WHEN ((COALESCE(pu.upc, (pu.customer_uploaded_upc)::text) <> ''::text) AND (COALESCE(pu.upc, (pu.customer_uploaded_upc)::text) IS NOT NULL)) THEN (replace(COALESCE(pu.upc, (pu.customer_uploaded_upc)::text), ';'::text, '
  • Buffers: shared hit=120062 read=60178, temp read=6773 written=6787
6. 242.480 1,173.662 ↓ 1.0 146,644 1

Sort (cost=49,272.01..49,637.22 rows=146,086 width=337) (actual time=1,112.454..1,173.662 rows=146,644 loops=1)

  • Output: (char_length(pu.title)), pu.product_id, p_1.id, p_1.site_id, p_1.reseller_id, pu.url, pu.id, pu.upc, pu.customer_uploaded_upc, pu.title, pu.updated_at
  • Sort Key: pu.product_id, (char_length(pu.title)) DESC NULLS LAST
  • Sort Method: external merge Disk: 54184kB
  • Buffers: shared hit=113456 read=60151, temp read=6773 written=6787
7. 118.976 931.182 ↓ 1.0 146,644 1

Nested Loop (cost=0.85..23,286.26 rows=146,086 width=337) (actual time=0.089..931.182 rows=146,644 loops=1)

  • Output: char_length(pu.title), pu.product_id, p_1.id, p_1.site_id, p_1.reseller_id, pu.url, pu.id, pu.upc, pu.customer_uploaded_upc, pu.title, pu.updated_at
  • Join Filter: (cdt.product_id = pu.product_id)
  • Buffers: shared hit=113456 read=60151
8. 7.363 129.069 ↑ 1.0 7,507 1

Nested Loop (cost=0.42..11,416.62 rows=7,507 width=26) (actual time=0.045..129.069 rows=7,507 loops=1)

  • Output: cdt.product_id, p_1.id, p_1.site_id, p_1.reseller_id
  • Inner Unique: true
  • Buffers: shared hit=29510 read=586
9. 1.594 1.594 ↑ 1.0 7,507 1

Seq Scan on public.cars_dashboard_taxonomy cdt (cost=0.00..123.07 rows=7,507 width=4) (actual time=0.014..1.594 rows=7,507 loops=1)

  • Output: cdt.id, cdt.country_id, cdt.site_id, cdt.product_id, cdt.updated_at
  • Buffers: shared hit=48
10. 120.112 120.112 ↑ 1.0 1 7,507

Index Scan using product_pkey on public.product p_1 (cost=0.42..1.50 rows=1 width=22) (actual time=0.016..0.016 rows=1 loops=7,507)

  • Output: p_1.id, p_1.site_id, p_1.reseller_id, p_1.secondary_id
  • Index Cond: (p_1.id = cdt.product_id)
  • Buffers: shared hit=29462 read=586
11. 683.137 683.137 ↑ 1.2 20 7,507

Index Scan using product_url_product_id on public.product_url pu (cost=0.43..1.22 rows=25 width=311) (actual time=0.008..0.091 rows=20 loops=7,507)

  • 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 = p_1.id)
  • Buffers: shared hit=83946 read=59565
12.          

SubPlan (forWindowAgg)

13. 0.000 18.694 ↑ 1.0 1 719

Limit (cost=5.43..6.55 rows=1 width=12) (actual time=0.025..0.026 rows=1 loops=719)

  • Output: cmcp.upc
  • Buffers: shared hit=6606 read=27
14.          

Initplan (forLimit)

15. 2.876 2.876 ↑ 1.0 1 719

Index Scan using product_pkey on public.product p2 (cost=0.42..2.64 rows=1 width=14) (actual time=0.003..0.004 rows=1 loops=719)

  • Output: p2.reseller_id
  • Index Cond: (p2.id = pu.product_id)
  • Buffers: shared hit=2881
16. 2.876 17.256 ↑ 2.0 1 719

Bitmap Heap Scan on public.ch_master_catalog_products cmcp (cost=2.79..5.02 rows=2 width=12) (actual time=0.024..0.024 rows=1 loops=719)

  • Output: cmcp.upc
  • Recheck Cond: ((cmcp.product_url_id = pu.id) OR ((cmcp.web_id)::text = ($1)::text))
  • Filter: (cmcp.upc IS NOT NULL)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=800
  • Buffers: shared hit=6606 read=27
17. 3.595 14.380 ↓ 0.0 0 719

BitmapOr (cost=2.79..2.79 rows=2 width=0) (actual time=0.020..0.020 rows=0 loops=719)

  • Buffers: shared hit=5806 read=27
18. 6.471 6.471 ↓ 0.0 0 719

Bitmap Index Scan on ch_master_catalog_products_product_url_id (cost=0.00..1.40 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=719)

  • Index Cond: (cmcp.product_url_id = pu.id)
  • Buffers: shared hit=1431 read=11
19. 4.314 4.314 ↑ 1.0 1 719

Bitmap Index Scan on ch_master_catalog_products_web_id_index (cost=0.00..1.40 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=719)

  • Index Cond: ((cmcp.web_id)::text = ($1)::text)
  • Buffers: shared hit=1494 read=16
20.          

CTE distinct_products_summary

21. 2.065 2,172.958 ↓ 37.5 7,507 1

Unique (cost=3,750.03..3,757.33 rows=200 width=300) (actual time=2,170.085..2,172.958 rows=7,507 loops=1)

  • Output: dp.product_id, dp.site_id, dp.reseller_id, dp.url, dp.url_id, dp.upc, dp.title, (COALESCE((cds.additional ->> 'details'::text), '-'::text)), (COALESCE((cds.additional ->> 'search_term_rankings'::text), '[]'::text)), (COALESCE((cds.additional ->> 'shelf_page_rankings'::text), '[]'::text)), (COALESCE(((cds.additional ->> 'no_reviews_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'in_store_only_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'online_only_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'pickup_only_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'only_one_keyword_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'positive_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'negative_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'undefined_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'inla_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'indeterminate_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'missing_association_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'not_found_in_master_catalog_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'marketplace_owned_top_ten_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'marketplace_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'listed_secondary_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'oos_marketplace_owned_value'::text))::integer, 0))
  • Buffers: shared hit=121681 read=60241, temp read=13382 written=13396
22. 5.741 2,170.893 ↓ 5.1 7,507 1

Sort (cost=3,750.03..3,753.68 rows=1,461 width=300) (actual time=2,170.084..2,170.893 rows=7,507 loops=1)

  • Output: dp.product_id, dp.site_id, dp.reseller_id, dp.url, dp.url_id, dp.upc, dp.title, (COALESCE((cds.additional ->> 'details'::text), '-'::text)), (COALESCE((cds.additional ->> 'search_term_rankings'::text), '[]'::text)), (COALESCE((cds.additional ->> 'shelf_page_rankings'::text), '[]'::text)), (COALESCE(((cds.additional ->> 'no_reviews_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'in_store_only_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'online_only_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'pickup_only_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'only_one_keyword_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'positive_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'negative_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'undefined_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'inla_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'indeterminate_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'missing_association_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'not_found_in_master_catalog_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'marketplace_owned_top_ten_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'marketplace_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'listed_secondary_value'::text))::integer, 0)), (COALESCE(((cds.additional ->> 'oos_marketplace_owned_value'::text))::integer, 0))
  • Sort Key: dp.product_id
  • Sort Method: quicksort Memory: 3309kB
  • Buffers: shared hit=121681 read=60241, temp read=13382 written=13396
23. 255.498 2,165.152 ↓ 5.1 7,507 1

Hash Join (cost=2.80..3,673.23 rows=1,461 width=300) (actual time=1,843.705..2,165.152 rows=7,507 loops=1)

  • Output: dp.product_id, dp.site_id, dp.reseller_id, dp.url, dp.url_id, dp.upc, dp.title, COALESCE((cds.additional ->> 'details'::text), '-'::text), COALESCE((cds.additional ->> 'search_term_rankings'::text), '[]'::text), COALESCE((cds.additional ->> 'shelf_page_rankings'::text), '[]'::text), COALESCE(((cds.additional ->> 'no_reviews_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'in_store_only_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'online_only_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'pickup_only_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'only_one_keyword_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'positive_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'negative_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'undefined_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'inla_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'indeterminate_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'missing_association_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'not_found_in_master_catalog_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'marketplace_owned_top_ten_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'marketplace_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'listed_secondary_value'::text))::integer, 0), COALESCE(((cds.additional ->> 'oos_marketplace_owned_value'::text))::integer, 0)
  • Hash Cond: (dp.product_id = cds.product_id)
  • Buffers: shared hit=121681 read=60241, temp read=13382 written=13396
24. 1,854.797 1,854.797 ↑ 19.5 7,507 1

CTE Scan on distinct_products dp (cost=0.00..2,921.72 rows=146,086 width=140) (actual time=1,788.768..1,854.797 rows=7,507 loops=1)

  • Output: dp.product_id, dp.site_id, dp.reseller_id, dp.url, dp.url_id, dp.upc, dp.title
  • Buffers: shared hit=120065 read=60178, temp read=13382 written=13396
25. 4.023 54.857 ↓ 6,044.5 12,089 1

Hash (cost=2.77..2.77 rows=2 width=149) (actual time=54.857..54.857 rows=12,089 loops=1)

  • Output: cds.additional, cds.product_id
  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2065kB
  • Buffers: shared hit=1616 read=63
26. 1.868 50.834 ↓ 6,044.5 12,089 1

Append (cost=0.00..2.77 rows=2 width=149) (actual time=0.055..50.834 rows=12,089 loops=1)

  • Buffers: shared hit=1616 read=63
27. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on public.cars_dashboard_summary cds (cost=0.00..0.00 rows=1 width=36) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: cds.additional, cds.product_id
  • Filter: ((cds.date = '2019-02-05'::date) AND ((cds.card_type)::text = 'secondary_images'::text))
28. 48.962 48.962 ↓ 12,089.0 12,089 1

Index Scan using cars_dashboard_summary_2019_1_date_card_type_value_inla_idx on public.cars_dashboard_summary_2019_1 cds_1 (cost=0.56..2.77 rows=1 width=262) (actual time=0.050..48.962 rows=12,089 loops=1)

  • Output: cds_1.additional, cds_1.product_id
  • Index Cond: ((cds_1.date = '2019-02-05'::date) AND ((cds_1.card_type)::text = 'secondary_images'::text))
  • Buffers: shared hit=1616 read=63
29.          

CTE products

30. 0.004 2,197.176 ↑ 1.0 10 1

Limit (cost=22.32..22.35 rows=10 width=376) (actual time=2,197.171..2,197.176 rows=10 loops=1)

  • Output: dps.product_id, dps.site_id, dps.reseller_id, dps.url, dps.url_id, dps.upc, dps.title, dps.details, dps.search_term_rankings, dps.shelf_page_rankings, dps.no_reviews_value, dps.in_store_only_value, dps.online_only_value, dps.pickup_only_value, dps.only_one_keyword_value, dps.positive_value, dps.negative_value, dps.undefined_value, dps.inla_value, dps.indeterminate_value, dps.missing_association_value, dps.not_found_in_master_catalog_value, dps.marketplace_owned_top_ten_value, dps.marketplace_value, dps.listed_secondary_value, dps.oos_marketplace_owned_value, (CASE WHEN (dps.listed_secondary_value = 1) THEN 'undefined'::text WHEN (dps.oos_marketplace_owned_value = 1) THEN 'undefined'::text WHEN (dps.no_reviews_value = 1) THEN 'undefined'::text WHEN (dps.in_store_only_value = 1) THEN 'undefined'::text WHEN (dps.online_only_value = 1) THEN 'undefined'::text WHEN (dps.pickup_only_value = 1) THEN 'undefined'::text WHEN (dps.only_one_keyword_value = 1) THEN 'undefined'::text WHEN (dps.marketplace_owned_top_ten_value = 1) THEN 'undefined'::text WHEN (dps.marketplace_value = 1) THEN 'undefined'::text WHEN (dps.inla_value = 1) THEN 'INLA'::text WHEN (dps.indeterminate_value = 1) THEN 'Indeterminate'::text WHEN (dps.missing_association_value = 1) THEN 'undefined'::text WHEN (dps.not_found_in_master_catalog_value = 1) THEN 'Not found in MC'::text WHEN ((dps.undefined_value = 1) AND (dps.missing_association_value = 0)) THEN 'Site Technical Issue'::text WHEN (dps.positive_value = 1) THEN 'Alternates found'::text ELSE 'Not enough alternates'::text END), (sum(dps.positive_value) OVER (?)), (sum(dps.negative_value) OVER (?)), (sum(dps.not_found_in_master_catalog_value) OVER (?)), (sum(dps.inla_value) OVER (?)), (sum(dps.undefined_value) OVER (?)), (CASE WHEN (dps.inla_value = 1) THEN 3 WHEN (dps.undefined_value = 1) THEN 4 WHEN (dps.positive_value = 1) THEN 2 ELSE 1 END)
  • Buffers: shared hit=121681 read=60241, temp read=13382 written=13396
31. 8.004 2,197.172 ↑ 20.0 10 1

Sort (cost=22.32..22.82 rows=200 width=376) (actual time=2,197.170..2,197.172 rows=10 loops=1)

  • Output: dps.product_id, dps.site_id, dps.reseller_id, dps.url, dps.url_id, dps.upc, dps.title, dps.details, dps.search_term_rankings, dps.shelf_page_rankings, dps.no_reviews_value, dps.in_store_only_value, dps.online_only_value, dps.pickup_only_value, dps.only_one_keyword_value, dps.positive_value, dps.negative_value, dps.undefined_value, dps.inla_value, dps.indeterminate_value, dps.missing_association_value, dps.not_found_in_master_catalog_value, dps.marketplace_owned_top_ten_value, dps.marketplace_value, dps.listed_secondary_value, dps.oos_marketplace_owned_value, (CASE WHEN (dps.listed_secondary_value = 1) THEN 'undefined'::text WHEN (dps.oos_marketplace_owned_value = 1) THEN 'undefined'::text WHEN (dps.no_reviews_value = 1) THEN 'undefined'::text WHEN (dps.in_store_only_value = 1) THEN 'undefined'::text WHEN (dps.online_only_value = 1) THEN 'undefined'::text WHEN (dps.pickup_only_value = 1) THEN 'undefined'::text WHEN (dps.only_one_keyword_value = 1) THEN 'undefined'::text WHEN (dps.marketplace_owned_top_ten_value = 1) THEN 'undefined'::text WHEN (dps.marketplace_value = 1) THEN 'undefined'::text WHEN (dps.inla_value = 1) THEN 'INLA'::text WHEN (dps.indeterminate_value = 1) THEN 'Indeterminate'::text WHEN (dps.missing_association_value = 1) THEN 'undefined'::text WHEN (dps.not_found_in_master_catalog_value = 1) THEN 'Not found in MC'::text WHEN ((dps.undefined_value = 1) AND (dps.missing_association_value = 0)) THEN 'Site Technical Issue'::text WHEN (dps.positive_value = 1) THEN 'Alternates found'::text ELSE 'Not enough alternates'::text END), (sum(dps.positive_value) OVER (?)), (sum(dps.negative_value) OVER (?)), (sum(dps.not_found_in_master_catalog_value) OVER (?)), (sum(dps.inla_value) OVER (?)), (sum(dps.undefined_value) OVER (?)), (CASE WHEN (dps.inla_value = 1) THEN 3 WHEN (dps.undefined_value = 1) THEN 4 WHEN (dps.positive_value = 1) THEN 2 ELSE 1 END)
  • Sort Key: (CASE WHEN (dps.inla_value = 1) THEN 3 WHEN (dps.undefined_value = 1) THEN 4 WHEN (dps.positive_value = 1) THEN 2 ELSE 1 END), (CASE WHEN (dps.listed_secondary_value = 1) THEN 'undefined'::text WHEN (dps.oos_marketplace_owned_value = 1) THEN 'undefined'::text WHEN (dps.no_reviews_value = 1) THEN 'undefined'::text WHEN (dps.in_store_only_value = 1) THEN 'undefined'::text WHEN (dps.online_only_value = 1) THEN 'undefined'::text WHEN (dps.pickup_only_value = 1) THEN 'undefined'::text WHEN (dps.only_one_keyword_value = 1) THEN 'undefined'::text WHEN (dps.marketplace_owned_top_ten_value = 1) THEN 'undefined'::text WHEN (dps.marketplace_value = 1) THEN 'undefined'::text WHEN (dps.inla_value = 1) THEN 'INLA'::text WHEN (dps.indeterminate_value = 1) THEN 'Indeterminate'::text WHEN (dps.missing_association_value = 1) THEN 'undefined'::text WHEN (dps.not_found_in_master_catalog_value = 1) THEN 'Not found in MC'::text WHEN ((dps.undefined_value = 1) AND (dps.missing_association_value = 0)) THEN 'Site Technical Issue'::text WHEN (dps.positive_value = 1) THEN 'Alternates found'::text ELSE 'Not enough alternates'::text END), dps.title
  • Sort Method: top-N heapsort Memory: 33kB
  • Buffers: shared hit=121681 read=60241, temp read=13382 written=13396
32. 13.544 2,189.168 ↓ 37.5 7,507 1

WindowAgg (cost=0.00..18.00 rows=200 width=376) (actual time=2,181.848..2,189.168 rows=7,507 loops=1)

  • Output: dps.product_id, dps.site_id, dps.reseller_id, dps.url, dps.url_id, dps.upc, dps.title, dps.details, dps.search_term_rankings, dps.shelf_page_rankings, dps.no_reviews_value, dps.in_store_only_value, dps.online_only_value, dps.pickup_only_value, dps.only_one_keyword_value, dps.positive_value, dps.negative_value, dps.undefined_value, dps.inla_value, dps.indeterminate_value, dps.missing_association_value, dps.not_found_in_master_catalog_value, dps.marketplace_owned_top_ten_value, dps.marketplace_value, dps.listed_secondary_value, dps.oos_marketplace_owned_value, CASE WHEN (dps.listed_secondary_value = 1) THEN 'undefined'::text WHEN (dps.oos_marketplace_owned_value = 1) THEN 'undefined'::text WHEN (dps.no_reviews_value = 1) THEN 'undefined'::text WHEN (dps.in_store_only_value = 1) THEN 'undefined'::text WHEN (dps.online_only_value = 1) THEN 'undefined'::text WHEN (dps.pickup_only_value = 1) THEN 'undefined'::text WHEN (dps.only_one_keyword_value = 1) THEN 'undefined'::text WHEN (dps.marketplace_owned_top_ten_value = 1) THEN 'undefined'::text WHEN (dps.marketplace_value = 1) THEN 'undefined'::text WHEN (dps.inla_value = 1) THEN 'INLA'::text WHEN (dps.indeterminate_value = 1) THEN 'Indeterminate'::text WHEN (dps.missing_association_value = 1) THEN 'undefined'::text WHEN (dps.not_found_in_master_catalog_value = 1) THEN 'Not found in MC'::text WHEN ((dps.undefined_value = 1) AND (dps.missing_association_value = 0)) THEN 'Site Technical Issue'::text WHEN (dps.positive_value = 1) THEN 'Alternates found'::text ELSE 'Not enough alternates'::text END, sum(dps.positive_value) OVER (?), sum(dps.negative_value) OVER (?), sum(dps.not_found_in_master_catalog_value) OVER (?), sum(dps.inla_value) OVER (?), sum(dps.undefined_value) OVER (?), CASE WHEN (dps.inla_value = 1) THEN 3 WHEN (dps.undefined_value = 1) THEN 4 WHEN (dps.positive_value = 1) THEN 2 ELSE 1 END
  • Buffers: shared hit=121681 read=60241, temp read=13382 written=13396
33. 2,175.624 2,175.624 ↓ 37.5 7,507 1

CTE Scan on distinct_products_summary dps (cost=0.00..4.00 rows=200 width=300) (actual time=2,170.086..2,175.624 rows=7,507 loops=1)

  • Output: dps.product_id, dps.site_id, dps.reseller_id, dps.url, dps.url_id, dps.upc, dps.title, dps.details, dps.search_term_rankings, dps.shelf_page_rankings, dps.no_reviews_value, dps.in_store_only_value, dps.online_only_value, dps.pickup_only_value, dps.only_one_keyword_value, dps.positive_value, dps.negative_value, dps.undefined_value, dps.inla_value, dps.indeterminate_value, dps.missing_association_value, dps.not_found_in_master_catalog_value, dps.marketplace_owned_top_ten_value, dps.marketplace_value, dps.listed_secondary_value, dps.oos_marketplace_owned_value
  • Buffers: shared hit=121681 read=60241, temp read=13382 written=13396
34.          

CTE mcp_products

35. 0.004 1.096 ↓ 1.7 10 1

Unique (cost=380.12..380.15 rows=6 width=44) (actual time=1.092..1.096 rows=10 loops=1)

  • Output: p_2.product_id, mcp.id, (COALESCE(mcc.name, c.name)), mcpl.customer
  • Buffers: shared hit=239 read=31
36. 0.013 1.092 ↓ 1.7 10 1

Sort (cost=380.12..380.14 rows=6 width=44) (actual time=1.091..1.092 rows=10 loops=1)

  • Output: p_2.product_id, mcp.id, (COALESCE(mcc.name, c.name)), mcpl.customer
  • Sort Key: p_2.product_id, mcp.id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=239 read=31
37. 0.015 1.079 ↓ 1.7 10 1

Nested Loop Left Join (cost=1.86..380.05 rows=6 width=44) (actual time=0.194..1.079 rows=10 loops=1)

  • Output: p_2.product_id, mcp.id, COALESCE(mcc.name, c.name), mcpl.customer
  • Inner Unique: true
  • Buffers: shared hit=239 read=31
38. 0.010 1.044 ↓ 1.7 10 1

Nested Loop Left Join (cost=1.71..378.74 rows=6 width=48) (actual time=0.181..1.044 rows=10 loops=1)

  • Output: p_2.product_id, mcp.id, mcpl.customer, mcc.name, mcc.ch_customer
  • Inner Unique: true
  • Buffers: shared hit=219 read=31
39. 0.009 1.014 ↓ 1.7 10 1

Nested Loop Left Join (cost=1.57..377.57 rows=6 width=12) (actual time=0.172..1.014 rows=10 loops=1)

  • Output: p_2.product_id, mcp.id, mcpl.customer
  • Inner Unique: true
  • Buffers: shared hit=199 read=31
40. 0.010 0.985 ↓ 1.7 10 1

Nested Loop (cost=1.42..376.61 rows=6 width=12) (actual time=0.163..0.985 rows=10 loops=1)

  • Output: p_2.product_id, mcp.id, mcpls.product_list_id
  • Buffers: shared hit=179 read=31
41. 0.007 0.875 ↓ 3.3 10 1

Nested Loop (cost=1.13..375.53 rows=3 width=8) (actual time=0.128..0.875 rows=10 loops=1)

  • Output: p_2.product_id, mcp.id
  • Inner Unique: true
  • Buffers: shared hit=149 read=31
42. 0.032 0.712 ↓ 1.5 12 1

Nested Loop (cost=0.85..372.63 rows=8 width=12) (actual time=0.072..0.712 rows=12 loops=1)

  • Output: p_2.product_id, idpn.imported_data_id
  • Buffers: shared hit=119 read=24
43. 0.014 0.130 ↑ 10.2 25 1

Nested Loop (cost=0.43..257.73 rows=255 width=195) (actual time=0.018..0.130 rows=25 loops=1)

  • Output: p_2.product_id, pu_1.url
  • Buffers: shared hit=55
44. 0.016 0.016 ↑ 1.0 10 1

CTE Scan on products p_2 (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.016 rows=10 loops=1)

  • Output: p_2.product_id, p_2.site_id, p_2.reseller_id, p_2.url, p_2.url_id, p_2.upc, p_2.title, p_2.details, p_2.search_term_rankings, p_2.shelf_page_rankings, p_2.no_reviews_value, p_2.in_store_only_value, p_2.online_only_value, p_2.pickup_only_value, p_2.only_one_keyword_value, p_2.positive_value, p_2.negative_value, p_2.undefined_value, p_2.inla_value, p_2.indeterminate_value, p_2.missing_association_value, p_2.not_found_in_master_catalog_value, p_2.marketplace_owned_top_ten_value, p_2.marketplace_value, p_2.listed_secondary_value, p_2.oos_marketplace_owned_value, p_2.value, p_2.positive, p_2.negative, p_2.not_found_in_master_catalog, p_2.inla, p_2.undefined
45. 0.100 0.100 ↑ 12.5 2 10

Index Scan using product_url_product_id on public.product_url pu_1 (cost=0.43..25.50 rows=25 width=195) (actual time=0.007..0.010 rows=2 loops=10)

  • Output: pu_1.id, pu_1.url, pu_1.product_id, pu_1.image_url, pu_1.title, pu_1.description, pu_1.model, pu_1.upc, pu_1.sku, pu_1.updated_at, pu_1.customer_uploaded_upc
  • Index Cond: (pu_1.product_id = p_2.product_id)
  • Buffers: shared hit=55
46. 0.550 0.550 ↓ 0.0 0 25

Index Scan using imported_data_parsed_new_url on public.imported_data_parsed_new idpn (cost=0.42..0.44 rows=1 width=66) (actual time=0.022..0.022 rows=0 loops=25)

  • Output: idpn.imported_data_id, idpn.url, idpn.model, idpn.revision, idpn.confidence, idpn.hash, idpn.manufacturer_images, idpn.manufacturer_videos, idpn.priceold, idpn.date, idpn.description, idpn.long_description, idpn.parsed_attributes, idpn.product_name, idpn.features, idpn.parsed_meta, idpn.htags, idpn.manufacturer_url, idpn.anchors, idpn.general_info, idpn.market_seller, idpn.product_url_id, idpn.selling_style, idpn.pc_nine, idpn.upc, idpn.tool_id, idpn.shelf_description, idpn.change_date, idpn.selected_variant, idpn.asin, idpn.web_id, idpn.item_num
  • Index Cond: ((idpn.url)::text = (pu_1.url)::text)
  • Buffers: shared hit=64 read=24
47. 0.156 0.156 ↑ 1.0 1 12

Index Scan using mc_product_imported_data_id_index on public.ch_master_catalog_products mcp (cost=0.29..0.36 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=12)

  • Output: mcp.id, mcp.imported_data_id
  • Index Cond: (mcp.imported_data_id = idpn.imported_data_id)
  • Buffers: shared hit=30 read=7
48. 0.100 0.100 ↑ 2.0 1 10

Index Only Scan using ch_master_catalog_products_lists_pkey on public.ch_master_catalog_products_lists mcpls (cost=0.29..0.34 rows=2 width=8) (actual time=0.010..0.010 rows=1 loops=10)

  • Output: mcpls.mc_product_id, mcpls.product_list_id
  • Index Cond: (mcpls.mc_product_id = mcp.id)
  • Heap Fetches: 10
  • Buffers: shared hit=30
49. 0.020 0.020 ↑ 1.0 1 10

Index Scan using ch_master_catalog_product_list_pkey on public.ch_master_catalog_product_list mcpl (cost=0.14..0.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=10)

  • Output: mcpl.id, mcpl.ch_product_list_id, mcpl.customer, mcpl.type, mcpl.name, mcpl.user_id
  • Index Cond: (mcpl.id = mcpls.product_list_id)
  • Buffers: shared hit=20
50. 0.020 0.020 ↑ 1.0 1 10

Index Scan using ch_master_catalog_customer_pkey on public.ch_master_catalog_customer mcc (cost=0.14..0.20 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=10)

  • Output: mcc.id, mcc.ch_customer, mcc.name
  • Index Cond: (mcc.id = mcpl.customer)
  • Buffers: shared hit=20
51. 0.020 0.020 ↑ 1.0 1 10

Index Scan using customers_id_pkey on public.customers c (cost=0.15..0.22 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=10)

  • Output: c.id, c.name, c.url, c.description, c.image_url, c.customer_type
  • Index Cond: (c.id = mcc.ch_customer)
  • Buffers: shared hit=20
52. 0.063 2,198.420 ↑ 1.0 10 1

Hash Left Join (cost=0.34..12.25 rows=10 width=317) (actual time=2,198.370..2,198.420 rows=10 loops=1)

  • Output: p.product_id, p.title, p.upc, p.reseller_id, p.url, p.value, p.positive_value, p.details, (upper("substring"((s.name)::text, 1, 1)) || "substring"((s.name)::text, 2, length((s.name)::text))), s.high_ratings_disabled, p.positive, p.negative, p.not_found_in_master_catalog, p.inla, p.undefined, mp.mc_product_id, mp.mc_customer_id, mp.customer_name, CASE WHEN (p.inla_value = 1) THEN 3 WHEN (p.undefined_value = 1) THEN 4 WHEN (p.positive_value = 1) THEN 2 ELSE 1 END
  • Hash Cond: (p.product_id = mp.product_id)
  • Buffers: shared hit=121940 read=60272, temp read=13382 written=13396
53. 0.019 2,197.251 ↑ 1.0 10 1

Nested Loop (cost=0.15..11.76 rows=10 width=263) (actual time=2,197.216..2,197.251 rows=10 loops=1)

  • Output: p.product_id, p.title, p.upc, p.reseller_id, p.url, p.value, p.positive_value, p.details, p.positive, p.negative, p.not_found_in_master_catalog, p.inla, p.undefined, p.inla_value, p.undefined_value, s.name, s.high_ratings_disabled
  • Inner Unique: true
  • Buffers: shared hit=121701 read=60241, temp read=13382 written=13396
54. 2,197.182 2,197.182 ↑ 1.0 10 1

CTE Scan on products p (cost=0.00..0.20 rows=10 width=252) (actual time=2,197.175..2,197.182 rows=10 loops=1)

  • Output: p.product_id, p.site_id, p.reseller_id, p.url, p.url_id, p.upc, p.title, p.details, p.search_term_rankings, p.shelf_page_rankings, p.no_reviews_value, p.in_store_only_value, p.online_only_value, p.pickup_only_value, p.only_one_keyword_value, p.positive_value, p.negative_value, p.undefined_value, p.inla_value, p.indeterminate_value, p.missing_association_value, p.not_found_in_master_catalog_value, p.marketplace_owned_top_ten_value, p.marketplace_value, p.listed_secondary_value, p.oos_marketplace_owned_value, p.value, p.positive, p.negative, p.not_found_in_master_catalog, p.inla, p.undefined
  • Buffers: shared hit=121681 read=60241, temp read=13382 written=13396
55. 0.050 0.050 ↑ 1.0 1 10

Index Scan using sites_id_pkey on public.sites s (cost=0.15..1.16 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=10)

  • Output: s.id, s.name, s.url, s.image_url, s.site_type, s.results_per_page, s.zip_code, s.traffic_upload, s.crawler_name, s.location, s.user_agent, s.results_per_page_original, s.search_path, s.crawler_match_url, s.high_ratings_disabled
  • Index Cond: (s.id = p.site_id)
  • Buffers: shared hit=20
56. 0.005 1.106 ↓ 1.7 10 1

Hash (cost=0.12..0.12 rows=6 width=44) (actual time=1.106..1.106 rows=10 loops=1)

  • Output: mp.mc_product_id, mp.mc_customer_id, mp.customer_name, mp.product_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=239 read=31
57. 1.101 1.101 ↓ 1.7 10 1

CTE Scan on mcp_products mp (cost=0.00..0.12 rows=6 width=44) (actual time=1.093..1.101 rows=10 loops=1)

  • Output: mp.mc_product_id, mp.mc_customer_id, mp.customer_name, mp.product_id
  • Buffers: shared hit=239 read=31