explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BAYH

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.190 209,124.455 ↑ 181.4 43 1

Sort (cost=991,079.43..991,098.93 rows=7,800 width=120) (actual time=209,124.451..209,124.455 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,243,913 read=2,938,138 dirtied=17 written=469, temp read=64,576 written=64,656
2. 11,844.772 209,124.265 ↑ 181.4 43 1

GroupAggregate (cost=984,607.93..990,575.19 rows=7,800 width=120) (actual time=196,226.063..209,124.265 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,243,913 read=2,938,138 dirtied=17 written=469, temp read=64,576 written=64,656
3. 5,633.766 197,279.493 ↓ 76.7 2,980,060 1

Sort (cost=984,607.93..984,705 rows=38,829 width=118) (actual time=196,096.611..197,279.493 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,552kB
  • Buffers: shared hit=17,243,913 read=2,938,138 dirtied=17 written=469, temp read=58,951 written=59,017
4. 828.707 191,645.727 ↓ 76.7 2,980,060 1

Hash Join (cost=1,018.22..981,648.21 rows=38,829 width=118) (actual time=29,814.113..191,645.727 rows=2,980,060 loops=1)

  • Buffers: shared hit=17,243,913 read=2,938,138 dirtied=17 written=469
5. 0.000 190,816.490 ↓ 76.7 2,980,060 1

Gather (cost=1,016.35..981,153.96 rows=38,829 width=114) (actual time=29,813.574..190,816.49 rows=2,980,060 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=17,243,913 read=2,938,137 dirtied=17 written=469
6. 411.894 192,760.231 ↑ 1.6 993,353 3 / 3

Result (cost=16.35..976,271.06 rows=1,617,900 width=114) (actual time=29,803.504..192,760.231 rows=993,353 loops=3)

  • Buffers: shared hit=17,243,913 read=2,938,137 dirtied=17 written=469
7. 493.184 192,348.337 ↑ 1.6 993,353 3 / 3

ProjectSet (cost=16.35..923,689.31 rows=1,617,900 width=50) (actual time=29,803.5..192,348.337 rows=993,353 loops=3)

  • Buffers: shared hit=17,243,913 read=2,938,137 dirtied=17 written=469
8. 250.605 191,855.153 ↓ 12.3 198,671 3 / 3

Hash Join (cost=16.35..915,438.02 rows=16,179 width=50) (actual time=29,803.47..191,855.153 rows=198,671 loops=3)

  • Buffers: shared hit=17,243,913 read=2,938,137 dirtied=17 written=469
9. 951.526 191,604.012 ↓ 18.6 1,080,197 3 / 3

Nested Loop (cost=1.12..915,269.17 rows=58,072 width=69) (actual time=29,802.83..191,604.012 rows=1,080,197 loops=3)

  • Buffers: shared hit=17,243,839 read=2,938,125 dirtied=17 written=469
10. 101,980.638 101,980.638 ↓ 16.5 1,081,364 3 / 3

Index Scan using lifecycle_item_new_pkey on lifecycle_item_new lifecycle_item_new (cost=0.56..756,164.06 rows=65,398 width=64) (actual time=29,797.468..101,980.638 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,836,937 read=1,065,412 dirtied=3 written=191
11. 88,671.848 88,671.848 ↑ 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.081..0.082 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.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,406,902 read=1,872,713 dirtied=14 written=278
12. 0.033 0.536 ↑ 1.0 151 3 / 3

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

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

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

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

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

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

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

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

  • Buffers: shared read=1
Planning time : 3.417 ms
Execution time : 209,166.461 ms