explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AScZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 150,455.514 ↑ 1.0 25 1

Limit (cost=154,509,709,804.64..154,509,709,804.70 rows=25 width=657) (actual time=150,455.508..150,455.514 rows=25 loops=1)

  • Output: cp.id, cp.name, cp.""imageUrl"", cp.""productUrl"", cp.country, cp.asin, cp.brand, cp.share, ((COALESCE((((((COALESCE(cp.share, '0'::double precision) - COALESCE(pp.share, '0'::double precision)))::numeric)::double precision / COALESCE(NULLIF(pp.share, '0'::double precision), NULLIF(cp.share, '0'::double precision))) * '100'::double precision), '0'::double precision))::numeric), ((cp.revenue / '100'::double precision)), ((COALESCE((((((COALESCE(cp.revenue, '0'::double precision) - COALESCE(pp.revenue, '0'::double precision)))::numeric)::double precision / COALESCE(NULLIF(pp.revenue, '0'::double precision), NULLIF(cp.revenue, '0'::double precision))) * '100'::double precision), '0'::double precision))::numeric), ((cp.revenue1p / '100'::double precision)), ((COALESCE((((((COALESCE(cp.revenue1p, '0'::double precision) - COALESCE(pp.revenue1p, '0'::double precision)))::numeric)::double precision / COALESCE(NULLIF(pp.revenue1p, '0'::double precision), NULLIF(cp.revenue1p, '0'::double precision))) * '100'::double precision), '0'::double precision))::numeric), ((cp.revenue3p / '100'::double precision)), ((COALESCE((((((COALESCE(cp.revenue3p, '0'::double precision) - COALESCE(pp.revenue3p, '0'::double precision)))::numeric)::double precision / COALESCE(NULLIF(pp.revenue3p, '0'::double precision), NULLIF(cp.revenue3p, '0'::double precision))) * '100'::double precision), '0'::double precision))::numeric), cp.unitsales, (COALESCE((((COALESCE(cp.unitsales, '0'::numeric) - COALESCE(pp.ppunitsales, '0'::numeric)) / COALESCE(NULLIF(pp.ppunitsales, '0'::numeric), NULLIF(cp.unitsales, '0'::numeric))) * '100'::numeric), '0'::numeric)), cp.unitsales1p, (COALESCE((((COALESCE(cp.unitsales1p, '0'::numeric) - COALESCE(pp.ppunitsales1p, '0'::numeric)) / COALESCE(NULLIF(pp.ppunitsales1p, '0'::numeric), NULLIF(cp.unitsales1p, '0'::numeric))) * '100'::numeric), '0'::numeric)), cp.unitsales3p, (COALESCE((((COALESCE(cp.unitsales3p, '0'::numeric) - COALESCE(pp.ppunitsales3p, '0'::numeric)) / COALESCE(NULLIF(pp.ppunitsales3p, '0'::numeric), NULLIF(cp.unitsales3p, '0'::numeric))) * '100'::numeric), '0'::numeric)), (count(*) OVER (?)), cp.issharedbsr, cp.sharedbsrparentasin, cp.splitrevenuedate, cp.sharedbsrvariantchildcount
  • Buffers: shared hit=3,785,222 read=170,347
2.          

CTE products

3. 1.317 1.678 ↑ 1.0 7,304 1

Bitmap Heap Scan on public.segment_product (cost=1,090.60..24,938.27 rows=7,506 width=745) (actual time=0.396..1.678 rows=7,304 loops=1)

  • Output: segment_product.segment_id, segment_product.id, segment_product.asin, segment_product.country, segment_product.brand, segment_product.name, segment_product.product_url, segment_product.imageurl, segment_product.scraped_parent_asin, segment_product.is_shared_bsr
  • Recheck Cond: ((segment_product.segment_id)::text = '266'::text)
  • Heap Blocks: exact=274
  • Buffers: shared hit=304
4. 0.361 0.361 ↑ 1.0 7,304 1

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..1,088.72 rows=7,506 width=0) (actual time=0.361..0.361 rows=7,304 loops=1)

  • Index Cond: ((segment_product.segment_id)::text = '266'::text)
  • Buffers: shared hit=30
5.          

CTE current_keepa_products

6. 311.184 145,038.994 ↑ 498.1 1,314,652 1

Nested Loop (cost=169.59..10,059,660.90 rows=654,777,126 width=81) (actual time=6.687..145,038.994 rows=1,314,652 loops=1)

  • Output: segment_asin_buybox_keepa_new.id, segment_asin_buybox_keepa_new.asin, segment_asin_buybox_keepa_new.recordeddate, segment_asin_buybox_keepa_new.country, segment_asin_buybox_keepa_new.salesrank, segment_asin_buybox_keepa_new.category, segment_asin_buybox_keepa_new.unitssold, segment_asin_buybox_keepa_new.reviews, segment_asin_buybox_keepa_new.is1p, segment_asin_buybox_keepa_new.is3p, segment_asin_buybox_keepa_new.price, segment_asin_buybox_keepa_new.iskeepa
  • Buffers: shared hit=3,785,181 read=170,347
7. 9.555 13.658 ↓ 36.5 7,304 1

HashAggregate (cost=168.88..170.88 rows=200 width=516) (actual time=6.642..13.658 rows=7,304 loops=1)

  • Output: products.id
  • Group Key: (products.id)::text
  • Buffers: shared hit=273
8. 4.103 4.103 ↑ 1.0 7,304 1

CTE Scan on products (cost=0.00..150.12 rows=7,506 width=516) (actual time=0.001..4.103 rows=7,304 loops=1)

  • Output: products.id, products.id
  • Buffers: shared hit=273
9. 144,714.152 144,714.152 ↑ 68.8 180 7,304

Index Scan using segment_asin_buybox_keepa_new_pkey on public.segment_asin_buybox_keepa_new (cost=0.71..50,173.68 rows=12,377 width=81) (actual time=1.935..19.813 rows=180 loops=7,304)

  • Output: segment_asin_buybox_keepa_new.id, segment_asin_buybox_keepa_new.asin, segment_asin_buybox_keepa_new.recordeddate, segment_asin_buybox_keepa_new.country, segment_asin_buybox_keepa_new.salesrank, segment_asin_buybox_keepa_new.category, segment_asin_buybox_keepa_new.unitssold, segment_asin_buybox_keepa_new.reviews, segment_asin_buybox_keepa_new.is1p, segment_asin_buybox_keepa_new.is3p, segment_asin_buybox_keepa_new.price, segment_asin_buybox_keepa_new.iskeepa
  • Index Cond: (((segment_asin_buybox_keepa_new.id)::text = (products.id)::text) AND (segment_asin_buybox_keepa_new.recordeddate >= '1580947200000'::bigint) AND (segment_asin_buybox_keepa_new.recordeddate < '1596585599999'::bigint))
  • Buffers: shared hit=3,784,908 read=170,347
10.          

CTE previous_keepa_products

11. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=169.59..24,015,607.11 rows=1,571,841,424 width=81) (never executed)

  • Output: segment_asin_buybox_keepa_new_1.id, segment_asin_buybox_keepa_new_1.asin, segment_asin_buybox_keepa_new_1.recordeddate, segment_asin_buybox_keepa_new_1.country, segment_asin_buybox_keepa_new_1.salesrank, segment_asin_buybox_keepa_new_1.category, segment_asin_buybox_keepa_new_1.unitssold, segment_asin_buybox_keepa_new_1.reviews, segment_asin_buybox_keepa_new_1.is1p, segment_asin_buybox_keepa_new_1.is3p, segment_asin_buybox_keepa_new_1.price, segment_asin_buybox_keepa_new_1.iskeepa
12. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=168.88..170.88 rows=200 width=516) (never executed)

  • Output: products_1.id
  • Group Key: (products_1.id)::text
13. 0.000 0.000 ↓ 0.0 0

CTE Scan on products products_1 (cost=0.00..150.12 rows=7,506 width=516) (never executed)

  • Output: products_1.id, products_1.id
14. 0.000 0.000 ↓ 0.0 0

Index Scan using segment_asin_buybox_keepa_new_pkey on public.segment_asin_buybox_keepa_new segment_asin_buybox_keepa_new_1 (cost=0.71..119,780.07 rows=29,711 width=81) (never executed)

  • Output: segment_asin_buybox_keepa_new_1.id, segment_asin_buybox_keepa_new_1.asin, segment_asin_buybox_keepa_new_1.recordeddate, segment_asin_buybox_keepa_new_1.country, segment_asin_buybox_keepa_new_1.salesrank, segment_asin_buybox_keepa_new_1.category, segment_asin_buybox_keepa_new_1.unitssold, segment_asin_buybox_keepa_new_1.reviews, segment_asin_buybox_keepa_new_1.is1p, segment_asin_buybox_keepa_new_1.is3p, segment_asin_buybox_keepa_new_1.price, segment_asin_buybox_keepa_new_1.iskeepa
  • Index Cond: (((segment_asin_buybox_keepa_new_1.id)::text = (products_1.id)::text) AND (segment_asin_buybox_keepa_new_1.recordeddate >= '1565308800001'::bigint) AND (segment_asin_buybox_keepa_new_1.recordeddate < '1580947200000'::bigint))
15.          

CTE current_total_revenue

16. 0.442 147,381.910 ↑ 1.0 1 1

Aggregate (cost=7,976,837,737.54..7,976,837,737.55 rows=1 width=40) (actual time=147,381.910..147,381.910 rows=1 loops=1)

  • Output: max((p.segment_id)::text), sum((sum(((COALESCE(kp.unitssold, '0'::bigint))::double precision * COALESCE(kp.price, '0'::double precision)))))
  • Buffers: shared hit=3,785,188 read=170,347
17. 0.306 147,381.468 ↓ 2.6 1,986 1

Append (cost=1,264,235,203.11..7,976,837,726.26 rows=752 width=72) (actual time=146,779.586..147,381.468 rows=1,986 loops=1)

  • Buffers: shared hit=3,785,188 read=170,347
18. 398.574 146,780.127 ↓ 2.6 1,985 1

HashAggregate (cost=1,264,235,203.11..1,264,235,210.62 rows=751 width=1,040) (actual time=146,779.585..146,780.127 rows=1,985 loops=1)

  • Output: p.segment_id, p.brand, sum(((COALESCE(kp.unitssold, '0'::bigint))::double precision * COALESCE(kp.price, '0'::double precision)))
  • Group Key: p.segment_id, p.brand
  • Buffers: shared hit=3,785,181 read=170,347
19. 543.642 146,381.553 ↑ 13,515.1 1,212,208 1

Hash Join (cost=243.94..1,059,446,003.25 rows=16,383,135,989 width=1,048) (actual time=9.929..146,381.553 rows=1,212,208 loops=1)

  • Output: p.segment_id, p.brand, kp.unitssold, kp.price
  • Hash Cond: ((kp.id)::text = (p.id)::text)
  • Join Filter: ((NOT p.is_shared_bsr) OR (p.is_shared_bsr IS NULL) OR (p.scraped_parent_asin IS NULL) OR (((kp.category)::text <> ALL ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) AND (kp.recordeddate < '1590019200000'::bigint)))
  • Rows Removed by Join Filter: 102,444
  • Buffers: shared hit=3,785,181 read=170,347
20. 145,834.706 145,834.706 ↑ 498.1 1,314,652 1

CTE Scan on current_keepa_products kp (cost=0.00..13,095,542.52 rows=654,777,126 width=1,056) (actual time=6.688..145,834.706 rows=1,314,652 loops=1)

  • Output: kp.id, kp.asin, kp.recordeddate, kp.country, kp.salesrank, kp.category, kp.unitssold, kp.reviews, kp.is1p, kp.is3p, kp.price, kp.iskeepa
  • Buffers: shared hit=3,785,181 read=170,347
21. 1.683 3.205 ↑ 1.0 7,304 1

Hash (cost=150.12..150.12 rows=7,506 width=2,065) (actual time=3.205..3.205 rows=7,304 loops=1)

  • Output: p.segment_id, p.brand, p.id, p.is_shared_bsr, p.scraped_parent_asin
  • Buckets: 8,192 Batches: 1 Memory Usage: 575kB
22. 1.522 1.522 ↑ 1.0 7,304 1

CTE Scan on products p (cost=0.00..150.12 rows=7,506 width=2,065) (actual time=0.001..1.522 rows=7,304 loops=1)

  • Output: p.segment_id, p.brand, p.id, p.is_shared_bsr, p.scraped_parent_asin
23. 0.001 601.035 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=6,712,602,508.11..6,712,602,508.13 rows=1 width=72) (actual time=601.035..601.035 rows=1 loops=1)

  • Output: ""*SELECT* 2"".max, ""*SELECT* 2"".max_1, ""*SELECT* 2"".revenue
  • Buffers: shared hit=7
24. 7.345 601.034 ↑ 1.0 1 1

Aggregate (cost=6,712,602,508.11..6,712,602,508.12 rows=1 width=72) (actual time=601.034..601.034 rows=1 loops=1)

  • Output: max((p_1.segment_id)::text), max((p_1.brand)::text), sum(((COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_1.unitssold DESC)), '0'::bigint))::double precision * COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_1.price DESC)), '0'::double precision)))
  • Buffers: shared hit=7
25. 71.104 593.689 ↑ 4.5 33,225 1

GroupAggregate (cost=6,638,433,770.08..6,712,599,133.11 rows=150,000 width=1,572) (actual time=517.154..593.689 rows=33,225 loops=1)

  • Output: p_1.segment_id, p_1.brand, percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_1.unitssold DESC), percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_1.price DESC), p_1.scraped_parent_asin, kp_1.recordeddate
  • Group Key: p_1.segment_id, p_1.brand, p_1.scraped_parent_asin, kp_1.recordeddate
  • Buffers: shared hit=7
26. 229.373 522.585 ↑ 41,367.9 102,444 1

Sort (cost=6,638,433,770.08..6,649,028,500.52 rows=4,237,892,173 width=1,572) (actual time=517.091..522.585 rows=102,444 loops=1)

  • Output: p_1.segment_id, p_1.brand, p_1.scraped_parent_asin, kp_1.recordeddate, kp_1.unitssold, kp_1.price
  • Sort Key: p_1.segment_id, p_1.brand, p_1.scraped_parent_asin, kp_1.recordeddate
  • Sort Method: quicksort Memory: 14,085kB
  • Buffers: shared hit=3
27. 64.862 293.212 ↑ 41,367.9 102,444 1

Hash Join (cost=196.80..166,786,772.61 rows=4,237,892,173 width=1,572) (actual time=1.705..293.212 rows=102,444 loops=1)

  • Output: p_1.segment_id, p_1.brand, p_1.scraped_parent_asin, kp_1.recordeddate, kp_1.unitssold, kp_1.price
  • Hash Cond: ((kp_1.id)::text = (p_1.id)::text)
28. 226.792 226.792 ↑ 414.4 547,732 1

CTE Scan on current_keepa_products kp_1 (cost=0.00..18,006,370.97 rows=226,989,404 width=540) (actual time=0.021..226.792 rows=547,732 loops=1)

  • Output: kp_1.id, kp_1.asin, kp_1.recordeddate, kp_1.country, kp_1.salesrank, kp_1.category, kp_1.unitssold, kp_1.reviews, kp_1.is1p, kp_1.is3p, kp_1.price, kp_1.iskeepa
  • Filter: (((kp_1.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) OR (kp_1.recordeddate >= '1590019200000'::bigint))
  • Rows Removed by Filter: 766,920
29. 0.282 1.558 ↑ 2.7 1,366 1

Hash (cost=150.12..150.12 rows=3,734 width=2,064) (actual time=1.558..1.558 rows=1,366 loops=1)

  • Output: p_1.segment_id, p_1.brand, p_1.scraped_parent_asin, p_1.id
  • Buckets: 4,096 Batches: 1 Memory Usage: 127kB
30. 1.276 1.276 ↑ 2.7 1,366 1

CTE Scan on products p_1 (cost=0.00..150.12 rows=3,734 width=2,064) (actual time=0.004..1.276 rows=1,366 loops=1)

  • Output: p_1.segment_id, p_1.brand, p_1.scraped_parent_asin, p_1.id
  • Filter: (p_1.is_shared_bsr AND (p_1.scraped_parent_asin IS NOT NULL))
  • Rows Removed by Filter: 5,938
31.          

CTE previous_total_revenue

32. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=19,213,250,742.61..19,213,250,742.62 rows=1 width=40) (never executed)

  • Output: max((p_2.segment_id)::text), sum((sum(((COALESCE(kp_2.unitssold, '0'::bigint))::double precision * COALESCE(kp_2.price, '0'::double precision)))))
33. 0.000 0.000 ↓ 0.0 0

Append (cost=3,034,890,742.68..19,213,250,731.33 rows=752 width=72) (never executed)

34. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=3,034,890,742.68..3,034,890,750.19 rows=751 width=1,040) (never executed)

  • Output: p_2.segment_id, p_2.brand, sum(((COALESCE(kp_2.unitssold, '0'::bigint))::double precision * COALESCE(kp_2.price, '0'::double precision)))
  • Group Key: p_2.segment_id, p_2.brand
35. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=243.94..2,543,278,964.02 rows=39,328,942,293 width=1,048) (never executed)

  • Output: p_2.segment_id, p_2.brand, kp_2.unitssold, kp_2.price
  • Hash Cond: ((kp_2.id)::text = (p_2.id)::text)
  • Join Filter: ((NOT p_2.is_shared_bsr) OR (p_2.is_shared_bsr IS NULL) OR (p_2.scraped_parent_asin IS NULL) OR (((kp_2.category)::text <> ALL ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) AND (kp_2.recordeddate < '1590019200000'::bigint)))
36. 0.000 0.000 ↓ 0.0 0

CTE Scan on previous_keepa_products kp_2 (cost=0.00..31,436,828.48 rows=1,571,841,424 width=1,056) (never executed)

  • Output: kp_2.id, kp_2.asin, kp_2.recordeddate, kp_2.country, kp_2.salesrank, kp_2.category, kp_2.unitssold, kp_2.reviews, kp_2.is1p, kp_2.is3p, kp_2.price, kp_2.iskeepa
37. 0.000 0.000 ↓ 0.0 0

Hash (cost=150.12..150.12 rows=7,506 width=2,065) (never executed)

  • Output: p_2.segment_id, p_2.brand, p_2.id, p_2.is_shared_bsr, p_2.scraped_parent_asin
38. 0.000 0.000 ↓ 0.0 0

CTE Scan on products p_2 (cost=0.00..150.12 rows=7,506 width=2,065) (never executed)

  • Output: p_2.segment_id, p_2.brand, p_2.id, p_2.is_shared_bsr, p_2.scraped_parent_asin
39. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2_1 (cost=16,178,359,973.61..16,178,359,973.63 rows=1 width=72) (never executed)

  • Output: ""*SELECT* 2_1"".max, ""*SELECT* 2_1"".max_1, ""*SELECT* 2_1"".revenue
40. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=16,178,359,973.61..16,178,359,973.62 rows=1 width=72) (never executed)

  • Output: max((p_3.segment_id)::text), max((p_3.brand)::text), sum(((COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_3.unitssold DESC)), '0'::bigint))::double precision * COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_3.price DESC)), '0'::double precision)))
41. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=16,000,320,253.66..16,178,356,598.61 rows=150,000 width=1,572) (never executed)

  • Output: p_3.segment_id, p_3.brand, percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_3.unitssold DESC), percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_3.price DESC), p_3.scraped_parent_asin, kp_3.recordeddate
  • Group Key: p_3.segment_id, p_3.brand, p_3.scraped_parent_asin, kp_3.recordeddate
42. 0.000 0.000 ↓ 0.0 0

Sort (cost=16,000,320,253.66..16,025,753,695.80 rows=10,173,376,854 width=1,572) (never executed)

  • Output: p_3.segment_id, p_3.brand, p_3.scraped_parent_asin, kp_3.recordeddate, kp_3.unitssold, kp_3.price
  • Sort Key: p_3.segment_id, p_3.brand, p_3.scraped_parent_asin, kp_3.recordeddate
43. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=196.80..400,383,835.90 rows=10,173,376,854 width=1,572) (never executed)

  • Output: p_3.segment_id, p_3.brand, p_3.scraped_parent_asin, kp_3.recordeddate, kp_3.unitssold, kp_3.price
  • Hash Cond: ((kp_3.id)::text = (p_3.id)::text)
44. 0.000 0.000 ↓ 0.0 0

CTE Scan on previous_keepa_products kp_3 (cost=0.00..43,225,639.16 rows=544,905,027 width=540) (never executed)

  • Output: kp_3.id, kp_3.asin, kp_3.recordeddate, kp_3.country, kp_3.salesrank, kp_3.category, kp_3.unitssold, kp_3.reviews, kp_3.is1p, kp_3.is3p, kp_3.price, kp_3.iskeepa
  • Filter: (((kp_3.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) OR (kp_3.recordeddate >= '1590019200000'::bigint))
45. 0.000 0.000 ↓ 0.0 0

Hash (cost=150.12..150.12 rows=3,734 width=2,064) (never executed)

  • Output: p_3.segment_id, p_3.brand, p_3.scraped_parent_asin, p_3.id
46. 0.000 0.000 ↓ 0.0 0

CTE Scan on products p_3 (cost=0.00..150.12 rows=3,734 width=2,064) (never executed)

  • Output: p_3.segment_id, p_3.brand, p_3.scraped_parent_asin, p_3.id
  • Filter: (p_3.is_shared_bsr AND (p_3.scraped_parent_asin IS NOT NULL))
47.          

CTE previous_period

48. 0.000 0.000 ↓ 0.0 0

Append (cost=2,865,074,167.54..89,865,440,296.71 rows=950 width=481) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1 (cost=2,865,074,167.54..2,865,074,193.79 rows=750 width=4,353) (never executed)

  • Output: ""*SELECT* 1"".segment_id, ""*SELECT* 1"".id, ""*SELECT* 1"".asin, ""*SELECT* 1"".""imageUrl"", ""*SELECT* 1"".""productUrl"", ""*SELECT* 1"".brand, ""*SELECT* 1"".name, ""*SELECT* 1"".country, ""*SELECT* 1"".revenue, ""*SELECT* 1"".ppunitsales, ""*SELECT* 1"".share, ""*SELECT* 1"".revenue1p, ""*SELECT* 1"".revenue3p, ""*SELECT* 1"".ppunitsales1p, ""*SELECT* 1"".ppunitsales3p, false, NULL::text, NULL::text, NULL::text
50. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=2,865,074,167.54..2,865,074,186.29 rows=750 width=4,361) (never executed)

  • Output: p_4.segment_id, p_4.id, p_4.asin, p_4.imageurl, p_4.product_url, p_4.brand, p_4.name, p_4.country, sum(((COALESCE(kp_4.unitssold, '0'::bigint))::double precision * COALESCE(kp_4.price, '0'::double precision))), sum(COALESCE(kp_4.unitssold, '0'::bigint)), ((sum(((COALESCE(kp_4.unitssold, '0'::bigint))::double precision * COALESCE(kp_4.price, '0'::double precision))) * '100'::double precision) / NULLIF(ptr.revenue, '0'::double precision)), sum(CASE WHEN kp_4.is1p THEN ((COALESCE(kp_4.unitssold, '0'::bigint))::double precision * COALESCE(kp_4.price, '0'::double precision)) ELSE '0'::double precision END), sum(CASE WHEN kp_4.is3p THEN ((COALESCE(kp_4.unitssold, '0'::bigint))::double precision * COALESCE(kp_4.price, '0'::double precision)) ELSE '0'::double precision END), sum(CASE WHEN kp_4.is1p THEN COALESCE(kp_4.unitssold, '0'::bigint) ELSE '0'::bigint END), sum(CASE WHEN kp_4.is3p THEN COALESCE(kp_4.unitssold, '0'::bigint) ELSE '0'::bigint END), false, NULL::text, NULL::text, NULL::text, ptr.revenue
  • Group Key: p_4.segment_id, p_4.id, p_4.asin, p_4.imageurl, p_4.product_url, p_4.brand, p_4.country, ptr.revenue, p_4.name
51. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=211.93..1,077,890,468.44 rows=29,786,394,985 width=4,154) (never executed)

  • Output: p_4.segment_id, p_4.id, p_4.asin, p_4.imageurl, p_4.product_url, p_4.brand, p_4.name, p_4.country, ptr.revenue, kp_4.unitssold, kp_4.price, kp_4.is1p, kp_4.is3p
  • Hash Cond: ((kp_4.id)::text = (p_4.id)::text)
52. 0.000 0.000 ↓ 0.0 0

CTE Scan on previous_keepa_products kp_4 (cost=0.00..31,436,828.48 rows=1,571,841,424 width=534) (never executed)

  • Output: kp_4.id, kp_4.asin, kp_4.recordeddate, kp_4.country, kp_4.salesrank, kp_4.category, kp_4.unitssold, kp_4.reviews, kp_4.is1p, kp_4.is3p, kp_4.price, kp_4.iskeepa
53. 0.000 0.000 ↓ 0.0 0

Hash (cost=164.56..164.56 rows=3,790 width=4,136) (never executed)

  • Output: p_4.segment_id, p_4.id, p_4.asin, p_4.imageurl, p_4.product_url, p_4.brand, p_4.name, p_4.country, ptr.revenue
54. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=0.03..164.56 rows=3,790 width=4,136) (never executed)

  • Output: p_4.segment_id, p_4.id, p_4.asin, p_4.imageurl, p_4.product_url, p_4.brand, p_4.name, p_4.country, ptr.revenue
  • Hash Cond: ((p_4.segment_id)::text = ptr.segment_id)
55. 0.000 0.000 ↓ 0.0 0

CTE Scan on products p_4 (cost=0.00..150.12 rows=3,790 width=4,128) (never executed)

  • Output: p_4.segment_id, p_4.id, p_4.asin, p_4.country, p_4.brand, p_4.name, p_4.product_url, p_4.imageurl, p_4.scraped_parent_asin, p_4.is_shared_bsr
  • Filter: ((NOT p_4.is_shared_bsr) OR (p_4.is_shared_bsr IS NULL) OR (p_4.scraped_parent_asin IS NULL))
56. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.02..0.02 rows=1 width=40) (never executed)

  • Output: ptr.revenue, ptr.segment_id
57. 0.000 0.000 ↓ 0.0 0

CTE Scan on previous_total_revenue ptr (cost=0.00..0.02 rows=1 width=40) (never executed)

  • Output: ptr.revenue, ptr.segment_id
58. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2_2 (cost=87,000,358,011.92..87,000,366,102.92 rows=200 width=481) (never executed)

  • Output: ""*SELECT* 2_2"".segment_id, ""*SELECT* 2_2"".id, ""*SELECT* 2_2"".asin, ""*SELECT* 2_2"".imageurl, ""*SELECT* 2_2"".producturl, ""*SELECT* 2_2"".brand, ""*SELECT* 2_2"".name, ""*SELECT* 2_2"".country, ""*SELECT* 2_2"".revenue, ""*SELECT* 2_2"".unitsales, ""*SELECT* 2_2"".share, ""*SELECT* 2_2"".revenue1p, ""*SELECT* 2_2"".revenue3p, ""*SELECT* 2_2"".unitsales1p, ""*SELECT* 2_2"".unitsales3p, true, NULL::text, ""*SELECT* 2_2"".splitrevenuedate, NULL::text
59. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=87,000,358,011.92..87,000,366,100.92 rows=200 width=997) (never executed)

  • Output: max(combined_with_children.segment_id), concat(max(combined_with_children.country), '/', COALESCE(max((combined_with_children.sharedbsrparentasin)::text), max((combined_with_children.asin)::text))), COALESCE(max((combined_with_children.sharedbsrparentasin)::text), max((combined_with_children.asin)::text)), max(combined_with_children.""imageUrl""), max(combined_with_children.""productUrl""), max(combined_with_children.brand), max(combined_with_children.name), max(combined_with_children.country), sum(combined_with_children.revenue), sum(combined_with_children.ppunitsales), sum(combined_with_children.share), sum(combined_with_children.revenue1p), sum(combined_with_children.revenue3p), sum(combined_with_children.ppunitsales1p), sum(combined_with_children.ppunitsales3p), true, NULL::text, max(combined_with_children.splitrevenuedate), NULL::text, combined_with_children.sharedbsrparentasin
  • Group Key: combined_with_children.sharedbsrparentasin
60. 0.000 0.000 ↓ 0.0 0

Sort (cost=87,000,358,011.92..87,000,358,396.92 rows=154,000 width=900) (never executed)

  • Output: combined_with_children.sharedbsrparentasin, combined_with_children.segment_id, combined_with_children.country, combined_with_children.asin, combined_with_children.""imageUrl"", combined_with_children.""productUrl"", combined_with_children.brand, combined_with_children.name, combined_with_children.revenue, combined_with_children.ppunitsales, combined_with_children.share, combined_with_children.revenue1p, combined_with_children.revenue3p, combined_with_children.ppunitsales1p, combined_with_children.ppunitsales3p, combined_with_children.splitrevenuedate
  • Sort Key: combined_with_children.sharedbsrparentasin
61. 0.000 0.000 ↓ 0.0 0

Subquery Scan on combined_with_children (cost=16,207,631,809.31..87,000,344,742.84 rows=154,000 width=900) (never executed)

  • Output: combined_with_children.sharedbsrparentasin, combined_with_children.segment_id, combined_with_children.country, combined_with_children.asin, combined_with_children.""imageUrl"", combined_with_children.""productUrl"", combined_with_children.brand, combined_with_children.name, combined_with_children.revenue, combined_with_children.ppunitsales, combined_with_children.share, combined_with_children.revenue1p, combined_with_children.revenue3p, combined_with_children.ppunitsales1p, combined_with_children.ppunitsales3p, combined_with_children.splitrevenuedate
62. 0.000 0.000 ↓ 0.0 0

Append (cost=16,207,631,809.31..87,000,343,202.84 rows=154,000 width=965) (never executed)

63. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1_1 (cost=16,207,631,809.31..16,207,634,579.31 rows=4,000 width=1,449) (never executed)

  • Output: ""*SELECT* 1_1"".segment_id, ""*SELECT* 1_1"".id, ""*SELECT* 1_1"".asin, ""*SELECT* 1_1"".""imageUrl"", ""*SELECT* 1_1"".""productUrl"", ""*SELECT* 1_1"".brand, ""*SELECT* 1_1"".name, ""*SELECT* 1_1"".country, ""*SELECT* 1_1"".revenue, ""*SELECT* 1_1"".ppunitsales, ""*SELECT* 1_1"".share, ""*SELECT* 1_1"".revenue1p, ""*SELECT* 1_1"".revenue3p, ""*SELECT* 1_1"".ppunitsales1p, ""*SELECT* 1_1"".ppunitsales3p, true, ""*SELECT* 1_1"".sharedbsrparentasin, ""*SELECT* 1_1"".splitrevenuedate, NULL::text
64. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=16,207,631,809.31..16,207,634,539.31 rows=4,000 width=1,973) (never executed)

  • Output: (max((p_5.segment_id)::text)), concat(max((max((p_5.country)::text))), '/', p_5.scraped_parent_asin), p_5.scraped_parent_asin, max((max((p_5.imageurl)::text))), max((max((p_5.product_url)::text))), max((max((p_5.brand)::text))), max((max((p_5.name)::text))), max((max((p_5.country)::text))), sum(((COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.unitssold DESC)), '0'::bigint))::double precision * COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.price DESC)), '0'::double precision))), sum(COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.unitssold DESC)), '0'::bigint)), ((sum(((COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.unitssold DESC)), '0'::bigint))::double precision * COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.price DESC)), '0'::double precision))) * '100'::double precision) / NULLIF(ptr_1.revenue, '0'::double precision)), sum(CASE WHEN ((sum((kp_5.is1p)::integer) >= sum((kp_5.is3p)::integer))) THEN ((COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.unitssold DESC)), '0'::bigint))::double precision * COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.price DESC)), '0'::double precision)) ELSE '0'::double precision END), sum(CASE WHEN ((sum((kp_5.is1p)::integer) < sum((kp_5.is3p)::integer))) THEN ((COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.unitssold DESC)), '0'::bigint))::double precision * COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.price DESC)), '0'::double precision)) ELSE '0'::double precision END), sum(CASE WHEN ((sum((kp_5.is1p)::integer) >= sum((kp_5.is3p)::integer))) THEN COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.unitssold DESC)), '0'::bigint) ELSE '0'::bigint END), sum(CASE WHEN ((sum((kp_5.is1p)::integer) < sum((kp_5.is3p)::integer))) THEN COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.unitssold DESC)), '0'::bigint) ELSE '0'::bigint END), true, p_5.scraped_parent_asin, CASE WHEN (max((max((kp_5.category)::text))) = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN NULL::text ELSE '2020-05-21'::text END, NULL::text, p_5.scraped_parent_asin, ptr_1.revenue
  • Group Key: (max((p_5.segment_id)::text)), p_5.scraped_parent_asin, ptr_1.revenue
65. 0.000 0.000 ↓ 0.0 0

Sort (cost=16,207,631,809.31..16,207,631,909.31 rows=40,000 width=766) (never executed)

  • Output: (max((p_5.segment_id)::text)), p_5.scraped_parent_asin, ptr_1.revenue, (max((p_5.country)::text)), (max((p_5.imageurl)::text)), (max((p_5.product_url)::text)), (max((p_5.brand)::text)), (max((p_5.name)::text)), (percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.unitssold DESC)), (percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.price DESC)), ((sum((kp_5.is1p)::integer) >= sum((kp_5.is3p)::integer))), ((sum((kp_5.is1p)::integer) < sum((kp_5.is3p)::integer))), (max((kp_5.category)::text))
  • Sort Key: (max((p_5.segment_id)::text)), p_5.scraped_parent_asin, ptr_1.revenue
66. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=15,995,732,791.12..16,207,628,751.77 rows=40,000 width=766) (never executed)

  • Output: (max((p_5.segment_id)::text)), p_5.scraped_parent_asin, ptr_1.revenue, (max((p_5.country)::text)), (max((p_5.imageurl)::text)), (max((p_5.product_url)::text)), (max((p_5.brand)::text)), (max((p_5.name)::text)), (percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.unitssold DESC)), (percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.price DESC)), ((sum((kp_5.is1p)::integer) >= sum((kp_5.is3p)::integer))), ((sum((kp_5.is1p)::integer) < sum((kp_5.is3p)::integer))), (max((kp_5.category)::text))
  • Hash Cond: ((max((p_5.segment_id)::text)) = ptr_1.segment_id)
67. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=15,995,732,791.08..16,207,628,199.73 rows=40,000 width=766) (never executed)

  • Output: p_5.scraped_parent_asin, max((p_5.segment_id)::text), max((p_5.imageurl)::text), max((p_5.product_url)::text), max((p_5.brand)::text), max((p_5.name)::text), max((p_5.country)::text), max((kp_5.category)::text), (sum((kp_5.is1p)::integer) >= sum((kp_5.is3p)::integer)), (sum((kp_5.is1p)::integer) < sum((kp_5.is3p)::integer)), percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.unitssold DESC), percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_5.price DESC), kp_5.recordeddate
  • Group Key: p_5.scraped_parent_asin, kp_5.recordeddate
68. 0.000 0.000 ↓ 0.0 0

Sort (cost=15,995,732,791.08..16,006,327,521.52 rows=4,237,892,173 width=4,154) (never executed)

  • Output: p_5.scraped_parent_asin, kp_5.recordeddate, p_5.segment_id, p_5.imageurl, p_5.product_url, p_5.brand, p_5.name, p_5.country, kp_5.category, kp_5.is1p, kp_5.is3p, kp_5.unitssold, kp_5.price
  • Sort Key: p_5.scraped_parent_asin, kp_5.recordeddate
69. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=196.80..166,786,772.61 rows=4,237,892,173 width=4,154) (never executed)

  • Output: p_5.scraped_parent_asin, kp_5.recordeddate, p_5.segment_id, p_5.imageurl, p_5.product_url, p_5.brand, p_5.name, p_5.country, kp_5.category, kp_5.is1p, kp_5.is3p, kp_5.unitssold, kp_5.price
  • Hash Cond: ((kp_5.id)::text = (p_5.id)::text)
70. 0.000 0.000 ↓ 0.0 0

CTE Scan on current_keepa_products kp_5 (cost=0.00..18,006,370.97 rows=226,989,404 width=1,058) (never executed)

  • Output: kp_5.id, kp_5.asin, kp_5.recordeddate, kp_5.country, kp_5.salesrank, kp_5.category, kp_5.unitssold, kp_5.reviews, kp_5.is1p, kp_5.is3p, kp_5.price, kp_5.iskeepa
  • Filter: (((kp_5.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) OR (kp_5.recordeddate >= '1590019200000'::bigint))
71. 0.000 0.000 ↓ 0.0 0

Hash (cost=150.12..150.12 rows=3,734 width=4,128) (never executed)

  • Output: p_5.scraped_parent_asin, p_5.segment_id, p_5.imageurl, p_5.product_url, p_5.brand, p_5.name, p_5.country, p_5.id
72. 0.000 0.000 ↓ 0.0 0

CTE Scan on products p_5 (cost=0.00..150.12 rows=3,734 width=4,128) (never executed)

  • Output: p_5.scraped_parent_asin, p_5.segment_id, p_5.imageurl, p_5.product_url, p_5.brand, p_5.name, p_5.country, p_5.id
  • Filter: (p_5.is_shared_bsr AND (p_5.scraped_parent_asin IS NOT NULL))
73. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.02..0.02 rows=1 width=40) (never executed)

  • Output: ptr_1.revenue, ptr_1.segment_id
74. 0.000 0.000 ↓ 0.0 0

CTE Scan on previous_total_revenue ptr_1 (cost=0.00..0.02 rows=1 width=40) (never executed)

  • Output: ptr_1.revenue, ptr_1.segment_id
75. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2_3 (cost=70,105,994,435.89..70,792,708,623.53 rows=150,000 width=1,449) (never executed)

  • Output: ""*SELECT* 2_3"".segment_id, ""*SELECT* 2_3"".id, ""*SELECT* 2_3"".asin, ""*SELECT* 2_3"".""imageUrl"", ""*SELECT* 2_3"".""productUrl"", ""*SELECT* 2_3"".brand, ""*SELECT* 2_3"".name, ""*SELECT* 2_3"".country, ""*SELECT* 2_3"".revenue, ""*SELECT* 2_3"".ppunitsales, ""*SELECT* 2_3"".share, ""*SELECT* 2_3"".revenue1p, ""*SELECT* 2_3"".revenue3p, ""*SELECT* 2_3"".ppunitsales1p, ""*SELECT* 2_3"".ppunitsales3p, true, ""*SELECT* 2_3"".sharedbsrparentasin, ""*SELECT* 2_3"".splitrevenuedate, NULL::text
76. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=70,105,994,435.89..70,792,707,123.53 rows=150,000 width=5,877) (never executed)

  • Output: p_6.segment_id, p_6.id, p_6.asin, p_6.imageurl, p_6.product_url, p_6.brand, p_6.name, p_6.country, CASE WHEN ((kp_6.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::double precision ELSE sum(((COALESCE(kp_6.unitssold, '0'::bigint))::double precision * COALESCE(kp_6.price, '0'::double precision))) END, CASE WHEN ((kp_6.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::numeric ELSE sum(COALESCE(kp_6.unitssold, '0'::bigint)) END, CASE WHEN ((kp_6.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::double precision ELSE ((sum(((COALESCE(kp_6.unitssold, '0'::bigint))::double precision * COALESCE(kp_6.price, '0'::double precision))) * '100'::double precision) / NULLIF(ptr_2.revenue, '0'::double precision)) END, CASE WHEN ((kp_6.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::double precision ELSE sum(CASE WHEN kp_6.is1p THEN ((COALESCE(kp_6.unitssold, '0'::bigint))::double precision * COALESCE(kp_6.price, '0'::double precision)) ELSE '0'::double precision END) END, CASE WHEN ((kp_6.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::double precision ELSE sum(CASE WHEN kp_6.is3p THEN ((COALESCE(kp_6.unitssold, '0'::bigint))::double precision * COALESCE(kp_6.price, '0'::double precision)) ELSE '0'::double precision END) END, CASE WHEN ((kp_6.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::numeric ELSE sum(CASE WHEN kp_6.is1p THEN COALESCE(kp_6.unitssold, '0'::bigint) ELSE '0'::bigint END) END, CASE WHEN ((kp_6.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::numeric ELSE sum(CASE WHEN kp_6.is3p THEN COALESCE(kp_6.unitssold, '0'::bigint) ELSE '0'::bigint END) END, true, p_6.scraped_parent_asin, CASE WHEN ((kp_6.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN NULL::text ELSE '2020-05-21'::text END, NULL::text, p_6.scraped_parent_asin, ptr_2.revenue, kp_6.category
  • Group Key: p_6.segment_id, p_6.id, p_6.asin, p_6.scraped_parent_asin, p_6.imageurl, p_6.product_url, p_6.brand, p_6.country, ptr_2.revenue, p_6.name, kp_6.category
77. 0.000 0.000 ↓ 0.0 0

Sort (cost=70,105,994,435.89..70,131,427,878.02 rows=10,173,376,854 width=5,186) (never executed)

  • Output: p_6.segment_id, p_6.id, p_6.asin, p_6.imageurl, p_6.product_url, p_6.brand, p_6.name, p_6.country, p_6.scraped_parent_asin, ptr_2.revenue, kp_6.category, kp_6.unitssold, kp_6.price, kp_6.is1p, kp_6.is3p
  • Sort Key: p_6.segment_id, p_6.id, p_6.asin, p_6.scraped_parent_asin, p_6.imageurl, p_6.product_url, p_6.brand, p_6.country, ptr_2.revenue, p_6.name, kp_6.category
78. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=211.02..400,383,850.13 rows=10,173,376,854 width=5,186) (never executed)

  • Output: p_6.segment_id, p_6.id, p_6.asin, p_6.imageurl, p_6.product_url, p_6.brand, p_6.name, p_6.country, p_6.scraped_parent_asin, ptr_2.revenue, kp_6.category, kp_6.unitssold, kp_6.price, kp_6.is1p, kp_6.is3p
  • Hash Cond: ((kp_6.id)::text = (p_6.id)::text)
79. 0.000 0.000 ↓ 0.0 0

CTE Scan on previous_keepa_products kp_6 (cost=0.00..43,225,639.16 rows=544,905,027 width=1,050) (never executed)

  • Output: kp_6.id, kp_6.asin, kp_6.recordeddate, kp_6.country, kp_6.salesrank, kp_6.category, kp_6.unitssold, kp_6.reviews, kp_6.is1p, kp_6.is3p, kp_6.price, kp_6.iskeepa
  • Filter: (((kp_6.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) OR (kp_6.recordeddate < '1590019200000'::bigint))
80. 0.000 0.000 ↓ 0.0 0

Hash (cost=164.34..164.34 rows=3,734 width=4,652) (never executed)

  • Output: p_6.segment_id, p_6.id, p_6.asin, p_6.imageurl, p_6.product_url, p_6.brand, p_6.name, p_6.country, p_6.scraped_parent_asin, ptr_2.revenue
81. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=0.03..164.34 rows=3,734 width=4,652) (never executed)

  • Output: p_6.segment_id, p_6.id, p_6.asin, p_6.imageurl, p_6.product_url, p_6.brand, p_6.name, p_6.country, p_6.scraped_parent_asin, ptr_2.revenue
  • Hash Cond: ((p_6.segment_id)::text = ptr_2.segment_id)
82. 0.000 0.000 ↓ 0.0 0

CTE Scan on products p_6 (cost=0.00..150.12 rows=3,734 width=4,644) (never executed)

  • Output: p_6.segment_id, p_6.id, p_6.asin, p_6.country, p_6.brand, p_6.name, p_6.product_url, p_6.imageurl, p_6.scraped_parent_asin, p_6.is_shared_bsr
  • Filter: (p_6.is_shared_bsr AND (p_6.scraped_parent_asin IS NOT NULL))
83. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.02..0.02 rows=1 width=40) (never executed)

  • Output: ptr_2.revenue, ptr_2.segment_id
84. 0.000 0.000 ↓ 0.0 0

CTE Scan on previous_total_revenue ptr_2 (cost=0.00..0.02 rows=1 width=40) (never executed)

  • Output: ptr_2.revenue, ptr_2.segment_id
85.          

CTE current_period

86. 0.510 150,380.677 ↓ 6.7 6,376 1

Append (cost=1,193,495,218.37..37,420,080,509.49 rows=950 width=457) (actual time=148,664.610..150,380.677 rows=6,376 loops=1)

  • Buffers: shared hit=3,785,219 read=170,347
87. 0.879 148,669.830 ↓ 7.9 5,938 1

Subquery Scan on *SELECT* 1_2 (cost=1,193,495,218.37..1,193,495,244.62 rows=750 width=4,329) (actual time=148,664.610..148,669.830 rows=5,938 loops=1)

  • Output: ""*SELECT* 1_2"".segment_id, ""*SELECT* 1_2"".id, ""*SELECT* 1_2"".asin, ""*SELECT* 1_2"".""imageUrl"", ""*SELECT* 1_2"".""productUrl"", ""*SELECT* 1_2"".brand, ""*SELECT* 1_2"".name, ""*SELECT* 1_2"".country, ""*SELECT* 1_2"".revenue, ""*SELECT* 1_2"".unitsales, ""*SELECT* 1_2"".share, ""*SELECT* 1_2"".revenue1p, ""*SELECT* 1_2"".revenue3p, ""*SELECT* 1_2"".unitsales1p, ""*SELECT* 1_2"".unitsales3p, false, NULL::text, NULL::text, NULL::bigint
  • Buffers: shared hit=3,785,219 read=170,347
88. 796.758 148,668.951 ↓ 7.9 5,938 1

HashAggregate (cost=1,193,495,218.37..1,193,495,237.12 rows=750 width=4,337) (actual time=148,664.609..148,668.951 rows=5,938 loops=1)

  • Output: p_7.segment_id, p_7.id, p_7.asin, p_7.imageurl, p_7.product_url, p_7.brand, p_7.name, p_7.country, sum(((COALESCE(kp_7.unitssold, '0'::bigint))::double precision * COALESCE(kp_7.price, '0'::double precision))), sum(COALESCE(kp_7.unitssold, '0'::bigint)), ((sum(((COALESCE(kp_7.unitssold, '0'::bigint))::double precision * COALESCE(kp_7.price, '0'::double precision))) * '100'::double precision) / NULLIF(ctr.revenue, '0'::double precision)), sum(CASE WHEN kp_7.is1p THEN ((COALESCE(kp_7.unitssold, '0'::bigint))::double precision * COALESCE(kp_7.price, '0'::double precision)) ELSE '0'::double precision END), sum(CASE WHEN kp_7.is3p THEN ((COALESCE(kp_7.unitssold, '0'::bigint))::double precision * COALESCE(kp_7.price, '0'::double precision)) ELSE '0'::double precision END), sum(CASE WHEN kp_7.is1p THEN COALESCE(kp_7.unitssold, '0'::bigint) ELSE '0'::bigint END), sum(CASE WHEN kp_7.is3p THEN COALESCE(kp_7.unitssold, '0'::bigint) ELSE '0'::bigint END), false, NULL::text, NULL::text, NULL::bigint, ctr.revenue
  • Group Key: p_7.segment_id, p_7.id, p_7.asin, p_7.imageurl, p_7.product_url, p_7.brand, p_7.country, ctr.revenue, p_7.name
  • Buffers: shared hit=3,785,219 read=170,347
89. 374.841 147,872.193 ↑ 11,609.5 1,068,778 1

Hash Right Join (cost=211.93..449,013,626.09 rows=12,408,026,538 width=4,154) (actual time=147,387.682..147,872.193 rows=1,068,778 loops=1)

  • Output: p_7.segment_id, p_7.id, p_7.asin, p_7.imageurl, p_7.product_url, p_7.brand, p_7.name, p_7.country, ctr.revenue, kp_7.unitssold, kp_7.price, kp_7.is1p, kp_7.is3p
  • Hash Cond: ((kp_7.id)::text = (p_7.id)::text)
  • Buffers: shared hit=3,785,219 read=170,347
90. 109.692 109.692 ↑ 498.1 1,314,652 1

CTE Scan on current_keepa_products kp_7 (cost=0.00..13,095,542.52 rows=654,777,126 width=534) (actual time=0.001..109.692 rows=1,314,652 loops=1)

  • Output: kp_7.id, kp_7.asin, kp_7.recordeddate, kp_7.country, kp_7.salesrank, kp_7.category, kp_7.unitssold, kp_7.reviews, kp_7.is1p, kp_7.is3p, kp_7.price, kp_7.iskeepa
91. 2.626 147,387.660 ↓ 1.6 5,938 1

Hash (cost=164.56..164.56 rows=3,790 width=4,136) (actual time=147,387.660..147,387.660 rows=5,938 loops=1)

  • Output: p_7.segment_id, p_7.id, p_7.asin, p_7.imageurl, p_7.product_url, p_7.brand, p_7.name, p_7.country, ctr.revenue
  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,619kB
  • Buffers: shared hit=3,785,219 read=170,347
92. 1.231 147,385.034 ↓ 1.6 5,938 1

Hash Left Join (cost=0.03..164.56 rows=3,790 width=4,136) (actual time=147,382.319..147,385.034 rows=5,938 loops=1)

  • Output: p_7.segment_id, p_7.id, p_7.asin, p_7.imageurl, p_7.product_url, p_7.brand, p_7.name, p_7.country, ctr.revenue
  • Hash Cond: ((p_7.segment_id)::text = ctr.segment_id)
  • Buffers: shared hit=3,785,219 read=170,347
93. 1.888 1.888 ↓ 1.6 5,938 1

CTE Scan on products p_7 (cost=0.00..150.12 rows=3,790 width=4,128) (actual time=0.399..1.888 rows=5,938 loops=1)

  • Output: p_7.segment_id, p_7.id, p_7.asin, p_7.country, p_7.brand, p_7.name, p_7.product_url, p_7.imageurl, p_7.scraped_parent_asin, p_7.is_shared_bsr
  • Filter: ((NOT p_7.is_shared_bsr) OR (p_7.is_shared_bsr IS NULL) OR (p_7.scraped_parent_asin IS NULL))
  • Rows Removed by Filter: 1,366
  • Buffers: shared hit=31
94. 0.003 147,381.915 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=40) (actual time=147,381.915..147,381.915 rows=1 loops=1)

  • Output: ctr.revenue, ctr.segment_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3,785,188 read=170,347
95. 147,381.912 147,381.912 ↑ 1.0 1 1

CTE Scan on current_total_revenue ctr (cost=0.00..0.02 rows=1 width=40) (actual time=147,381.912..147,381.912 rows=1 loops=1)

  • Output: ctr.revenue, ctr.segment_id
  • Buffers: shared hit=3,785,188 read=170,347
96. 0.081 1,710.337 ↓ 2.2 438 1

Subquery Scan on *SELECT* 2_4 (cost=36,226,576,788.37..36,226,585,264.87 rows=200 width=457) (actual time=1,705.726..1,710.337 rows=438 loops=1)

  • Output: ""*SELECT* 2_4"".segment_id, ""*SELECT* 2_4"".id, ""*SELECT* 2_4"".asin, ""*SELECT* 2_4"".imageurl, ""*SELECT* 2_4"".producturl, ""*SELECT* 2_4"".brand, ""*SELECT* 2_4"".name, ""*SELECT* 2_4"".country, ""*SELECT* 2_4"".revenue, ""*SELECT* 2_4"".unitsales, ""*SELECT* 2_4"".share, ""*SELECT* 2_4"".revenue1p, ""*SELECT* 2_4"".revenue3p, ""*SELECT* 2_4"".unitsales1p, ""*SELECT* 2_4"".unitsales3p, true, NULL::text, ""*SELECT* 2_4"".splitrevenuedate, ""*SELECT* 2_4"".sharedbsrvariantchildcount
97. 4.447 1,710.256 ↓ 2.2 438 1

GroupAggregate (cost=36,226,576,788.37..36,226,585,262.87 rows=200 width=973) (actual time=1,705.724..1,710.256 rows=438 loops=1)

  • Output: max(combined_with_children_1.segment_id), concat(max(combined_with_children_1.country), '/', COALESCE(max((combined_with_children_1.sharedbsrparentasin)::text), max((combined_with_children_1.asin)::text))), COALESCE(max((combined_with_children_1.sharedbsrparentasin)::text), max((combined_with_children_1.asin)::text)), max(combined_with_children_1.""imageUrl""), max(combined_with_children_1.""productUrl""), max(combined_with_children_1.brand), max(combined_with_children_1.name), max(combined_with_children_1.country), sum(combined_with_children_1.revenue), sum(combined_with_children_1.unitsales), sum(combined_with_children_1.share), sum(combined_with_children_1.revenue1p), sum(combined_with_children_1.revenue3p), sum(combined_with_children_1.unitsales1p), sum(combined_with_children_1.unitsales3p), true, NULL::text, max(combined_with_children_1.splitrevenuedate), (count(1) - 1), combined_with_children_1.sharedbsrparentasin
  • Group Key: combined_with_children_1.sharedbsrparentasin
98. 3.669 1,705.809 ↑ 85.4 1,804 1

Sort (cost=36,226,576,788.37..36,226,577,173.37 rows=154,000 width=900) (actual time=1,705.690..1,705.809 rows=1,804 loops=1)

  • Output: combined_with_children_1.sharedbsrparentasin, combined_with_children_1.segment_id, combined_with_children_1.country, combined_with_children_1.asin, combined_with_children_1.""imageUrl"", combined_with_children_1.""productUrl"", combined_with_children_1.brand, combined_with_children_1.name, combined_with_children_1.revenue, combined_with_children_1.unitsales, combined_with_children_1.share, combined_with_children_1.revenue1p, combined_with_children_1.revenue3p, combined_with_children_1.unitsales1p, combined_with_children_1.unitsales3p, combined_with_children_1.splitrevenuedate
  • Sort Key: combined_with_children_1.sharedbsrparentasin
  • Sort Method: quicksort Memory: 918kB
99. 0.310 1,702.140 ↑ 85.4 1,804 1

Subquery Scan on combined_with_children_1 (cost=16,207,631,809.31..36,226,563,519.29 rows=154,000 width=900) (actual time=764.263..1,702.140 rows=1,804 loops=1)

  • Output: combined_with_children_1.sharedbsrparentasin, combined_with_children_1.segment_id, combined_with_children_1.country, combined_with_children_1.asin, combined_with_children_1.""imageUrl"", combined_with_children_1.""productUrl"", combined_with_children_1.brand, combined_with_children_1.name, combined_with_children_1.revenue, combined_with_children_1.unitsales, combined_with_children_1.share, combined_with_children_1.revenue1p, combined_with_children_1.revenue3p, combined_with_children_1.unitsales1p, combined_with_children_1.unitsales3p, combined_with_children_1.splitrevenuedate
100. 0.175 1,701.830 ↑ 85.4 1,804 1

Append (cost=16,207,631,809.31..36,226,561,979.29 rows=154,000 width=965) (actual time=764.262..1,701.830 rows=1,804 loops=1)

101. 0.083 787.724 ↑ 9.1 438 1

Subquery Scan on *SELECT* 1_3 (cost=16,207,631,809.31..16,207,634,579.31 rows=4,000 width=1,449) (actual time=764.261..787.724 rows=438 loops=1)

  • Output: ""*SELECT* 1_3"".segment_id, ""*SELECT* 1_3"".id, ""*SELECT* 1_3"".asin, ""*SELECT* 1_3"".""imageUrl"", ""*SELECT* 1_3"".""productUrl"", ""*SELECT* 1_3"".brand, ""*SELECT* 1_3"".name, ""*SELECT* 1_3"".country, ""*SELECT* 1_3"".revenue, ""*SELECT* 1_3"".unitsales, ""*SELECT* 1_3"".share, ""*SELECT* 1_3"".revenue1p, ""*SELECT* 1_3"".revenue3p, ""*SELECT* 1_3"".unitsales1p, ""*SELECT* 1_3"".unitsales3p, true, ""*SELECT* 1_3"".sharedbsrparentasin, ""*SELECT* 1_3"".splitrevenuedate, NULL::text
102. 21.788 787.641 ↑ 9.1 438 1

GroupAggregate (cost=16,207,631,809.31..16,207,634,539.31 rows=4,000 width=1,973) (actual time=764.260..787.641 rows=438 loops=1)

  • Output: (max((p_8.segment_id)::text)), concat(max((max((p_8.country)::text))), '/', p_8.scraped_parent_asin), p_8.scraped_parent_asin, max((max((p_8.imageurl)::text))), max((max((p_8.product_url)::text))), max((max((p_8.brand)::text))), max((max((p_8.name)::text))), max((max((p_8.country)::text))), sum(((COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.unitssold DESC)), '0'::bigint))::double precision * COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.price DESC)), '0'::double precision))), sum(COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.unitssold DESC)), '0'::bigint)), ((sum(((COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.unitssold DESC)), '0'::bigint))::double precision * COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.price DESC)), '0'::double precision))) * '100'::double precision) / NULLIF(ctr_1.revenue, '0'::double precision)), sum(CASE WHEN ((sum((kp_8.is1p)::integer) >= sum((kp_8.is3p)::integer))) THEN ((COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.unitssold DESC)), '0'::bigint))::double precision * COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.price DESC)), '0'::double precision)) ELSE '0'::double precision END), sum(CASE WHEN ((sum((kp_8.is1p)::integer) < sum((kp_8.is3p)::integer))) THEN ((COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.unitssold DESC)), '0'::bigint))::double precision * COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.price DESC)), '0'::double precision)) ELSE '0'::double precision END), sum(CASE WHEN ((sum((kp_8.is1p)::integer) >= sum((kp_8.is3p)::integer))) THEN COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.unitssold DESC)), '0'::bigint) ELSE '0'::bigint END), sum(CASE WHEN ((sum((kp_8.is1p)::integer) < sum((kp_8.is3p)::integer))) THEN COALESCE((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.unitssold DESC)), '0'::bigint) ELSE '0'::bigint END), true, p_8.scraped_parent_asin, CASE WHEN (max((max((kp_8.category)::text))) = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN NULL::text ELSE '2020-05-21'::text END, NULL::text, p_8.scraped_parent_asin, ctr_1.revenue
  • Group Key: (max((p_8.segment_id)::text)), p_8.scraped_parent_asin, ctr_1.revenue
103. 21.401 765.853 ↑ 1.2 32,850 1

Sort (cost=16,207,631,809.31..16,207,631,909.31 rows=40,000 width=766) (actual time=764.186..765.853 rows=32,850 loops=1)

  • Output: (max((p_8.segment_id)::text)), p_8.scraped_parent_asin, ctr_1.revenue, (max((p_8.country)::text)), (max((p_8.imageurl)::text)), (max((p_8.product_url)::text)), (max((p_8.brand)::text)), (max((p_8.name)::text)), (percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.unitssold DESC)), (percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.price DESC)), ((sum((kp_8.is1p)::integer) >= sum((kp_8.is3p)::integer))), ((sum((kp_8.is1p)::integer) < sum((kp_8.is3p)::integer))), (max((kp_8.category)::text))
  • Sort Key: (max((p_8.segment_id)::text)), p_8.scraped_parent_asin, ctr_1.revenue
  • Sort Method: quicksort Memory: 15,925kB
104. 8.783 744.452 ↑ 1.2 32,850 1

Hash Left Join (cost=15,995,732,791.12..16,207,628,751.77 rows=40,000 width=766) (actual time=478.521..744.452 rows=32,850 loops=1)

  • Output: (max((p_8.segment_id)::text)), p_8.scraped_parent_asin, ctr_1.revenue, (max((p_8.country)::text)), (max((p_8.imageurl)::text)), (max((p_8.product_url)::text)), (max((p_8.brand)::text)), (max((p_8.name)::text)), (percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.unitssold DESC)), (percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.price DESC)), ((sum((kp_8.is1p)::integer) >= sum((kp_8.is3p)::integer))), ((sum((kp_8.is1p)::integer) < sum((kp_8.is3p)::integer))), (max((kp_8.category)::text))
  • Hash Cond: ((max((p_8.segment_id)::text)) = ctr_1.segment_id)
105. 250.746 735.665 ↑ 1.2 32,850 1

GroupAggregate (cost=15,995,732,791.08..16,207,628,199.73 rows=40,000 width=766) (actual time=478.508..735.665 rows=32,850 loops=1)

  • Output: p_8.scraped_parent_asin, max((p_8.segment_id)::text), max((p_8.imageurl)::text), max((p_8.product_url)::text), max((p_8.brand)::text), max((p_8.name)::text), max((p_8.country)::text), max((kp_8.category)::text), (sum((kp_8.is1p)::integer) >= sum((kp_8.is3p)::integer)), (sum((kp_8.is1p)::integer) < sum((kp_8.is3p)::integer)), percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.unitssold DESC), percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY kp_8.price DESC), kp_8.recordeddate
  • Group Key: p_8.scraped_parent_asin, kp_8.recordeddate
106. 193.030 484.919 ↑ 41,367.9 102,444 1

Sort (cost=15,995,732,791.08..16,006,327,521.52 rows=4,237,892,173 width=4,154) (actual time=478.466..484.919 rows=102,444 loops=1)

  • Output: p_8.scraped_parent_asin, kp_8.recordeddate, p_8.segment_id, p_8.imageurl, p_8.product_url, p_8.brand, p_8.name, p_8.country, kp_8.category, kp_8.is1p, kp_8.is3p, kp_8.unitssold, kp_8.price
  • Sort Key: p_8.scraped_parent_asin, kp_8.recordeddate
  • Sort Method: quicksort Memory: 45,715kB
107. 67.505 291.889 ↑ 41,367.9 102,444 1

Hash Join (cost=196.80..166,786,772.61 rows=4,237,892,173 width=4,154) (actual time=2.018..291.889 rows=102,444 loops=1)

  • Output: p_8.scraped_parent_asin, kp_8.recordeddate, p_8.segment_id, p_8.imageurl, p_8.product_url, p_8.brand, p_8.name, p_8.country, kp_8.category, kp_8.is1p, kp_8.is3p, kp_8.unitssold, kp_8.price
  • Hash Cond: ((kp_8.id)::text = (p_8.id)::text)
108. 222.506 222.506 ↑ 414.4 547,732 1

CTE Scan on current_keepa_products kp_8 (cost=0.00..18,006,370.97 rows=226,989,404 width=1,058) (actual time=0.020..222.506 rows=547,732 loops=1)

  • Output: kp_8.id, kp_8.asin, kp_8.recordeddate, kp_8.country, kp_8.salesrank, kp_8.category, kp_8.unitssold, kp_8.reviews, kp_8.is1p, kp_8.is3p, kp_8.price, kp_8.iskeepa
  • Filter: (((kp_8.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) OR (kp_8.recordeddate >= '1590019200000'::bigint))
  • Rows Removed by Filter: 766,920
109. 0.533 1.878 ↑ 2.7 1,366 1

Hash (cost=150.12..150.12 rows=3,734 width=4,128) (actual time=1.878..1.878 rows=1,366 loops=1)

  • Output: p_8.scraped_parent_asin, p_8.segment_id, p_8.imageurl, p_8.product_url, p_8.brand, p_8.name, p_8.country, p_8.id
  • Buckets: 4,096 Batches: 1 Memory Usage: 379kB
110. 1.345 1.345 ↑ 2.7 1,366 1

CTE Scan on products p_8 (cost=0.00..150.12 rows=3,734 width=4,128) (actual time=0.003..1.345 rows=1,366 loops=1)

  • Output: p_8.scraped_parent_asin, p_8.segment_id, p_8.imageurl, p_8.product_url, p_8.brand, p_8.name, p_8.country, p_8.id
  • Filter: (p_8.is_shared_bsr AND (p_8.scraped_parent_asin IS NOT NULL))
  • Rows Removed by Filter: 5,938
111. 0.002 0.004 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=40) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: ctr_1.revenue, ctr_1.segment_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
112. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on current_total_revenue ctr_1 (cost=0.00..0.02 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: ctr_1.revenue, ctr_1.segment_id
113. 0.325 913.931 ↑ 109.8 1,366 1

Subquery Scan on *SELECT* 2_5 (cost=19,732,858,428.31..20,018,927,399.99 rows=150,000 width=1,449) (actual time=820.429..913.931 rows=1,366 loops=1)

  • Output: ""*SELECT* 2_5"".segment_id, ""*SELECT* 2_5"".id, ""*SELECT* 2_5"".asin, ""*SELECT* 2_5"".""imageUrl"", ""*SELECT* 2_5"".""productUrl"", ""*SELECT* 2_5"".brand, ""*SELECT* 2_5"".name, ""*SELECT* 2_5"".country, ""*SELECT* 2_5"".revenue, ""*SELECT* 2_5"".unitsales, ""*SELECT* 2_5"".share, ""*SELECT* 2_5"".revenue1p, ""*SELECT* 2_5"".revenue3p, ""*SELECT* 2_5"".unitsales1p, ""*SELECT* 2_5"".unitsales3p, true, ""*SELECT* 2_5"".sharedbsrparentasin, ""*SELECT* 2_5"".splitrevenuedate, NULL::text
114. 83.833 913.606 ↑ 109.8 1,366 1

GroupAggregate (cost=19,732,858,428.31..20,018,925,899.99 rows=150,000 width=5,877) (actual time=820.428..913.606 rows=1,366 loops=1)

  • Output: p_9.segment_id, p_9.id, p_9.asin, p_9.imageurl, p_9.product_url, p_9.brand, p_9.name, p_9.country, CASE WHEN ((kp_9.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::double precision ELSE sum(((COALESCE(kp_9.unitssold, '0'::bigint))::double precision * COALESCE(kp_9.price, '0'::double precision))) END, CASE WHEN ((kp_9.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::numeric ELSE sum(COALESCE(kp_9.unitssold, '0'::bigint)) END, CASE WHEN ((kp_9.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::double precision ELSE ((sum(((COALESCE(kp_9.unitssold, '0'::bigint))::double precision * COALESCE(kp_9.price, '0'::double precision))) * '100'::double precision) / NULLIF(ctr_2.revenue, '0'::double precision)) END, CASE WHEN ((kp_9.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::double precision ELSE sum(CASE WHEN kp_9.is1p THEN ((COALESCE(kp_9.unitssold, '0'::bigint))::double precision * COALESCE(kp_9.price, '0'::double precision)) ELSE '0'::double precision END) END, CASE WHEN ((kp_9.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::double precision ELSE sum(CASE WHEN kp_9.is3p THEN ((COALESCE(kp_9.unitssold, '0'::bigint))::double precision * COALESCE(kp_9.price, '0'::double precision)) ELSE '0'::double precision END) END, CASE WHEN ((kp_9.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::numeric ELSE sum(CASE WHEN kp_9.is1p THEN COALESCE(kp_9.unitssold, '0'::bigint) ELSE '0'::bigint END) END, CASE WHEN ((kp_9.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN '0'::numeric ELSE sum(CASE WHEN kp_9.is3p THEN COALESCE(kp_9.unitssold, '0'::bigint) ELSE '0'::bigint END) END, true, p_9.scraped_parent_asin, CASE WHEN ((kp_9.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) THEN NULL::text ELSE '2020-05-21'::text END, NULL::text, p_9.scraped_parent_asin, ctr_2.revenue, kp_9.category
  • Group Key: p_9.segment_id, p_9.id, p_9.asin, p_9.scraped_parent_asin, p_9.imageurl, p_9.product_url, p_9.brand, p_9.country, ctr_2.revenue, p_9.name, kp_9.category
115. 509.881 829.773 ↑ 29,546.8 143,430 1

Sort (cost=19,732,858,428.31..19,743,453,158.74 rows=4,237,892,173 width=5,186) (actual time=820.329..829.773 rows=143,430 loops=1)

  • Output: p_9.segment_id, p_9.id, p_9.asin, p_9.imageurl, p_9.product_url, p_9.brand, p_9.name, p_9.country, p_9.scraped_parent_asin, ctr_2.revenue, kp_9.category, kp_9.unitssold, kp_9.price, kp_9.is1p, kp_9.is3p
  • Sort Key: p_9.segment_id, p_9.id, p_9.asin, p_9.scraped_parent_asin, p_9.imageurl, p_9.product_url, p_9.brand, p_9.country, ctr_2.revenue, p_9.name, kp_9.category
  • Sort Method: quicksort Memory: 74,299kB
116. 93.734 319.892 ↑ 29,546.8 143,430 1

Hash Join (cost=211.02..166,786,786.84 rows=4,237,892,173 width=5,186) (actual time=2.127..319.892 rows=143,430 loops=1)

  • Output: p_9.segment_id, p_9.id, p_9.asin, p_9.imageurl, p_9.product_url, p_9.brand, p_9.name, p_9.country, p_9.scraped_parent_asin, ctr_2.revenue, kp_9.category, kp_9.unitssold, kp_9.price, kp_9.is1p, kp_9.is3p
  • Hash Cond: ((kp_9.id)::text = (p_9.id)::text)
117. 224.171 224.171 ↑ 296.0 766,920 1

CTE Scan on current_keepa_products kp_9 (cost=0.00..18,006,370.97 rows=226,989,404 width=1,050) (actual time=0.002..224.171 rows=766,920 loops=1)

  • Output: kp_9.id, kp_9.asin, kp_9.recordeddate, kp_9.country, kp_9.salesrank, kp_9.category, kp_9.unitssold, kp_9.reviews, kp_9.is1p, kp_9.is3p, kp_9.price, kp_9.iskeepa
  • Filter: (((kp_9.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) OR (kp_9.recordeddate < '1590019200000'::bigint))
  • Rows Removed by Filter: 547,732
118. 0.522 1.987 ↑ 2.7 1,366 1

Hash (cost=164.34..164.34 rows=3,734 width=4,652) (actual time=1.987..1.987 rows=1,366 loops=1)

  • Output: p_9.segment_id, p_9.id, p_9.asin, p_9.imageurl, p_9.product_url, p_9.brand, p_9.name, p_9.country, p_9.scraped_parent_asin, ctr_2.revenue
  • Buckets: 4,096 Batches: 1 Memory Usage: 420kB
119. 0.289 1.465 ↑ 2.7 1,366 1

Hash Left Join (cost=0.03..164.34 rows=3,734 width=4,652) (actual time=0.015..1.465 rows=1,366 loops=1)

  • Output: p_9.segment_id, p_9.id, p_9.asin, p_9.imageurl, p_9.product_url, p_9.brand, p_9.name, p_9.country, p_9.scraped_parent_asin, ctr_2.revenue
  • Hash Cond: ((p_9.segment_id)::text = ctr_2.segment_id)
120. 1.171 1.171 ↑ 2.7 1,366 1

CTE Scan on products p_9 (cost=0.00..150.12 rows=3,734 width=4,644) (actual time=0.003..1.171 rows=1,366 loops=1)

  • Output: p_9.segment_id, p_9.id, p_9.asin, p_9.country, p_9.brand, p_9.name, p_9.product_url, p_9.imageurl, p_9.scraped_parent_asin, p_9.is_shared_bsr
  • Filter: (p_9.is_shared_bsr AND (p_9.scraped_parent_asin IS NOT NULL))
  • Rows Removed by Filter: 5,938
121. 0.003 0.005 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=40) (actual time=0.005..0.005 rows=1 loops=1)

  • Output: ctr_2.revenue, ctr_2.segment_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
122. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on current_total_revenue ctr_2 (cost=0.00..0.02 rows=1 width=40) (actual time=0.001..0.002 rows=1 loops=1)

  • Output: ctr_2.revenue, ctr_2.segment_id
123. 3.053 150,455.509 ↑ 37.8 25 1

Sort (cost=311.99..314.35 rows=945 width=657) (actual time=150,455.507..150,455.509 rows=25 loops=1)

  • Output: cp.id, cp.name, cp.""imageUrl"", cp.""productUrl"", cp.country, cp.asin, cp.brand, cp.share, ((COALESCE((((((COALESCE(cp.share, '0'::double precision) - COALESCE(pp.share, '0'::double precision)))::numeric)::double precision / COALESCE(NULLIF(pp.share, '0'::double precision), NULLIF(cp.share, '0'::double precision))) * '100'::double precision), '0'::double precision))::numeric), ((cp.revenue / '100'::double precision)), ((COALESCE((((((COALESCE(cp.revenue, '0'::double precision) - COALESCE(pp.revenue, '0'::double precision)))::numeric)::double precision / COALESCE(NULLIF(pp.revenue, '0'::double precision), NULLIF(cp.revenue, '0'::double precision))) * '100'::double precision), '0'::double precision))::numeric), ((cp.revenue1p / '100'::double precision)), ((COALESCE((((((COALESCE(cp.revenue1p, '0'::double precision) - COALESCE(pp.revenue1p, '0'::double precision)))::numeric)::double precision / COALESCE(NULLIF(pp.revenue1p, '0'::double precision), NULLIF(cp.revenue1p, '0'::double precision))) * '100'::double precision), '0'::double precision))::numeric), ((cp.revenue3p / '100'::double precision)), ((COALESCE((((((COALESCE(cp.revenue3p, '0'::double precision) - COALESCE(pp.revenue3p, '0'::double precision)))::numeric)::double precision / COALESCE(NULLIF(pp.revenue3p, '0'::double precision), NULLIF(cp.revenue3p, '0'::double precision))) * '100'::double precision), '0'::double precision))::numeric), cp.unitsales, (COALESCE((((COALESCE(cp.unitsales, '0'::numeric) - COALESCE(pp.ppunitsales, '0'::numeric)) / COALESCE(NULLIF(pp.ppunitsales, '0'::numeric), NULLIF(cp.unitsales, '0'::numeric))) * '100'::numeric), '0'::numeric)), cp.unitsales1p, (COALESCE((((COALESCE(cp.unitsales1p, '0'::numeric) - COALESCE(pp.ppunitsales1p, '0'::numeric)) / COALESCE(NULLIF(pp.ppunitsales1p, '0'::numeric), NULLIF(cp.unitsales1p, '0'::numeric))) * '100'::numeric), '0'::numeric)), cp.unitsales3p, (COALESCE((((COALESCE(cp.unitsales3p, '0'::numeric) - COALESCE(pp.ppunitsales3p, '0'::numeric)) / COALESCE(NULLIF(pp.ppunitsales3p, '0'::numeric), NULLIF(cp.unitsales3p, '0'::numeric))) * '100'::numeric), '0'::numeric)), (count(*) OVER (?)), cp.issharedbsr, cp.sharedbsrparentasin, cp.splitrevenuedate, cp.sharedbsrvariantchildcount
  • Sort Key: ((cp.revenue / '100'::double precision)) DESC
  • Sort Method: top-N heapsort Memory: 37kB
  • Buffers: shared hit=3,785,222 read=170,347
124. 34.389 150,452.456 ↓ 6.7 6,376 1

WindowAgg (cost=131.69..285.32 rows=945 width=657) (actual time=150,421.742..150,452.456 rows=6,376 loops=1)

  • Output: cp.id, cp.name, cp.""imageUrl"", cp.""productUrl"", cp.country, cp.asin, cp.brand, cp.share, (COALESCE((((((COALESCE(cp.share, '0'::double precision) - COALESCE(pp.share, '0'::double precision)))::numeric)::double precision / COALESCE(NULLIF(pp.share, '0'::double precision), NULLIF(cp.share, '0'::double precision))) * '100'::double precision), '0'::double precision))::numeric, (cp.revenue / '100'::double precision), (COALESCE((((((COALESCE(cp.revenue, '0'::double precision) - COALESCE(pp.revenue, '0'::double precision)))::numeric)::double precision / COALESCE(NULLIF(pp.revenue, '0'::double precision), NULLIF(cp.revenue, '0'::double precision))) * '100'::double precision), '0'::double precision))::numeric, (cp.revenue1p / '100'::double precision), (COALESCE((((((COALESCE(cp.revenue1p, '0'::double precision) - COALESCE(pp.revenue1p, '0'::double precision)))::numeric)::double precision / COALESCE(NULLIF(pp.revenue1p, '0'::double precision), NULLIF(cp.revenue1p, '0'::double precision))) * '100'::double precision), '0'::double precision))::numeric, (cp.revenue3p / '100'::double precision), (COALESCE((((((COALESCE(cp.revenue3p, '0'::double precision) - COALESCE(pp.revenue3p, '0'::double precision)))::numeric)::double precision / COALESCE(NULLIF(pp.revenue3p, '0'::double precision), NULLIF(cp.revenue3p, '0'::double precision))) * '100'::double precision), '0'::double precision))::numeric, cp.unitsales, COALESCE((((COALESCE(cp.unitsales, '0'::numeric) - COALESCE(pp.ppunitsales, '0'::numeric)) / COALESCE(NULLIF(pp.ppunitsales, '0'::numeric), NULLIF(cp.unitsales, '0'::numeric))) * '100'::numeric), '0'::numeric), cp.unitsales1p, COALESCE((((COALESCE(cp.unitsales1p, '0'::numeric) - COALESCE(pp.ppunitsales1p, '0'::numeric)) / COALESCE(NULLIF(pp.ppunitsales1p, '0'::numeric), NULLIF(cp.unitsales1p, '0'::numeric))) * '100'::numeric), '0'::numeric), cp.unitsales3p, COALESCE((((COALESCE(cp.unitsales3p, '0'::numeric) - COALESCE(pp.ppunitsales3p, '0'::numeric)) / COALESCE(NULLIF(pp.ppunitsales3p, '0'::numeric), NULLIF(cp.unitsales3p, '0'::numeric))) * '100'::numeric), '0'::numeric), count(*) OVER (?), cp.issharedbsr, cp.sharedbsrparentasin, cp.splitrevenuedate, cp.sharedbsrvariantchildcount
  • Buffers: shared hit=3,785,219 read=170,347
125. 2.271 150,418.067 ↓ 6.7 6,376 1

Merge Left Join (cost=131.69..155.39 rows=945 width=553) (actual time=150,415.290..150,418.067 rows=6,376 loops=1)

  • Output: cp.id, cp.name, cp.""imageUrl"", cp.""productUrl"", cp.country, cp.asin, cp.brand, cp.share, pp.share, cp.revenue, pp.revenue, cp.revenue1p, pp.revenue1p, cp.revenue3p, pp.revenue3p, cp.unitsales, pp.ppunitsales, cp.unitsales1p, pp.ppunitsales1p, cp.unitsales3p, pp.ppunitsales3p, cp.issharedbsr, cp.sharedbsrparentasin, cp.splitrevenuedate, cp.sharedbsrvariantchildcount
  • Merge Cond: (((cp.segment_id)::text = (pp.segment_id)::text) AND ((cp.id)::text = (pp.id)::text) AND ((cp.name)::text = (pp.name)::text) AND (cp.sharedbsrparentasin = pp.sharedbsrparentasin))
  • Buffers: shared hit=3,785,219 read=170,347
126. 28.663 150,415.796 ↓ 6.7 6,376 1

Sort (cost=65.70..68.07 rows=945 width=457) (actual time=150,415.286..150,415.796 rows=6,376 loops=1)

  • Output: cp.id, cp.name, cp.""imageUrl"", cp.""productUrl"", cp.country, cp.asin, cp.brand, cp.share, cp.revenue, cp.revenue1p, cp.revenue3p, cp.unitsales, cp.unitsales1p, cp.unitsales3p, cp.issharedbsr, cp.sharedbsrparentasin, cp.splitrevenuedate, cp.sharedbsrvariantchildcount, cp.segment_id
  • Sort Key: cp.segment_id, cp.id, cp.name, cp.sharedbsrparentasin
  • Sort Method: quicksort Memory: 2,937kB
  • Buffers: shared hit=3,785,219 read=170,347
127. 150,387.133 150,387.133 ↓ 6.7 6,376 1

CTE Scan on current_period cp (cost=0.00..19.00 rows=945 width=457) (actual time=148,664.615..150,387.133 rows=6,376 loops=1)

  • Output: cp.id, cp.name, cp.""imageUrl"", cp.""productUrl"", cp.country, cp.asin, cp.brand, cp.share, cp.revenue, cp.revenue1p, cp.revenue3p, cp.unitsales, cp.unitsales1p, cp.unitsales3p, cp.issharedbsr, cp.sharedbsrparentasin, cp.splitrevenuedate, cp.sharedbsrvariantchildcount, cp.segment_id
  • Filter: (cp.asin IS NOT NULL)
  • Buffers: shared hit=3,785,219 read=170,347
128. 0.000 0.000 ↓ 0.0 0

Sort (cost=65.99..68.36 rows=950 width=256) (never executed)

  • Output: pp.share, pp.revenue, pp.revenue1p, pp.revenue3p, pp.ppunitsales, pp.ppunitsales1p, pp.ppunitsales3p, pp.segment_id, pp.id, pp.name, pp.sharedbsrparentasin
  • Sort Key: pp.segment_id, pp.id, pp.name, pp.sharedbsrparentasin
129. 0.000 0.000 ↓ 0.0 0

CTE Scan on previous_period pp (cost=0.00..19.00 rows=950 width=256) (never executed)

  • Output: pp.share, pp.revenue, pp.revenue1p, pp.revenue3p, pp.ppunitsales, pp.ppunitsales1p, pp.ppunitsales3p, pp.segment_id, pp.id, pp.name, pp.sharedbsrparentasin
Planning time : 7.764 ms
Execution time : 150,504.669 ms