explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OCnX

Settings
# exclusive inclusive rows x rows loops node
1. 15.813 5,272.090 ↓ 14.3 3,293 1

Merge Left Join (cost=5,943,093.89..5,987,571.97 rows=231 width=1,184) (actual time=5,149.763..5,272.090 rows=3,293 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=283,939, local hit=33, temp read=10,064 written=10,093
2. 7.049 8.129 ↓ 14.3 3,293 1

Sort (cost=44.38..44.96 rows=231 width=1,064) (actual time=7.500..8.129 rows=3,293 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: 370kB
  • Buffers: local hit=33
3. 1.080 1.080 ↓ 14.3 3,293 1

Seq Scan on pg_temp_113.segment_brand_day_temp_733_1593423701 t (cost=0.00..35.31 rows=231 width=1,064) (actual time=0.018..1.080 rows=3,293 loops=1)

  • Output: t.segment_id, t.day, t.brand, t.asin_count, t.price_average, t.review_count
  • Buffers: local hit=33
4. 518.357 5,248.148 ↓ 2.5 100,199 1

GroupAggregate (cost=5,943,049.51..5,986,923.55 rows=40,000 width=160) (actual time=4,214.914..5,248.148 rows=100,199 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=283,939, temp read=10,064 written=10,093
5. 2,165.253 4,729.791 ↓ 1.1 1,036,828 1

Sort (cost=5,943,049.51..5,945,448.07 rows=959,423 width=58) (actual time=4,214.903..4,729.791 rows=1,036,828 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,392kB
  • Buffers: shared hit=283,939, temp read=10,064 written=10,093
6. 156.807 2,564.538 ↓ 1.1 1,036,831 1

Subquery Scan on combined_results (cost=27,786.93..5,775,573.17 rows=959,423 width=58) (actual time=7.934..2,564.538 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=283,939
7. 103.906 2,407.731 ↓ 1.1 1,036,831 1

Append (cost=27,786.93..5,765,978.94 rows=959,423 width=58) (actual time=7.933..2,407.731 rows=1,036,831 loops=1)

  • Buffers: shared hit=283,939
8. 191.960 1,920.694 ↓ 1.1 1,035,427 1

Nested Loop (cost=27,786.93..2,878,675.72 rows=955,820 width=52) (actual time=7.932..1,920.694 rows=1,035,427 loops=1)

  • Output: s.unitssold, s.price, s.is1p, s.is3p, s.recordeddate, p.brand
  • Buffers: shared hit=236,853
9. 3.142 11.234 ↓ 1,374.0 1,374 1

Hash Join (cost=5,931.25..11,684.68 rows=1 width=66) (actual time=7.407..11.234 rows=1,374 loops=1)

  • 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=1,044
10. 2.763 3.849 ↑ 1.1 1,374 1

Bitmap Heap Scan on public.segment_product p (cost=64.24..5,813.67 rows=1,524 width=52) (actual time=3.145..3.849 rows=1,374 loops=1)

  • 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=475
  • Buffers: shared hit=522
11. 1.086 1.086 ↓ 7.7 11,801 1

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..63.86 rows=1,524 width=0) (actual time=1.086..1.086 rows=11,801 loops=1)

  • Index Cond: ((p.segment_id)::text = '733'::text)
  • Buffers: shared hit=47
12. 0.386 4.243 ↑ 1.1 1,374 1

Hash (cost=5,847.96..5,847.96 rows=1,524 width=14) (actual time=4.243..4.243 rows=1,374 loops=1)

  • Output: p_1.id
  • Buckets: 2,048 Batches: 1 Memory Usage: 78kB
  • Buffers: shared hit=522
13. 0.848 3.857 ↑ 1.1 1,374 1

HashAggregate (cost=5,817.48..5,832.72 rows=1,524 width=14) (actual time=3.599..3.857 rows=1,374 loops=1)

  • Output: p_1.id
  • Group Key: p_1.id
  • Buffers: shared hit=522
14. 1.991 3.009 ↑ 1.1 1,374 1

Bitmap Heap Scan on public.segment_product p_1 (cost=64.24..5,813.67 rows=1,524 width=14) (actual time=2.673..3.009 rows=1,374 loops=1)

  • 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=522
15. 1.018 1.018 ↓ 7.7 11,801 1

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..63.86 rows=1,524 width=0) (actual time=1.018..1.018 rows=11,801 loops=1)

  • Index Cond: ((p_1.segment_id)::text = '733'::text)
  • Buffers: shared hit=47
16. 1,143.168 1,717.500 ↑ 785.5 754 1,374

Bitmap Heap Scan on public.segment_asin_buybox_keepa_new s (cost=21,855.67..2,861,068.65 rows=592,238 width=58) (actual time=0.446..1.250 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=165,527
  • Buffers: shared hit=235,809
17. 574.332 574.332 ↑ 130.8 5,931 1,374

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

  • Index Cond: ((s.id)::text = (p.id)::text)
  • Buffers: shared hit=70,282
18. 0.216 383.131 ↑ 2.6 1,404 1

Subquery Scan on *SELECT* 2 (cost=2,877,546.86..2,877,745.02 rows=3,603 width=58) (actual time=374.614..383.131 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=47,086
19. 7.555 382.915 ↑ 2.6 1,404 1

GroupAggregate (cost=2,877,546.86..2,877,708.99 rows=3,603 width=69) (actual time=374.613..382.915 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=47,086
20. 14.974 375.360 ↓ 3.1 11,349 1

Sort (cost=2,877,546.86..2,877,555.86 rows=3,603 width=63) (actual time=374.570..375.360 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=47,086
21. 3.660 360.386 ↓ 3.1 11,349 1

Nested Loop (cost=27,731.29..2,877,334.01 rows=3,603 width=63) (actual time=6.690..360.386 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=47,086
22. 0.418 6.944 ↓ 291.0 291 1

Hash Join (cost=5,930.87..11,680.34 rows=1 width=65) (actual time=5.820..6.944 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
23. 2.687 3.691 ↓ 24.2 291 1

Bitmap Heap Scan on public.segment_product p_2 (cost=63.86..5,813.29 rows=12 width=51) (actual time=2.958..3.691 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
24. 1.004 1.004 ↓ 7.7 11,801 1

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..63.86 rows=1,524 width=0) (actual time=1.004..1.004 rows=11,801 loops=1)

  • Index Cond: ((p_2.segment_id)::text = '733'::text)
  • Buffers: shared hit=47
25. 0.227 2.835 ↑ 1.1 1,374 1

Hash (cost=5,847.96..5,847.96 rows=1,524 width=14) (actual time=2.835..2.835 rows=1,374 loops=1)

  • Output: p_3.id
  • Buckets: 2,048 Batches: 1 Memory Usage: 78kB
  • Buffers: shared hit=522
26. 0.487 2.608 ↑ 1.1 1,374 1

HashAggregate (cost=5,817.48..5,832.72 rows=1,524 width=14) (actual time=2.472..2.608 rows=1,374 loops=1)

  • Output: p_3.id
  • Group Key: p_3.id
  • Buffers: shared hit=522
27. 1.498 2.121 ↑ 1.1 1,374 1

Bitmap Heap Scan on public.segment_product p_3 (cost=64.24..5,813.67 rows=1,524 width=14) (actual time=1.900..2.121 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
28. 0.623 0.623 ↓ 7.7 11,801 1

Bitmap Index Scan on segment_product_index_segment_id (cost=0.00..63.86 rows=1,524 width=0) (actual time=0.623..0.623 rows=11,801 loops=1)

  • Index Cond: ((p_3.segment_id)::text = '733'::text)
  • Buffers: shared hit=47
29. 236.874 349.782 ↑ 9,518.5 39 291

Bitmap Heap Scan on public.segment_asin_buybox_keepa_new s_1 (cost=21,800.42..2,861,013.40 rows=371,222 width=40) (actual time=1.183..1.202 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,866
  • Buffers: shared hit=46,042
30. 112.908 112.908 ↑ 133.5 5,814 291

Bitmap Index Scan on segment_asin_buybox_keepa_new_index_id (cost=0.00..21,707.61 rows=775,997 width=0) (actual time=0.388..0.388 rows=5,814 loops=291)

  • Index Cond: ((s_1.id)::text = (p_3.id)::text)
  • Buffers: shared hit=13,176
Planning time : 4.894 ms
Execution time : 5,280.698 ms