explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FEEP : sbd_pp

Settings
# exclusive inclusive rows x rows loops node
1. 5.903 4,919.762 ↓ 0.0 0 1

Update on public.segment_brand_day (cost=5,343,554.42..5,378,593.67 rows=4,056 width=138) (actual time=4,919.762..4,919.762 rows=0 loops=1)

  • Buffers: shared hit=160,887 read=2, local read=6,970 dirtied=6,970 written=6,970, temp read=8,172 written=10,977
2.          

CTE comparison_model

3. 100.285 4,830.115 ↓ 51.8 419,836 1

Unique (cost=5,343,411.90..5,343,553.85 rows=8,111 width=541) (actual time=4,686.053..4,830.115 rows=419,836 loops=1)

  • Output: segment_brand_day_1.id, brendan_sbd_postprocessing.brand, brendan_sbd_postprocessing.revenue, segment_brand_day_1.revenue_sum, (CASE WHEN (((segment_brand_day_1.revenue_sum IS NULL) OR (segment_brand_day_1.revenue_sum = '0'::numeric)) AND (brendan_sbd_postprocessing.revenue IS NOT NULL) AND (brendan_sbd_postprocessing.revenue > '0'::double precision)) THEN brendan_sbd_postprocessing.revenue WHEN (((brendan_sbd_postprocessing.revenue IS NULL) OR (brendan_sbd_postprocessing.revenue = '0'::double precision)) AND (segment_brand_day_1.revenue_sum IS NOT NULL) AND (segment_brand_day_1.revenue_sum > '0'::numeric)) THEN (segment_brand_day_1.revenue_sum)::double precision WHEN (abs((((segment_brand_day_1.revenue_sum - (brendan_sbd_postprocessing.revenue)::numeric) / NULLIF(segment_brand_day_1.revenue_sum, '0'::numeric)) * '100'::numeric)) > '10'::numeric) THEN brendan_sbd_postprocessing.revenue ELSE (segment_brand_day_1.revenue_sum)::double precision END), (CASE WHEN (((segment_brand_day_1.revenue_sum IS NULL) OR (segment_brand_day_1.revenue_sum = '0'::numeric)) AND (brendan_sbd_postprocessing.revenue IS NOT NULL) AND (brendan_sbd_postprocessing.revenue > '0'::double precision)) THEN true WHEN (((brendan_sbd_postprocessing.revenue IS NULL) OR (brendan_sbd_postprocessing.revenue = '0'::double precision)) AND (segment_brand_day_1.revenue_sum IS NOT NULL) AND (segment_brand_day_1.revenue_sum > '0'::numeric)) THEN false WHEN (abs((((segment_brand_day_1.revenue_sum - (brendan_sbd_postprocessing.revenue)::numeric) / NULLIF(segment_brand_day_1.revenue_sum, '0'::numeric)) * '100'::numeric)) > '10'::numeric) THEN true ELSE false END)
  • Buffers: shared hit=157,285, local read=6,970 dirtied=6,970 written=6,970, temp read=8,172 written=8,186
4. 247.006 4,729.830 ↓ 51.8 419,836 1

Sort (cost=5,343,411.90..5,343,432.18 rows=8,111 width=541) (actual time=4,686.053..4,729.830 rows=419,836 loops=1)

  • Output: segment_brand_day_1.id, brendan_sbd_postprocessing.brand, brendan_sbd_postprocessing.revenue, segment_brand_day_1.revenue_sum, (CASE WHEN (((segment_brand_day_1.revenue_sum IS NULL) OR (segment_brand_day_1.revenue_sum = '0'::numeric)) AND (brendan_sbd_postprocessing.revenue IS NOT NULL) AND (brendan_sbd_postprocessing.revenue > '0'::double precision)) THEN brendan_sbd_postprocessing.revenue WHEN (((brendan_sbd_postprocessing.revenue IS NULL) OR (brendan_sbd_postprocessing.revenue = '0'::double precision)) AND (segment_brand_day_1.revenue_sum IS NOT NULL) AND (segment_brand_day_1.revenue_sum > '0'::numeric)) THEN (segment_brand_day_1.revenue_sum)::double precision WHEN (abs((((segment_brand_day_1.revenue_sum - (brendan_sbd_postprocessing.revenue)::numeric) / NULLIF(segment_brand_day_1.revenue_sum, '0'::numeric)) * '100'::numeric)) > '10'::numeric) THEN brendan_sbd_postprocessing.revenue ELSE (segment_brand_day_1.revenue_sum)::double precision END), (CASE WHEN (((segment_brand_day_1.revenue_sum IS NULL) OR (segment_brand_day_1.revenue_sum = '0'::numeric)) AND (brendan_sbd_postprocessing.revenue IS NOT NULL) AND (brendan_sbd_postprocessing.revenue > '0'::double precision)) THEN true WHEN (((brendan_sbd_postprocessing.revenue IS NULL) OR (brendan_sbd_postprocessing.revenue = '0'::double precision)) AND (segment_brand_day_1.revenue_sum IS NOT NULL) AND (segment_brand_day_1.revenue_sum > '0'::numeric)) THEN false WHEN (abs((((segment_brand_day_1.revenue_sum - (brendan_sbd_postprocessing.revenue)::numeric) / NULLIF(segment_brand_day_1.revenue_sum, '0'::numeric)) * '100'::numeric)) > '10'::numeric) THEN true ELSE false END)
  • Sort Key: segment_brand_day_1.id, brendan_sbd_postprocessing.brand, brendan_sbd_postprocessing.revenue, segment_brand_day_1.revenue_sum, (CASE WHEN (((segment_brand_day_1.revenue_sum IS NULL) OR (segment_brand_day_1.revenue_sum = '0'::numeric)) AND (brendan_sbd_postprocessing.revenue IS NOT NULL) AND (brendan_sbd_postprocessing.revenue > '0'::double precision)) THEN brendan_sbd_postprocessing.revenue WHEN (((brendan_sbd_postprocessing.revenue IS NULL) OR (brendan_sbd_postprocessing.revenue = '0'::double precision)) AND (segment_brand_day_1.revenue_sum IS NOT NULL) AND (segment_brand_day_1.revenue_sum > '0'::numeric)) THEN (segment_brand_day_1.revenue_sum)::double precision WHEN (abs((((segment_brand_day_1.revenue_sum - (brendan_sbd_postprocessing.revenue)::numeric) / NULLIF(segment_brand_day_1.revenue_sum, '0'::numeric)) * '100'::numeric)) > '10'::numeric) THEN brendan_sbd_postprocessing.revenue ELSE (segment_brand_day_1.revenue_sum)::double precision END), (CASE WHEN (((segment_brand_day_1.revenue_sum IS NULL) OR (segment_brand_day_1.revenue_sum = '0'::numeric)) AND (brendan_sbd_postprocessing.revenue IS NOT NULL) AND (brendan_sbd_postprocessing.revenue > '0'::double precision)) THEN true WHEN (((brendan_sbd_postprocessing.revenue IS NULL) OR (brendan_sbd_postprocessing.revenue = '0'::double precision)) AND (segment_brand_day_1.revenue_sum IS NOT NULL) AND (segment_brand_day_1.revenue_sum > '0'::numeric)) THEN false WHEN (abs((((segment_brand_day_1.revenue_sum - (brendan_sbd_postprocessing.revenue)::numeric) / NULLIF(segment_brand_day_1.revenue_sum, '0'::numeric)) * '100'::numeric)) > '10'::numeric) THEN true ELSE false END)
  • Sort Method: external merge Disk: 22,408kB
  • Buffers: shared hit=157,285, local read=6,970 dirtied=6,970 written=6,970, temp read=8,172 written=8,186
5. 1,150.381 4,482.824 ↓ 51.8 419,836 1

Merge Join (cost=5,311,819.09..5,340,914.77 rows=8,111 width=541) (actual time=2,909.040..4,482.824 rows=419,836 loops=1)

  • Output: segment_brand_day_1.id, brendan_sbd_postprocessing.brand, brendan_sbd_postprocessing.revenue, segment_brand_day_1.revenue_sum, CASE WHEN (((segment_brand_day_1.revenue_sum IS NULL) OR (segment_brand_day_1.revenue_sum = '0'::numeric)) AND (brendan_sbd_postprocessing.revenue IS NOT NULL) AND (brendan_sbd_postprocessing.revenue > '0'::double precision)) THEN brendan_sbd_postprocessing.revenue WHEN (((brendan_sbd_postprocessing.revenue IS NULL) OR (brendan_sbd_postprocessing.revenue = '0'::double precision)) AND (segment_brand_day_1.revenue_sum IS NOT NULL) AND (segment_brand_day_1.revenue_sum > '0'::numeric)) THEN (segment_brand_day_1.revenue_sum)::double precision WHEN (abs((((segment_brand_day_1.revenue_sum - (brendan_sbd_postprocessing.revenue)::numeric) / NULLIF(segment_brand_day_1.revenue_sum, '0'::numeric)) * '100'::numeric)) > '10'::numeric) THEN brendan_sbd_postprocessing.revenue ELSE (segment_brand_day_1.revenue_sum)::double precision END, CASE WHEN (((segment_brand_day_1.revenue_sum IS NULL) OR (segment_brand_day_1.revenue_sum = '0'::numeric)) AND (brendan_sbd_postprocessing.revenue IS NOT NULL) AND (brendan_sbd_postprocessing.revenue > '0'::double precision)) THEN true WHEN (((brendan_sbd_postprocessing.revenue IS NULL) OR (brendan_sbd_postprocessing.revenue = '0'::double precision)) AND (segment_brand_day_1.revenue_sum IS NOT NULL) AND (segment_brand_day_1.revenue_sum > '0'::numeric)) THEN false WHEN (abs((((segment_brand_day_1.revenue_sum - (brendan_sbd_postprocessing.revenue)::numeric) / NULLIF(segment_brand_day_1.revenue_sum, '0'::numeric)) * '100'::numeric)) > '10'::numeric) THEN true ELSE false END
  • Merge Cond: ((((to_timestamp(((segment_brand_day_1.day / 1000))::double precision))::date) = brendan_sbd_postprocessing.recordeddate) AND ((segment_brand_day_1.brand)::text = (brendan_sbd_postprocessing.brand)::text))
  • Buffers: shared hit=157,277, local read=6,970 dirtied=6,970 written=6,970, temp read=5,371 written=5,378
6. 1,047.841 1,915.085 ↑ 3.6 419,903 1

Sort (cost=5,275,257.00..5,279,074.82 rows=1,527,130 width=37) (actual time=1,626.682..1,915.085 rows=419,903 loops=1)

  • Output: segment_brand_day_1.id, segment_brand_day_1.revenue_sum, segment_brand_day_1.day, segment_brand_day_1.brand, ((to_timestamp(((segment_brand_day_1.day / 1000))::double precision))::date)
  • Sort Key: ((to_timestamp(((segment_brand_day_1.day / 1000))::double precision))::date), segment_brand_day_1.brand
  • Sort Method: external merge Disk: 19,376kB
  • Buffers: shared hit=157,277, temp read=2,422 written=2,429
7. 593.867 867.244 ↑ 3.6 419,903 1

Bitmap Heap Scan on public.segment_brand_day segment_brand_day_1 (cost=35,127.83..5,034,885.48 rows=1,527,130 width=37) (actual time=284.853..867.244 rows=419,903 loops=1)

  • Output: segment_brand_day_1.id, segment_brand_day_1.revenue_sum, segment_brand_day_1.day, segment_brand_day_1.brand, (to_timestamp(((segment_brand_day_1.day / 1000))::double precision))::date
  • Recheck Cond: ((segment_brand_day_1.segment_id)::text = '699'::text)
  • Rows Removed by Index Recheck: 482,277
  • Heap Blocks: exact=63,877 lossy=66,550
  • Buffers: shared hit=157,277
8. 273.377 273.377 ↓ 3.5 5,379,344 1

Bitmap Index Scan on segment_brand_day_segment_id_index (cost=0.00..34,746.05 rows=1,527,130 width=0) (actual time=273.377..273.377 rows=5,379,344 loops=1)

  • Index Cond: ((segment_brand_day_1.segment_id)::text = '699'::text)
  • Buffers: shared hit=26,850
9. 67.080 1,417.358 ↓ 7.2 651,519 1

Materialize (cost=36,562.09..37,012.87 rows=90,157 width=528) (actual time=1,278.831..1,417.358 rows=651,519 loops=1)

  • Output: brendan_sbd_postprocessing.brand, brendan_sbd_postprocessing.revenue, brendan_sbd_postprocessing.recordeddate
  • Buffers: local read=6,970 dirtied=6,970 written=6,970, temp read=2,949 written=2,949
10. 1,211.288 1,350.278 ↓ 7.2 651,519 1

Sort (cost=36,562.09..36,787.48 rows=90,157 width=528) (actual time=1,278.826..1,350.278 rows=651,519 loops=1)

  • Output: brendan_sbd_postprocessing.brand, brendan_sbd_postprocessing.revenue, brendan_sbd_postprocessing.recordeddate
  • Sort Key: brendan_sbd_postprocessing.recordeddate, brendan_sbd_postprocessing.brand
  • Sort Method: external sort Disk: 23,592kB
  • Buffers: local read=6,970 dirtied=6,970 written=6,970, temp read=2,949 written=2,949
11. 138.990 138.990 ↓ 7.2 651,520 1

Seq Scan on pg_temp_11.brendan_sbd_postprocessing (cost=0.00..7,876.10 rows=90,157 width=528) (actual time=0.022..138.990 rows=651,520 loops=1)

  • Output: brendan_sbd_postprocessing.brand, brendan_sbd_postprocessing.revenue, brendan_sbd_postprocessing.recordeddate
  • Filter: (brendan_sbd_postprocessing.brand IS NOT NULL)
  • Buffers: local read=6,970 dirtied=6,970 written=6,970
12. 0.165 4,913.859 ↑ 67.6 60 1

Nested Loop (cost=0.58..35,039.82 rows=4,056 width=138) (actual time=4,869.796..4,913.859 rows=60 loops=1)

  • Output: segment_brand_day.id, segment_brand_day.segment_id, segment_brand_day.brand, segment_brand_day.day, segment_brand_day.review_count, segment_brand_day.asin_count, segment_brand_day.sales_count, comparison_model.value_to_display_displayed, segment_brand_day.""1p_revenue_sum"", segment_brand_day.""3p_revenue_sum"", segment_brand_day.price_average, segment_brand_day.unit_sales_1p, segment_brand_day.unit_sales_3p, segment_brand_day.ctid, comparison_model.*
  • Inner Unique: true
  • Buffers: shared hit=157,585, local read=6,970 dirtied=6,970 written=6,970, temp read=8,172 written=10,977
13. 4,913.214 4,913.214 ↑ 67.6 60 1

CTE Scan on comparison_model (cost=0.00..162.22 rows=4,056 width=48) (actual time=4,869.752..4,913.214 rows=60 loops=1)

  • Output: comparison_model.value_to_display_displayed, comparison_model.*, comparison_model.id
  • Filter: comparison_model.value_needs_updating
  • Rows Removed by Filter: 419,776
  • Buffers: shared hit=157,285, local read=6,970 dirtied=6,970 written=6,970, temp read=8,172 written=10,977
14. 0.480 0.480 ↑ 1.0 1 60

Index Scan using segment_brand_day_pkey on public.segment_brand_day (cost=0.58..8.59 rows=1 width=86) (actual time=0.008..0.008 rows=1 loops=60)

  • Output: segment_brand_day.id, segment_brand_day.segment_id, segment_brand_day.brand, segment_brand_day.day, segment_brand_day.review_count, segment_brand_day.asin_count, segment_brand_day.sales_count, segment_brand_day.""1p_revenue_sum"", segment_brand_day.""3p_revenue_sum"", segment_brand_day.price_average, segment_brand_day.unit_sales_1p, segment_brand_day.unit_sales_3p, segment_brand_day.ctid
  • Index Cond: (segment_brand_day.id = comparison_model.id)
  • Buffers: shared hit=300
Planning time : 1.203 ms
Execution time : 4,932.674 ms