explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5tP3 : Optimization for: plan #BAYH

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.193 208,350.931 ↑ 181.4 43 1

Sort (cost=981,578.05..981,597.55 rows=7,800 width=120) (actual time=208,350.928..208,350.931 rows=43 loops=1)

  • Sort Key: (COALESCE(pd.descrip2, '[NOT FOUND]'::character varying)), ((COALESCE(pd.valor, 0) - count(DISTINCT lifecycle_new.user_id)))
  • Sort Method: quicksort Memory: 32kB
  • Buffers: shared hit=17,245,661 read=2,936,597 dirtied=5, temp read=64,575 written=64,655
2. 11,911.349 208,350.738 ↑ 181.4 43 1

GroupAggregate (cost=980,301.88..981,073.81 rows=7,800 width=120) (actual time=195,370.453..208,350.738 rows=43 loops=1)

  • Group Key: (((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)), (COALESCE(pd.descrip, '[NOT FOUND]'::character varying)), (COALESCE(pd.descrip2, '[NOT FOUND]'::character varying)), pd.valor
  • Buffers: shared hit=17,245,661 read=2,936,597 dirtied=5, temp read=64,575 written=64,655
3. 5,608.477 196,439.389 ↓ 77.3 2,980,060 1

Sort (cost=980,301.88..980,398.23 rows=38,539 width=118) (actual time=195,244.913..196,439.389 rows=2,980,060 loops=1)

  • Sort Key: (((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)), (COALESCE(pd.descrip, '[NOT FOUND]'::character varying)), (COALESCE(pd.descrip2, '[NOT FOUND]'::character varying)), pd.valor
  • Sort Method: external merge Disk: 300,544kB
  • Buffers: shared hit=17,245,661 read=2,936,597 dirtied=5, temp read=58,950 written=59,016
4. 881.820 190,830.912 ↓ 77.3 2,980,060 1

Hash Join (cost=1,018.22..977,366.36 rows=38,539 width=118) (actual time=35,789.706..190,830.912 rows=2,980,060 loops=1)

  • Buffers: shared hit=17,245,661 read=2,936,597 dirtied=5
5. 0.000 189,948.578 ↓ 77.3 2,980,060 1

Gather (cost=1,016.35..976,875.78 rows=38,539 width=114) (actual time=35,789.186..189,948.578 rows=2,980,060 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=17,245,661 read=2,936,596 dirtied=5
6. 426.076 191,219.013 ↑ 1.6 993,353 3 / 3

Result (cost=16.35..972,021.88 rows=1,605,800 width=114) (actual time=35,768.383..191,219.013 rows=993,353 loops=3)

  • Buffers: shared hit=17,245,661 read=2,936,596 dirtied=5
7. 473.400 190,792.937 ↑ 1.6 993,353 3 / 3

ProjectSet (cost=16.35..919,833.38 rows=1,605,800 width=50) (actual time=35,768.38..190,792.937 rows=993,353 loops=3)

  • Buffers: shared hit=17,245,661 read=2,936,596 dirtied=5
8. 239.621 190,319.537 ↓ 12.4 198,671 3 / 3

Hash Join (cost=16.35..911,643.8 rows=16,058 width=50) (actual time=35,768.34..190,319.537 rows=198,671 loops=3)

  • Buffers: shared hit=17,245,661 read=2,936,596 dirtied=5
9. 985.445 190,077.709 ↓ 18.7 1,080,197 3 / 3

Nested Loop (cost=1.12..911,476.1 rows=57,638 width=69) (actual time=35,763.206..190,077.709 rows=1,080,197 loops=3)

  • Buffers: shared hit=17,245,589 read=2,936,582 dirtied=5
10. 102,583.144 102,583.144 ↓ 16.7 1,081,364 3 / 3

Index Scan using lifecycle_item_new_pkey on lifecycle_item_new lifecycle_item_new (cost=0.56..753,525.22 rows=64,910 width=64) (actual time=35,761.898..102,583.144 rows=1,081,364 loops=3)

  • Index Cond: (lifecycle_item_new.cycle_start_time = '2020-08-01 00:00:00'::timestamp without time zone)
  • Filter: (lifecycle_item_new.assignment_json IS NOT NULL)
  • Buffers: shared hit=2,837,071 read=1,065,486
11. 86,509.120 86,509.120 ↑ 1.0 1 3,244,092 / 3

Index Scan using lifecycle_new_pkey on lifecycle_new lifecycle_new (cost=0.56..2.42 rows=1 width=65) (actual time=0.079..0.08 rows=1 loops=3,244,092)

  • Index Cond: ((lifecycle_new.id)::text = (lifecycle_item_new.lifecycle_id)::text)
  • Filter: ((lifecycle_new.date_deleted IS NULL) AND ((lifecycle_new.status)::text = ANY ('{READY_TO_FULFILL,FULFILLMENT_IN_PROGRESS,FULFILLMENT,FULFILLED,SHIPPED,DELIVERED}'::text[])))
  • Buffers: shared hit=14,408,518 read=1,871,096 dirtied=5
12. 0.044 2.207 ↑ 1.0 151 3 / 3

Hash (cost=13.33..13.33 rows=151 width=18) (actual time=2.206..2.207 rows=151 loops=3)

  • Buffers: shared hit=19 read=7
13. 0.261 2.163 ↑ 1.0 151 3 / 3

Bitmap Heap Scan on cycle cycle (cost=3.45..13.33 rows=151 width=18) (actual time=2.061..2.163 rows=151 loops=3)

  • Filter: (cycle.date_deleted IS NULL)
  • Heap Blocks: exact=6
  • Buffers: shared hit=19 read=7
14. 1.902 1.902 ↑ 1.0 151 3 / 3

Bitmap Index Scan on cycle_seq_start_end_key (cost=0..3.41 rows=151 width=0) (actual time=1.902..1.902 rows=151 loops=3)

  • Index Cond: ((cycle.seq_id)::text = 'GB_PLUS-SEQ'::text)
  • Buffers: shared hit=5 read=3
15. 0.010 0.514 ↑ 1.0 39 1

Hash (cost=1.39..1.39 rows=39 width=72) (actual time=0.513..0.514 rows=39 loops=1)

  • Buffers: shared read=1
16. 0.504 0.504 ↑ 1.0 39 1

Seq Scan on product_descs pd (cost=0..1.39 rows=39 width=72) (actual time=0.499..0.504 rows=39 loops=1)

  • Buffers: shared read=1
Planning time : 3.514 ms
Execution time : 208,385.377 ms