explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tGn9 : Optimization for: plan #BAYH

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.125 287,621.032 ↑ 181.4 43 1

Sort (cost=981,576.27..981,595.77 rows=7,800 width=120) (actual time=287,621.03..287,621.032 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,216,757 read=2,965,518 dirtied=9,768 written=63,777, temp read=64,575 written=64,655
2. 11,720.753 287,620.907 ↑ 181.4 43 1

GroupAggregate (cost=980,300.1..981,072.03 rows=7,800 width=120) (actual time=274,878.892..287,620.907 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,216,757 read=2,965,518 dirtied=9,768 written=63,777, temp read=64,575 written=64,655
3. 5,736.322 275,900.154 ↓ 77.3 2,980,060 1

Sort (cost=980,300.1..980,396.45 rows=38,539 width=118) (actual time=274,749.048..275,900.154 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,216,757 read=2,965,518 dirtied=9,768 written=63,777, temp read=58,950 written=59,016
4. 858.103 270,163.832 ↓ 77.3 2,980,060 1

Hash Join (cost=1,018.22..977,364.58 rows=38,539 width=118) (actual time=35,531.291..270,163.832 rows=2,980,060 loops=1)

  • Buffers: shared hit=17,216,757 read=2,965,518 dirtied=9,768 written=63,777
5. 0.000 269,305.222 ↓ 77.3 2,980,060 1

Gather (cost=1,016.35..976,874 rows=38,539 width=114) (actual time=35,530.776..269,305.222 rows=2,980,060 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=17,216,757 read=2,965,517 dirtied=9,768 written=63,777
6. 420.343 271,654.846 ↑ 1.6 993,353 3 / 3

Result (cost=16.35..972,020.1 rows=1,605,800 width=114) (actual time=35,507.423..271,654.846 rows=993,353 loops=3)

  • Buffers: shared hit=17,216,757 read=2,965,517 dirtied=9,768 written=63,777
7. 548.232 271,234.503 ↑ 1.6 993,353 3 / 3

ProjectSet (cost=16.35..919,831.6 rows=1,605,800 width=50) (actual time=35,507.418..271,234.503 rows=993,353 loops=3)

  • Buffers: shared hit=17,216,757 read=2,965,517 dirtied=9,768 written=63,777
8. 269.533 270,686.271 ↓ 12.4 198,671 3 / 3

Hash Join (cost=16.35..911,642.02 rows=16,058 width=50) (actual time=35,507.377..270,686.271 rows=198,671 loops=3)

  • Buffers: shared hit=17,216,757 read=2,965,517 dirtied=9,768 written=63,777
9. 698.770 270,415.216 ↓ 18.7 1,080,197 3 / 3

Nested Loop (cost=1.12..911,474.32 rows=57,638 width=69) (actual time=35,503.77..270,415.216 rows=1,080,197 loops=3)

  • Buffers: shared hit=17,216,685 read=2,965,503 dirtied=9,768 written=63,777
10. 129,139.126 129,139.126 ↓ 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,523.44 rows=64,910 width=64) (actual time=35,499.911..129,139.126 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,825,142 read=1,077,413 dirtied=1 written=21,969
11. 140,577.320 140,577.320 ↑ 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.128..0.13 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,391,543 read=1,888,090 dirtied=9,767 written=41,808
12. 0.038 1.522 ↑ 1.0 151 3 / 3

Hash (cost=13.33..13.33 rows=151 width=18) (actual time=1.521..1.522 rows=151 loops=3)

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

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

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

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

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

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

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

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

  • Buffers: shared read=1
Planning time : 3.801 ms
Execution time : 287,664.324 ms