explain.depesz.com

PostgreSQL's explain analyze made readable

Result: solV : Optimization for: plan #BAYH

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.143 222,123.035 ↑ 181.4 43 1

Sort (cost=986,543.48..986,562.98 rows=7,800 width=120) (actual time=222,123.033..222,123.035 rows=43 loops=1)

  • Sort Key: (COALESCE(pd.descrip2, '[NOT FOUND]'::character varying)), (CASE WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jzbuqsbimg0h1zov'::text) THEN (30928 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k1wafcz2covd1p59'::text) THEN (127562 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jv8dr6ny901t1pze'::text) THEN (8518 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jyt5znho9mbo7s9'::text) THEN (132608 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k0nygcoozbpq2pl'::text) THEN (48293 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jy3lup66jikrdh3'::text) THEN (15441 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jy3lup8mni51di3'::text) THEN (127602 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-ju04waou1h151ixh'::text) THEN (52709 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k1hwok4dt4oan05'::text) THEN (70411 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jios18lvy7ov1la8'::text) THEN (22298 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jjop5yr7btimaam'::text) THEN (50676 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jjkhf9at7mju2106'::text) THEN (9446 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jthphfwg2yh29kt'::text) THEN (59607 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k9965a5b3vgvrp3'::text) THEN (127602 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k728gm9wa50upm4'::text) THEN (127602 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k06uo840cihy1vup'::text) THEN (11829 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k06uo84qjl661vve'::text) THEN (13809 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k0y7kdv4umt4kjg'::text) THEN (94849 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k401ab03t6y3jpf'::text) THEN (23370 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jyyx1toe4q2hkqj'::text) THEN (93634 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jy3lup61m0todh1'::text) THEN (70181 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jy3lup8rt543di5'::text) THEN (127571 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jy3lup51znaydgm'::text) THEN (99019 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k1jumarwe7i73mn'::text) THEN (28500 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k3szrwaj6r0p22e'::text) THEN (7566 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k3szrw7j7io820e'::text) THEN (19104 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k3szrwcpp7nq23q'::text) THEN (22456 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k3szrw9ke05k21q'::text) THEN (28428 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k3szrw5g3wd21z2'::text) THEN (4687 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k3szrw6io3e11zq'::text) THEN (7643 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k3szrwbjpp83232'::text) THEN (19153 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k3szrw8jqmn0212'::text) THEN (28687 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k3szrwdpycyt24e'::text) THEN (22181 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k3szrweq7b83252'::text) THEN (9546 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k3szrw4gu7741ye'::text) THEN (6601 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jy3lup897gxjdhy'::text) THEN (102386 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-jyyx1tw3rk7yktw'::text) THEN (223809 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k3elctfmu0op1q4g'::text) THEN (95702 - count(DISTINCT lifecycle_new.user_id)) WHEN ((((jsonb_array_elements(jsonb_extract_path(lifecycle_item_new.assignment_json, VARIADIC '{items}'::text[]))) ->> 'product_id'::text)) = 'p-k2yy308ydlvv1q3y'::text) THEN (165872 - count(DISTINCT lifecycle_new.user_id)) ELSE NULL::bigint END)
  • Sort Method: quicksort Memory: 32kB
  • Buffers: shared hit=17,188,634 read=2,993,442 dirtied=8 written=122,746, temp read=64,575 written=64,655
2. 12,537.207 222,122.892 ↑ 181.4 43 1

GroupAggregate (cost=980,104.6..986,039.24 rows=7,800 width=120) (actual time=208,536.132..222,122.892 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,188,634 read=2,993,442 dirtied=8 written=122,746, temp read=64,575 written=64,655
3. 5,869.827 209,585.685 ↓ 77.3 2,980,060 1

Sort (cost=980,104.6..980,200.95 rows=38,539 width=118) (actual time=208,372.017..209,585.685 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,188,634 read=2,993,442 dirtied=8 written=122,746, temp read=58,950 written=59,016
4. 885.511 203,715.858 ↓ 77.3 2,980,060 1

Hash Join (cost=1,018.22..977,169.08 rows=38,539 width=118) (actual time=28,565.309..203,715.858 rows=2,980,060 loops=1)

  • Buffers: shared hit=17,188,634 read=2,993,442 dirtied=8 written=122,746
5. 0.000 202,829.573 ↓ 77.3 2,980,060 1

Gather (cost=1,016.35..976,678.5 rows=38,539 width=114) (actual time=28,564.527..202,829.573 rows=2,980,060 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=17,188,634 read=2,993,441 dirtied=8 written=122,746
6. 428.103 204,156.092 ↑ 1.6 993,353 3 / 3

Result (cost=16.35..971,824.6 rows=1,605,800 width=114) (actual time=28,555.655..204,156.092 rows=993,353 loops=3)

  • Buffers: shared hit=17,188,634 read=2,993,441 dirtied=8 written=122,746
7. 480.382 203,727.989 ↑ 1.6 993,353 3 / 3

ProjectSet (cost=16.35..919,636.1 rows=1,605,800 width=50) (actual time=28,555.652..203,727.989 rows=993,353 loops=3)

  • Buffers: shared hit=17,188,634 read=2,993,441 dirtied=8 written=122,746
8. 257.520 203,247.607 ↓ 12.4 198,671 3 / 3

Hash Join (cost=16.35..911,446.52 rows=16,058 width=50) (actual time=28,555.613..203,247.607 rows=198,671 loops=3)

  • Buffers: shared hit=17,188,634 read=2,993,441 dirtied=8 written=122,746
9. 856.204 202,988.993 ↓ 18.7 1,080,197 3 / 3

Nested Loop (cost=1.12..911,278.82 rows=57,638 width=69) (actual time=28,554.426..202,988.993 rows=1,080,197 loops=3)

  • Buffers: shared hit=17,188,561 read=2,993,428 dirtied=8 written=122,746
10. 108,054.121 108,054.121 ↓ 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,328.85 rows=64,910 width=64) (actual time=28,553.191..108,054.121 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,785,727 read=1,116,648 dirtied=3 written=34,096
11. 94,078.668 94,078.668 ↑ 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.085..0.087 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,402,834 read=1,876,780 dirtied=5 written=88,650
12. 0.031 1.094 ↑ 1.0 151 3 / 3

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

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

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

  • Filter: (cycle.date_deleted IS NULL)
  • Heap Blocks: exact=6
  • Buffers: shared hit=19 read=7
14. 0.545 0.545 ↑ 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.545..0.545 rows=151 loops=3)

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

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

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

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

  • Buffers: shared read=1
Planning time : 4.415 ms
Execution time : 222,169.039 ms