explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WYNv

Settings
# exclusive inclusive rows x rows loops node
1. 136.520 100,203.821 ↓ 14.7 51,442 1

Merge Left Join (cost=99,052,963.25..101,346,962.59 rows=3,500 width=1,184) (actual time=99,603.947..100,203.821 rows=51,442 loops=1)

  • Output: t.segment_id, t.day, t.brand, t.asin_count, t.price_average, t.review_count, (sum(combined_results.unitssold)), (sum(((combined_results.unitssold)::double precision * (combined_results.price / '100'::double precision)))), (sum(CASE WHEN combined_results.is1p THEN ((combined_results.unitssold)::double precision * (combined_results.price / '100'::double precision)) ELSE '0'::double precision END)), (sum(CASE WHEN combined_results.is3p THEN ((combined_results.unitssold)::double precision * (combined_results.price / '100'::double precision)) ELSE '0'::double precision END)), (sum(CASE WHEN combined_results.is1p THEN combined_results.unitssold ELSE '0'::bigint END)), (sum(CASE WHEN combined_results.is3p THEN combined_results.unitssold ELSE '0'::bigint END))
  • Inner Unique: true
  • Merge Cond: ((t.day = combined_results.recordeddate) AND ((t.brand)::text = (combined_results.brand)::text))
  • Buffers: shared hit=713,370 read=193,478, local hit=500, temp read=107,611 written=107,792
2. 110.956 119.743 ↓ 14.7 51,442 1

Sort (cost=741.03..749.78 rows=3,500 width=1,064) (actual time=110.109..119.743 rows=51,442 loops=1)

  • Output: t.segment_id, t.day, t.brand, t.asin_count, t.price_average, t.review_count
  • Sort Key: t.day, t.brand
  • Sort Method: external merge Disk: 2,488kB
  • Buffers: local hit=500, temp read=311 written=312
3. 8.787 8.787 ↓ 14.7 51,442 1

Seq Scan on pg_temp_67.segment_brand_day_temp_1299_1593447120 t (cost=0.00..535.00 rows=3,500 width=1,064) (actual time=0.015..8.787 rows=51,442 loops=1)

  • Output: t.segment_id, t.day, t.brand, t.asin_count, t.price_average, t.review_count
  • Buffers: local hit=500
4. 2,455.967 99,947.558 ↓ 30.2 1,208,855 1

GroupAggregate (cost=99,052,222.22..101,345,560.31 rows=40,000 width=160) (actual time=95,110.066..99,947.558 rows=1,208,855 loops=1)

  • Output: sum(combined_results.unitssold), sum(((combined_results.unitssold)::double precision * (combined_results.price / '100'::double precision))), sum(CASE WHEN combined_results.is1p THEN ((combined_results.unitssold)::double precision * (combined_results.price / '100'::double precision)) ELSE '0'::double precision END), sum(CASE WHEN combined_results.is3p THEN ((combined_results.unitssold)::double precision * (combined_results.price / '100'::double precision)) ELSE '0'::double precision END), sum(CASE WHEN combined_results.is1p THEN combined_results.unitssold ELSE '0'::bigint END), sum(CASE WHEN combined_results.is3p THEN combined_results.unitssold ELSE '0'::bigint END), combined_results.recordeddate, combined_results.brand
  • Group Key: combined_results.recordeddate, combined_results.brand
  • Buffers: shared hit=713,370 read=193,478, temp read=107,300 written=107,480
5. 15,174.689 97,491.591 ↑ 7.9 6,414,207 1

Sort (cost=99,052,222.22..99,179,591.00 rows=50,947,513 width=58) (actual time=95,110.042..97,491.591 rows=6,414,207 loops=1)

  • Output: combined_results.recordeddate, combined_results.brand, combined_results.unitssold, combined_results.price, combined_results.is1p, combined_results.is3p
  • Sort Key: combined_results.recordeddate, combined_results.brand
  • Sort Method: external merge Disk: 309,568kB
  • Buffers: shared hit=713,370 read=193,478, temp read=107,300 written=107,480
6. 681.738 82,316.902 ↑ 7.9 6,414,211 1

Subquery Scan on combined_results (cost=63,995.97..86,783,777.58 rows=50,947,513 width=58) (actual time=35.408..82,316.902 rows=6,414,211 loops=1)

  • Output: combined_results.recordeddate, combined_results.brand, combined_results.unitssold, combined_results.price, combined_results.is1p, combined_results.is3p
  • Buffers: shared hit=713,370 read=193,478, temp read=1,176 written=1,181
7. 492.358 81,635.164 ↑ 7.9 6,414,211 1

Append (cost=63,995.97..86,274,302.45 rows=50,947,513 width=58) (actual time=35.407..81,635.164 rows=6,414,211 loops=1)

  • Buffers: shared hit=713,370 read=193,478, temp read=1,176 written=1,181
8. 892.444 79,050.321 ↑ 8.0 6,385,366 1

Gather (cost=63,995.97..83,415,552.62 rows=50,868,907 width=52) (actual time=35.406..79,050.321 rows=6,385,366 loops=1)

  • Output: s.unitssold, s.price, s.is1p, s.is3p, s.recordeddate, p.brand
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=442,319 read=193,478
9. 294.571 78,157.877 ↑ 10.0 2,128,455 3 / 3

Nested Loop (cost=62,995.97..78,327,661.92 rows=21,195,378 width=52) (actual time=24.583..78,157.877 rows=2,128,455 loops=3)

  • Output: s.unitssold, s.price, s.is1p, s.is3p, s.recordeddate, p.brand
  • Buffers: shared hit=442,319 read=193,478
  • Worker 0: actual time=28.264..78118.000 rows=2,157,682 loops=1
  • Buffers: shared hit=149,546 read=64,771
  • Worker 1: actual time=10.491..78783.695 rows=2,155,906 loops=1
  • Buffers: shared hit=149,982 read=64,650
10. 5.662 18.653 ↓ 83.8 2,932 3 / 3

Hash Join (cost=42,865.51..84,214.21 rows=35 width=66) (actual time=11.758..18.653 rows=2,932 loops=3)

  • Output: p.brand, p.id, p.is_shared_bsr, p.scraped_parent_asin, p_1.id
  • Inner Unique: true
  • Hash Cond: ((p.id)::text = (p_1.id)::text)
  • Buffers: shared hit=5,306
  • Worker 0: actual time=8.920..15.910 rows=2,971 loops=1
  • Buffers: shared hit=1,431
  • Worker 1: actual time=10.195..17.459 rows=2,973 loops=1
  • Buffers: shared hit=1,432
11. 2.438 3.031 ↑ 1.8 2,932 3 / 3

Parallel Bitmap Heap Scan on public.segment_product p (cost=498.38..41,833.26 rows=5,266 width=52) (actual time=1.740..3.031 rows=2,932 loops=3)

  • Output: p.segment_id, p.id, p.asin, p.country, p.brand, p.name, p.product_url, p.imageurl, p.scraped_parent_asin, p.is_shared_bsr
  • Recheck Cond: ((p.segment_id)::text = '1299'::text)
  • Heap Blocks: exact=996
  • Buffers: shared hit=1,326
  • Worker 0: actual time=0.025..1.332 rows=2,971 loops=1
  • Buffers: shared hit=104
  • Worker 1: actual time=0.022..1.370 rows=2,973 loops=1
  • Buffers: shared hit=105
12. 0.593 0.593 ↓ 2.7 34,123 1 / 3

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..495.22 rows=12,639 width=0) (actual time=1.780..1.780 rows=34,123 loops=1)

  • Index Cond: ((p.segment_id)::text = '1299'::text)
  • Buffers: shared hit=121
13. 1.718 9.960 ↑ 1.4 8,797 3 / 3

Hash (cost=42,209.39..42,209.39 rows=12,619 width=14) (actual time=9.960..9.960 rows=8,797 loops=3)

  • Output: p_1.id
  • Buckets: 16,384 Batches: 1 Memory Usage: 524kB
  • Buffers: shared hit=3,980
  • Worker 0: actual time=8.838..8.838 rows=8,797 loops=1
  • Buffers: shared hit=1,327
  • Worker 1: actual time=10.119..10.119 rows=8,797 loops=1
  • Buffers: shared hit=1,327
14. 3.424 8.242 ↑ 1.4 8,797 3 / 3

HashAggregate (cost=41,957.01..42,083.20 rows=12,619 width=14) (actual time=7.048..8.242 rows=8,797 loops=3)

  • Output: p_1.id
  • Group Key: p_1.id
  • Buffers: shared hit=3,980
  • Worker 0: actual time=6.324..7.318 rows=8,797 loops=1
  • Buffers: shared hit=1,327
  • Worker 1: actual time=6.515..8.002 rows=8,797 loops=1
  • Buffers: shared hit=1,327
15. 3.245 4.818 ↑ 1.4 8,797 3 / 3

Bitmap Heap Scan on public.segment_product p_1 (cost=498.38..41,925.42 rows=12,639 width=14) (actual time=3.661..4.818 rows=8,797 loops=3)

  • Output: p_1.segment_id, p_1.id, p_1.asin, p_1.country, p_1.brand, p_1.name, p_1.product_url, p_1.imageurl, p_1.scraped_parent_asin, p_1.is_shared_bsr
  • Recheck Cond: ((p_1.segment_id)::text = '1299'::text)
  • Heap Blocks: exact=1,205
  • Buffers: shared hit=3,980
  • Worker 0: actual time=3.215..4.190 rows=8,797 loops=1
  • Buffers: shared hit=1,327
  • Worker 1: actual time=3.237..4.372 rows=8,797 loops=1
  • Buffers: shared hit=1,327
16. 1.573 1.573 ↓ 2.7 34,123 3 / 3

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..495.22 rows=12,639 width=0) (actual time=1.573..1.573 rows=34,123 loops=3)

  • Index Cond: ((p_1.segment_id)::text = '1299'::text)
  • Buffers: shared hit=365
  • Worker 0: actual time=1.574..1.574 rows=34,123 loops=1
  • Buffers: shared hit=122
  • Worker 1: actual time=1.576..1.576 rows=34,123 loops=1
  • Buffers: shared hit=122
17. 47,439.289 77,844.653 ↑ 631.2 726 8,797 / 3

Bitmap Heap Scan on public.segment_asin_buybox_keepa_new s (cost=20,130.45..2,230,944.59 rows=458,249 width=58) (actual time=10.710..26.547 rows=726 loops=8,797)

  • Output: s.id, s.asin, s.recordeddate, s.country, s.salesrank, s.category, s.unitssold, s.reviews, s.is1p, s.is3p, s.price, s.iskeepa
  • Recheck Cond: ((s.id)::text = (p.id)::text)
  • Filter: ((NOT p.is_shared_bsr) OR (p.is_shared_bsr IS NULL) OR (p.scraped_parent_asin IS NULL) OR (((s.category)::text <> ALL ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) AND (s.recordeddate < '1590019200000'::bigint)))
  • Rows Removed by Filter: 18
  • Heap Blocks: exact=148,442
  • Buffers: shared hit=437,013 read=193,478
  • Worker 0: actual time=10.634..26.186 rows=726 loops=2,971
  • Buffers: shared hit=148,115 read=64,771
  • Worker 1: actual time=10.645..26.393 rows=725 loops=2,973
  • Buffers: shared hit=148,550 read=64,650
18. 30,405.364 30,405.364 ↑ 205.7 2,925 8,797 / 3

Bitmap Index Scan on segment_asin_buybox_keepa_new_index_id (cost=0.00..20,015.89 rows=601,765 width=0) (actual time=10.369..10.369 rows=2,925 loops=8,797)

  • Index Cond: ((s.id)::text = (p.id)::text)
  • Buffers: shared hit=96,710 read=75,636
  • Worker 0: actual time=10.261..10.261 rows=2,926 loops=2,971
  • Buffers: shared hit=32,874 read=25,284
  • Worker 1: actual time=10.288..10.288 rows=2,922 loops=2,973
  • Buffers: shared hit=32,952 read=25,273
19. 3.517 2,092.485 ↑ 2.7 28,845 1

Subquery Scan on *SELECT* 2 (cost=2,342,899.75..2,350,060.75 rows=78,606 width=58) (actual time=1,961.948..2,092.485 rows=28,845 loops=1)

  • Output: ""*SELECT* 2"".unitssold, ""*SELECT* 2"".price, ""*SELECT* 2"".is1p, ""*SELECT* 2"".is3p, ""*SELECT* 2"".recordeddate, ""*SELECT* 2"".max
  • Buffers: shared hit=271,051, temp read=1,176 written=1,181
20. 91.073 2,088.968 ↑ 2.7 28,845 1

GroupAggregate (cost=2,342,899.75..2,349,274.69 rows=78,606 width=69) (actual time=1,961.947..2,088.968 rows=28,845 loops=1)

  • Output: percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY s_1.unitssold DESC), percentile_disc('0.5'::double precision) WITHIN GROUP (ORDER BY s_1.price DESC), (count(s_1.is1p) >= count(s_1.is3p)), (count(s_1.is1p) < count(s_1.is3p)), s_1.recordeddate, max((p_2.brand)::text), p_2.scraped_parent_asin
  • Group Key: p_2.scraped_parent_asin, s_1.recordeddate
  • Buffers: shared hit=271,051, temp read=1,176 written=1,181
21. 326.277 1,997.895 ↑ 1.2 156,500 1

Sort (cost=2,342,899.75..2,343,380.03 rows=192,113 width=63) (actual time=1,961.783..1,997.895 rows=156,500 loops=1)

  • Output: s_1.recordeddate, p_2.scraped_parent_asin, s_1.unitssold, s_1.price, s_1.is1p, s_1.is3p, p_2.brand
  • Sort Key: p_2.scraped_parent_asin, s_1.recordeddate
  • Sort Method: external merge Disk: 9,408kB
  • Buffers: shared hit=271,040, temp read=1,176 written=1,181
22. 26.843 1,671.618 ↑ 1.2 156,500 1

Nested Loop (cost=62,950.82..2,318,816.30 rows=192,113 width=63) (actual time=16.101..1,671.618 rows=156,500 loops=1)

  • Output: s_1.recordeddate, p_2.scraped_parent_asin, s_1.unitssold, s_1.price, s_1.is1p, s_1.is3p, p_2.brand
  • Join Filter: ((p_2.id)::text = (s_1.id)::text)
  • Buffers: shared hit=271,040
23. 2.155 21.055 ↓ 3,866.0 3,866 1

Hash Join (cost=42,862.38..84,289.67 rows=1 width=65) (actual time=15.737..21.055 rows=3,866 loops=1)

  • Output: p_2.brand, p_2.scraped_parent_asin, p_2.id, p_3.id
  • Inner Unique: true
  • Hash Cond: ((p_2.id)::text = (p_3.id)::text)
  • Buffers: shared hit=2,652
24. 6.640 8.234 ↓ 39.1 3,866 1

Bitmap Heap Scan on public.segment_product p_2 (cost=495.25..41,922.28 rows=99 width=51) (actual time=4.998..8.234 rows=3,866 loops=1)

  • Output: p_2.segment_id, p_2.id, p_2.asin, p_2.country, p_2.brand, p_2.name, p_2.product_url, p_2.imageurl, p_2.scraped_parent_asin, p_2.is_shared_bsr
  • Recheck Cond: ((p_2.segment_id)::text = '1299'::text)
  • Filter: (p_2.is_shared_bsr AND (p_2.scraped_parent_asin IS NOT NULL))
  • Rows Removed by Filter: 4,931
  • Heap Blocks: exact=1,205
  • Buffers: shared hit=1,326
25. 1.594 1.594 ↓ 2.7 34,123 1

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..495.22 rows=12,639 width=0) (actual time=1.594..1.594 rows=34,123 loops=1)

  • Index Cond: ((p_2.segment_id)::text = '1299'::text)
  • Buffers: shared hit=121
26. 1.474 10.666 ↑ 1.4 8,797 1

Hash (cost=42,209.39..42,209.39 rows=12,619 width=14) (actual time=10.666..10.666 rows=8,797 loops=1)

  • Output: p_3.id
  • Buckets: 16,384 Batches: 1 Memory Usage: 524kB
  • Buffers: shared hit=1,326
27. 3.461 9.192 ↑ 1.4 8,797 1

HashAggregate (cost=41,957.01..42,083.20 rows=12,619 width=14) (actual time=8.177..9.192 rows=8,797 loops=1)

  • Output: p_3.id
  • Group Key: p_3.id
  • Buffers: shared hit=1,326
28. 4.174 5.731 ↑ 1.4 8,797 1

Bitmap Heap Scan on public.segment_product p_3 (cost=498.38..41,925.42 rows=12,639 width=14) (actual time=4.350..5.731 rows=8,797 loops=1)

  • Output: p_3.segment_id, p_3.id, p_3.asin, p_3.country, p_3.brand, p_3.name, p_3.product_url, p_3.imageurl, p_3.scraped_parent_asin, p_3.is_shared_bsr
  • Recheck Cond: ((p_3.segment_id)::text = '1299'::text)
  • Heap Blocks: exact=1,205
  • Buffers: shared hit=1,326
29. 1.557 1.557 ↓ 2.7 34,123 1

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..495.22 rows=12,639 width=0) (actual time=1.557..1.557 rows=34,123 loops=1)

  • Index Cond: ((p_3.segment_id)::text = '1299'::text)
  • Buffers: shared hit=121
30. 1,109.542 1,623.720 ↑ 7,248.1 40 3,866

Bitmap Heap Scan on public.segment_asin_buybox_keepa_new s_1 (cost=20,088.44..2,230,902.57 rows=289,924 width=40) (actual time=0.409..0.420 rows=40 loops=3,866)

  • Output: s_1.id, s_1.asin, s_1.recordeddate, s_1.country, s_1.salesrank, s_1.category, s_1.unitssold, s_1.reviews, s_1.is1p, s_1.is3p, s_1.price, s_1.iskeepa
  • Recheck Cond: ((s_1.id)::text = (p_3.id)::text)
  • Filter: (((s_1.category)::text = ANY ('{""Clothing, Shoes & Jewelry"",Accessories,""Sports & Outdoors"",""Beauty & Personal Care""}'::text[])) OR (s_1.recordeddate >= '1590019200000'::bigint))
  • Rows Removed by Filter: 703
  • Heap Blocks: exact=194,306
  • Buffers: shared hit=268,388
31. 514.178 514.178 ↑ 213.4 2,820 3,866

Bitmap Index Scan on segment_asin_buybox_keepa_new_index_id (cost=0.00..20,015.96 rows=601,765 width=0) (actual time=0.133..0.133 rows=2,820 loops=3,866)

  • Index Cond: ((s_1.id)::text = (p_3.id)::text)
  • Buffers: shared hit=74,082
Planning time : 6.691 ms
Execution time : 100,248.434 ms