explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xquUm

Settings
# exclusive inclusive rows x rows loops node
1. 19.905 111,976.356 ↓ 12.3 860 1

Merge Left Join (cost=6,425,574.44..6,523,279.98 rows=70 width=1,184) (actual time=111,809.388..111,976.356 rows=860 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=219,783 read=64,021, local hit=9, temp read=10,056 written=10,085
2. 0.931 1.099 ↓ 12.3 860 1

Sort (cost=12.85..13.02 rows=70 width=1,064) (actual time=0.779..1.099 rows=860 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: quicksort Memory: 96kB
  • Buffers: local hit=9
3. 0.168 0.168 ↓ 12.3 860 1

Seq Scan on pg_temp_56.segment_brand_day_temp_733_1593423701 t (cost=0.00..10.70 rows=70 width=1,064) (actual time=0.016..0.168 rows=860 loops=1)

  • Output: t.segment_id, t.day, t.brand, t.asin_count, t.price_average, t.review_count
  • Buffers: local hit=9
4. 740.565 111,955.352 ↓ 2.5 100,180 1

GroupAggregate (cost=6,425,561.60..6,522,665.91 rows=40,000 width=160) (actual time=110,471.026..111,955.352 rows=100,180 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=219,783 read=64,021, temp read=10,056 written=10,085
5. 2,356.202 111,214.787 ↑ 2.1 1,036,691 1

Sort (cost=6,425,561.60..6,430,917.39 rows=2,142,318 width=58) (actual time=110,470.996..111,214.787 rows=1,036,691 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: 50,408kB
  • Buffers: shared hit=219,783 read=64,021, temp read=10,056 written=10,085
6. 159.301 108,858.585 ↑ 2.1 1,036,831 1

Subquery Scan on combined_results (cost=35,342.82..6,039,190.92 rows=2,142,318 width=58) (actual time=8.259..108,858.585 rows=1,036,831 loops=1)

  • Output: combined_results.recordeddate, combined_results.brand, combined_results.unitssold, combined_results.price, combined_results.is1p, combined_results.is3p
  • Buffers: shared hit=219,783 read=64,021
7. 105.775 108,699.284 ↑ 2.1 1,036,831 1

Append (cost=35,342.82..6,017,767.74 rows=2,142,318 width=58) (actual time=8.258..108,699.284 rows=1,036,831 loops=1)

  • Buffers: shared hit=219,783 read=64,021
8. 4,726.027 108,211.057 ↑ 2.1 1,035,427 1

Gather (cost=35,342.82..3,105,596.07 rows=2,134,214 width=52) (actual time=8.258..108,211.057 rows=1,035,427 loops=1)

  • Output: s.unitssold, s.price, s.is1p, s.is3p, s.recordeddate, p.brand
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=173,161 read=64,021
9. 75.996 103,485.030 ↑ 2.6 345,142 3 / 3

Nested Loop (cost=34,342.82..2,891,174.67 rows=889,256 width=52) (actual time=5.590..103,485.030 rows=345,142 loops=3)

  • Output: s.unitssold, s.price, s.is1p, s.is3p, s.recordeddate, p.brand
  • Buffers: shared hit=173,161 read=64,021
  • Worker 0: actual time=3.941..108462.457 rows=428,541 loops=1
  • Buffers: shared hit=75,209 read=22,349
  • Worker 1: actual time=5.016..97033.674 rows=415,682 loops=1
  • Buffers: shared hit=74,765 read=19,987
10. 1.425 6.832 ↓ 458.0 458 3 / 3

Hash Join (cost=12,686.25..25,074.66 rows=1 width=66) (actual time=5.108..6.832 rows=458 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=2,090
  • Worker 0: actual time=3.539..5.589 rows=568 loops=1
  • Buffers: shared hit=544
  • Worker 1: actual time=4.460..6.398 rows=552 loops=1
  • Buffers: shared hit=543
11. 1.054 1.298 ↑ 3.1 458 3 / 3

Parallel Bitmap Heap Scan on public.segment_product p (cost=78.81..12,463.50 rows=1,418 width=52) (actual time=0.968..1.298 rows=458 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 = '733'::text)
  • Heap Blocks: exact=434
  • Buffers: shared hit=522
  • Worker 0: actual time=0.029..0.422 rows=568 loops=1
  • Buffers: shared hit=21
  • Worker 1: actual time=0.028..0.392 rows=552 loops=1
  • Buffers: shared hit=20
12. 0.244 0.244 ↓ 3.5 11,801 1 / 3

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..77.96 rows=3,404 width=0) (actual time=0.732..0.732 rows=11,801 loops=1)

  • Index Cond: ((p.segment_id)::text = '733'::text)
  • Buffers: shared hit=47
13. 0.344 4.109 ↑ 2.5 1,374 3 / 3

Hash (cost=12,564.90..12,564.90 rows=3,403 width=14) (actual time=4.108..4.109 rows=1,374 loops=3)

  • Output: p_1.id
  • Buckets: 4,096 Batches: 1 Memory Usage: 94kB
  • Buffers: shared hit=1,568
  • Worker 0: actual time=3.479..3.479 rows=1,374 loops=1
  • Buffers: shared hit=523
  • Worker 1: actual time=4.399..4.399 rows=1,374 loops=1
  • Buffers: shared hit=523
14. 0.728 3.765 ↑ 2.5 1,374 3 / 3

HashAggregate (cost=12,496.84..12,530.87 rows=3,403 width=14) (actual time=3.534..3.765 rows=1,374 loops=3)

  • Output: p_1.id
  • Group Key: p_1.id
  • Buffers: shared hit=1,568
  • Worker 0: actual time=3.039..3.217 rows=1,374 loops=1
  • Buffers: shared hit=523
  • Worker 1: actual time=3.763..4.026 rows=1,374 loops=1
  • Buffers: shared hit=523
15. 2.110 3.037 ↑ 2.5 1,374 3 / 3

Bitmap Heap Scan on public.segment_product p_1 (cost=78.81..12,488.33 rows=3,404 width=14) (actual time=2.734..3.037 rows=1,374 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 = '733'::text)
  • Heap Blocks: exact=475
  • Buffers: shared hit=1,568
  • Worker 0: actual time=2.378..2.645 rows=1,374 loops=1
  • Buffers: shared hit=523
  • Worker 1: actual time=2.912..3.214 rows=1,374 loops=1
  • Buffers: shared hit=523
16. 0.927 0.927 ↓ 3.5 11,801 3 / 3

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..77.96 rows=3,404 width=0) (actual time=0.927..0.927 rows=11,801 loops=3)

  • Index Cond: ((p_1.segment_id)::text = '733'::text)
  • Buffers: shared hit=143
  • Worker 0: actual time=0.785..0.785 rows=11,801 loops=1
  • Buffers: shared hit=48
  • Worker 1: actual time=1.082..1.082 rows=11,801 loops=1
  • Buffers: shared hit=48
17. 70,900.690 103,402.202 ↑ 785.5 754 1,374 / 3

Bitmap Heap Scan on public.segment_asin_buybox_keepa_new s (cost=21,656.57..2,860,177.63 rows=592,238 width=58) (actual time=72.107..225.769 rows=754 loops=1,374)

  • 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: 8
  • Heap Blocks: exact=30,881
  • Buffers: shared hit=171,071 read=64,021
  • Worker 0: actual time=61.366..190.782 rows=754 loops=568
  • Buffers: shared hit=74,665 read=22,349
  • Worker 1: actual time=56.398..175.606 rows=753 loops=552
  • Buffers: shared hit=74,222 read=19,987
18. 32,501.512 32,501.512 ↑ 131.4 5,907 1,374 / 3

Bitmap Index Scan on segment_asin_buybox_keepa_new_index_id (cost=0.00..21,508.51 rows=775,997 width=0) (actual time=70.964..70.964 rows=5,907 loops=1,374)

  • Index Cond: ((s.id)::text = (p.id)::text)
  • Buffers: shared hit=50,131 read=20,000
  • Worker 0: actual time=60.505..60.505 rows=5,859 loops=568
  • Buffers: shared hit=22,043 read=7,017
  • Worker 1: actual time=55.407..55.407 rows=5,911 loops=552
  • Buffers: shared hit=21,873 read=6,208
19. 0.256 382.452 ↑ 5.8 1,404 1

Subquery Scan on *SELECT* 2 (cost=2,890,383.81..2,890,829.53 rows=8,104 width=58) (actual time=371.063..382.452 rows=1,404 loops=1)

  • Output: ""*SELECT* 2"".unitssold, ""*SELECT* 2"".price, ""*SELECT* 2"".is1p, ""*SELECT* 2"".is3p, ""*SELECT* 2"".recordeddate, ""*SELECT* 2"".max
  • Buffers: shared hit=46,622
20. 10.174 382.196 ↑ 5.8 1,404 1

GroupAggregate (cost=2,890,383.81..2,890,748.49 rows=8,104 width=69) (actual time=371.061..382.196 rows=1,404 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=46,622
21. 15.694 372.022 ↓ 1.4 11,349 1

Sort (cost=2,890,383.81..2,890,404.07 rows=8,104 width=63) (actual time=370.737..372.022 rows=11,349 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: quicksort Memory: 1,876kB
  • Buffers: shared hit=46,577
22. 3.418 356.328 ↓ 1.4 11,349 1

Nested Loop (cost=34,286.76..2,889,857.68 rows=8,104 width=63) (actual time=8.276..356.328 rows=11,349 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=46,577
23. 0.411 8.075 ↓ 291.0 291 1

Hash Join (cost=12,685.40..25,094.99 rows=1 width=65) (actual time=7.023..8.075 rows=291 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=1,044
24. 2.706 3.746 ↓ 10.8 291 1

Bitmap Heap Scan on public.segment_product p_2 (cost=77.97..12,487.48 rows=27 width=51) (actual time=3.069..3.746 rows=291 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 = '733'::text)
  • Filter: (p_2.is_shared_bsr AND (p_2.scraped_parent_asin IS NOT NULL))
  • Rows Removed by Filter: 1,083
  • Heap Blocks: exact=475
  • Buffers: shared hit=522
25. 1.040 1.040 ↓ 3.5 11,801 1

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..77.96 rows=3,404 width=0) (actual time=1.040..1.040 rows=11,801 loops=1)

  • Index Cond: ((p_2.segment_id)::text = '733'::text)
  • Buffers: shared hit=47
26. 0.416 3.918 ↑ 2.5 1,374 1

Hash (cost=12,564.90..12,564.90 rows=3,403 width=14) (actual time=3.918..3.918 rows=1,374 loops=1)

  • Output: p_3.id
  • Buckets: 4,096 Batches: 1 Memory Usage: 94kB
  • Buffers: shared hit=522
27. 0.828 3.502 ↑ 2.5 1,374 1

HashAggregate (cost=12,496.84..12,530.87 rows=3,403 width=14) (actual time=3.220..3.502 rows=1,374 loops=1)

  • Output: p_3.id
  • Group Key: p_3.id
  • Buffers: shared hit=522
28. 1.964 2.674 ↑ 2.5 1,374 1

Bitmap Heap Scan on public.segment_product p_3 (cost=78.81..12,488.33 rows=3,404 width=14) (actual time=2.350..2.674 rows=1,374 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 = '733'::text)
  • Heap Blocks: exact=475
  • Buffers: shared hit=522
29. 0.710 0.710 ↓ 3.5 11,801 1

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..77.96 rows=3,404 width=0) (actual time=0.710..0.710 rows=11,801 loops=1)

  • Index Cond: ((p_3.segment_id)::text = '733'::text)
  • Buffers: shared hit=47
30. 238.038 344.835 ↑ 9,518.5 39 291

Bitmap Heap Scan on public.segment_asin_buybox_keepa_new s_1 (cost=21,601.36..2,860,122.42 rows=371,222 width=40) (actual time=1.167..1.185 rows=39 loops=291)

  • 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: 718
  • Heap Blocks: exact=32,470
  • Buffers: shared hit=45,533
31. 106.797 106.797 ↑ 135.3 5,734 291

Bitmap Index Scan on segment_asin_buybox_keepa_new_index_id (cost=0.00..21,508.56 rows=775,997 width=0) (actual time=0.367..0.367 rows=5,734 loops=291)

  • Index Cond: ((s_1.id)::text = (p_3.id)::text)
  • Buffers: shared hit=13,063
Planning time : 7.139 ms
Execution time : 111,992.468 ms