explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oo3L

Settings
# exclusive inclusive rows x rows loops node
1. 477.051 54,488.431 ↓ 30.4 651,520 1

Merge Left Join (cost=134,271,240.49..134,279,122.35 rows=21,442 width=580) (actual time=45,264.966..54,488.431 rows=651,520 loops=1)

  • Output: ((to_timestamp(((products.recordeddate / 1000))::double precision))::date), products.brand, (sum(((""*SELECT* 1"".price / '100'::double precision) * (""*SELECT* 1"".unitssold)::double precision))), (sum(""*SELECT* 1"".unitssold)), (sum(CASE WHEN (products.is1p OR products.is3p) THEN 1 ELSE 0 END)), ((percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY ""*SELECT* 1"".price) / '100'::double precision)), (percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY products.reviews))
  • Inner Unique: true
  • Merge Cond: ((((to_timestamp(((products.recordeddate / 1000))::double precision))::date) = ((to_timestamp((("*SELECT* 1".recordeddate / 1000))::double precision))::date)) AND ((products.brand)::text = ("*SELECT* 1".brand)::text))
  • Buffers: shared hit=2,386,184 read=4, temp read=351,903 written=270,200
2.          

CTE products

3. 1,263.035 12,876.852 ↓ 33.3 7,149,352 1

Nested Loop (cost=47,964.32..134,016,284.91 rows=214,420 width=100) (actual time=39.154..12,876.852 rows=7,149,352 loops=1)

  • Output: segment_asin_buybox_keepa_new.recordeddate, sp.brand, segment_asin_buybox_keepa_new.unitssold, segment_asin_buybox_keepa_new.price, segment_asin_buybox_keepa_new.reviews, segment_asin_buybox_keepa_new.id, segment_asin_buybox_keepa_new.is1p, segment_asin_buybox_keepa_new.is3p, segment_asin_buybox_keepa_new.category, sp.scraped_parent_asin, sp.is_shared_bsr
  • Buffers: shared hit=2,386,184 read=4
4. 14.291 57.109 ↓ 156.8 9,411 1

Hash Join (cost=27,771.02..54,837.80 rows=60 width=66) (actual time=38.479..57.109 rows=9,411 loops=1)

  • Output: segment_product.id, sp.brand, sp.scraped_parent_asin, sp.is_shared_bsr, sp.id
  • Inner Unique: true
  • Hash Cond: ((segment_product.id)::text = (sp.id)::text)
  • Buffers: shared hit=7,590
5. 15.255 20.665 ↓ 1.2 9,411 1

Bitmap Heap Scan on public.segment_product (cost=313.32..27,359.48 rows=7,857 width=14) (actual time=16.302..20.665 rows=9,411 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 = '699'::text)
  • Heap Blocks: exact=3,429
  • Buffers: shared hit=3,795
6. 5.410 5.410 ↓ 13.4 105,185 1

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..311.36 rows=7,857 width=0) (actual time=5.410..5.410 rows=105,185 loops=1)

  • Index Cond: ((segment_product.segment_id)::text = '699'::text)
  • Buffers: shared hit=366
7. 2.499 22.153 ↓ 1.2 9,411 1

Hash (cost=27,359.48..27,359.48 rows=7,857 width=52) (actual time=22.153..22.153 rows=9,411 loops=1)

  • Output: sp.brand, sp.scraped_parent_asin, sp.is_shared_bsr, sp.id
  • Buckets: 16,384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 763kB
  • Buffers: shared hit=3,795
8. 14.937 19.654 ↓ 1.2 9,411 1

Bitmap Heap Scan on public.segment_product sp (cost=313.32..27,359.48 rows=7,857 width=52) (actual time=15.740..19.654 rows=9,411 loops=1)

  • Output: sp.brand, sp.scraped_parent_asin, sp.is_shared_bsr, sp.id
  • Recheck Cond: ((sp.segment_id)::text = '699'::text)
  • Heap Blocks: exact=3,429
  • Buffers: shared hit=3,795
9. 4.717 4.717 ↓ 13.4 105,185 1

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..311.36 rows=7,857 width=0) (actual time=4.717..4.717 rows=105,185 loops=1)

  • Index Cond: ((sp.segment_id)::text = '699'::text)
  • Buffers: shared hit=366
10. 7,142.949 11,556.708 ↑ 791.8 760 9,411

Bitmap Heap Scan on public.segment_asin_buybox_keepa_new (cost=20,193.30..2,226,673.14 rows=601,765 width=62) (actual time=0.633..1.228 rows=760 loops=9,411)

  • 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
  • Recheck Cond: ((segment_asin_buybox_keepa_new.id)::text = (segment_product.id)::text)
  • Heap Blocks: exact=1,743,786
  • Buffers: shared hit=2,378,594 read=4
11. 4,413.759 4,413.759 ↑ 61.2 9,838 9,411

Bitmap Index Scan on segment_asin_buybox_keepa_new_index_id (cost=0.00..20,042.86 rows=601,765 width=0) (actual time=0.469..0.469 rows=9,838 loops=9,411)

  • Index Cond: ((segment_asin_buybox_keepa_new.id)::text = (segment_product.id)::text)
  • Buffers: shared hit=634,808 read=4
12. 2,085.967 32,876.698 ↓ 30.4 651,520 1

GroupAggregate (cost=126,562.58..129,725.27 rows=21,442 width=532) (actual time=28,850.738..32,876.698 rows=651,520 loops=1)

  • Output: ((to_timestamp(((products.recordeddate / 1000))::double precision))::date), products.brand, sum(CASE WHEN (products.is1p OR products.is3p) THEN 1 ELSE 0 END), percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY products.reviews)
  • Group Key: ((to_timestamp(((products.recordeddate / 1000))::double precision))::date), products.brand
  • Buffers: shared hit=2,386,184 read=4, temp read=78,832 written=161,047
13. 13,169.294 30,790.731 ↓ 33.3 7,149,352 1

Sort (cost=126,562.58..127,098.63 rows=214,420 width=526) (actual time=28,850.719..30,790.731 rows=7,149,352 loops=1)

  • Output: ((to_timestamp(((products.recordeddate / 1000))::double precision))::date), products.brand, products.is1p, products.is3p, products.reviews
  • Sort Key: ((to_timestamp(((products.recordeddate / 1000))::double precision))::date), products.brand
  • Sort Method: external merge Disk: 231,672kB
  • Buffers: shared hit=2,386,184 read=4, temp read=78,832 written=161,047
14. 17,621.437 17,621.437 ↓ 33.3 7,149,352 1

CTE Scan on products (cost=0.00..6,432.60 rows=214,420 width=526) (actual time=39.159..17,621.437 rows=7,149,352 loops=1)

  • Output: (to_timestamp(((products.recordeddate / 1000))::double precision))::date, products.brand, products.is1p, products.is3p, products.reviews
  • Buffers: shared hit=2,386,184 read=4, temp written=82,049
15. 2,770.064 21,134.682 ↓ 40.4 651,520 1

GroupAggregate (cost=128,393.01..132,462.41 rows=16,116 width=568) (actual time=16,414.222..21,134.682 rows=651,520 loops=1)

  • Output: ((to_timestamp(((""*SELECT* 1"".recordeddate / 1000))::double precision))::date), ""*SELECT* 1"".brand, sum(((""*SELECT* 1"".price / '100'::double precision) * (""*SELECT* 1"".unitssold)::double precision)), (percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY ""*SELECT* 1"".price) / '100'::double precision), sum(""*SELECT* 1"".unitssold)
  • Group Key: ((to_timestamp(((""*SELECT* 1"".recordeddate / 1000))::double precision))::date), ""*SELECT* 1"".brand
  • Buffers: temp read=273,071 written=109,153
16. 12,856.204 18,364.618 ↓ 43.7 7,046,899 1

Sort (cost=128,393.01..128,795.92 rows=161,165 width=536) (actual time=16,414.193..18,364.618 rows=7,046,899 loops=1)

  • Output: ((to_timestamp(((""*SELECT* 1"".recordeddate / 1000))::double precision))::date), ""*SELECT* 1"".brand, ""*SELECT* 1"".price, ""*SELECT* 1"".unitssold
  • Sort Key: ((to_timestamp(((""*SELECT* 1"".recordeddate / 1000))::double precision))::date), ""*SELECT* 1"".brand
  • Sort Method: external merge Disk: 313,688kB
  • Buffers: temp read=273,071 written=109,153
17. 1,328.767 5,508.414 ↓ 43.7 7,046,899 1

Result (cost=0.00..37,327.70 rows=161,165 width=536) (actual time=0.023..5,508.414 rows=7,046,899 loops=1)

  • Output: (to_timestamp(((""*SELECT* 1"".recordeddate / 1000))::double precision))::date, ""*SELECT* 1"".brand, ""*SELECT* 1"".price, ""*SELECT* 1"".unitssold
  • Buffers: temp read=164,959 written=860
18. 521.739 4,179.647 ↓ 43.7 7,046,899 1

Append (cost=0.00..34,104.40 rows=161,165 width=540) (actual time=0.019..4,179.647 rows=7,046,899 loops=1)

  • Buffers: temp read=164,959 written=860
19. 727.051 2,236.961 ↓ 49.2 7,033,210 1

Subquery Scan on *SELECT* 1 (cost=0.00..7,326.07 rows=142,952 width=540) (actual time=0.019..2,236.961 rows=7,033,210 loops=1)

  • Output: ""*SELECT* 1"".recordeddate, ""*SELECT* 1"".brand, ""*SELECT* 1"".price, ""*SELECT* 1"".unitssold
  • Buffers: temp read=82,051 written=1
20. 1,509.910 1,509.910 ↓ 49.2 7,033,210 1

CTE Scan on products products_1 (cost=0.00..5,896.55 rows=142,952 width=540) (actual time=0.018..1,509.910 rows=7,033,210 loops=1)

  • Output: products_1.recordeddate, products_1.brand, products_1.unitssold, products_1.price
  • Filter: ((NOT products_1.is_shared_bsr) OR (products_1.is_shared_bsr IS NULL) OR (products_1.scraped_parent_asin IS NULL) OR (((products_1.category)::text <> ALL ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) AND (products_1.recordeddate < '1590019200000'::bigint)))
  • Rows Removed by Filter: 116,142
  • Buffers: temp read=82,051 written=1
21. 1.620 1,420.947 ↑ 1.3 13,689 1

Subquery Scan on *SELECT* 2 (cost=25,768.31..26,778.33 rows=18,213 width=540) (actual time=1,345.749..1,420.947 rows=13,689 loops=1)

  • Output: ""*SELECT* 2"".recordeddate, ""*SELECT* 2"".brand, ""*SELECT* 2"".price, ""*SELECT* 2"".unitssold
  • Buffers: temp read=82,908 written=859
22. 49.425 1,419.327 ↑ 1.3 13,689 1

GroupAggregate (cost=25,768.31..26,596.20 rows=18,213 width=1,056) (actual time=1,345.748..1,419.327 rows=13,689 loops=1)

  • Output: products_2.recordeddate, products_2.brand, percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY products_2.unitssold DESC), percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY products_2.price DESC), products_2.scraped_parent_asin
  • Group Key: products_2.scraped_parent_asin, products_2.recordeddate, products_2.brand
  • Buffers: temp read=82,908 written=859
23. 277.672 1,369.902 ↓ 3.1 116,142 1

Sort (cost=25,768.31..25,860.76 rows=36,980 width=1,056) (actual time=1,345.720..1,369.902 rows=116,142 loops=1)

  • Output: products_2.recordeddate, products_2.brand, products_2.scraped_parent_asin, products_2.unitssold, products_2.price
  • Sort Key: products_2.scraped_parent_asin, products_2.recordeddate, products_2.brand
  • Sort Method: external merge Disk: 6,856kB
  • Buffers: temp read=82,908 written=859
24. 1,092.230 1,092.230 ↓ 3.1 116,142 1

CTE Scan on products products_2 (cost=0.00..5,896.55 rows=36,980 width=1,056) (actual time=0.430..1,092.230 rows=116,142 loops=1)

  • Output: products_2.recordeddate, products_2.brand, products_2.scraped_parent_asin, products_2.unitssold, products_2.price
  • Filter: (products_2.is_shared_bsr AND (products_2.scraped_parent_asin IS NOT NULL) AND (((products_2.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) OR (products_2.recordeddate >= '1590019200000'::bigint)))
  • Rows Removed by Filter: 7,033,210
  • Buffers: temp read=82,051
Planning time : 3.001 ms
Execution time : 54,930.325 ms