explain.depesz.com

PostgreSQL's explain analyze made readable

Result: njz4

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 87.247 ↓ 1.4 7 1
→

Limit (cost=12,583.25..12,583.26 rows=5 width=650) (actual time=87.243..87.247 rows=7 loops=1)

2. 0.045 87.246 ↓ 1.4 7 1
→

Sort (cost=12,583.25..12,583.26 rows=5 width=650) (actual time=87.243..87.246 rows=7 loops=1)

  • Sort Key: (CASE WHEN ((p.product_attributes ->> 'productEngine'::text) = 'APRL'::text) THEN 1 WHEN ((p.product_attributes ->> 'productEngine'::text) = 'FTWR'::text) THEN 2 WHEN ((p.product_attributes ->> 'productEngine'::text) = 'EQMT'::text) THEN 3 ELSE 4 END), (CASE WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'APRL_TOP'::text) THEN 1 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'APRL_BOTTOM'::text) THEN 2 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'APRL_SET_WARMUP'::text) THEN 3 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'APRL_HEADWEAR'::text) THEN 4 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'APRL_SOCK'::text) THEN 5 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'APRL_OTHER'::text) THEN 6 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'FTWR_HIGH_TOP'::text) THEN 7 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'FTWR_THREE_QUARTER_HIGH'::text) THEN 8 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'FTWR_LOW_TOP'::text) THEN 9 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'FTWR_OTHER'::text) THEN 10 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'EQMT_SOCK'::text) THEN 11 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'EQMT_BAG'::text) THEN 12 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'EQMT_BALL'::text) THEN 13 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'EQMT_EYEWEAR'::text) THEN 14 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'EQMT_GLOVE'::text) THEN 15 WHEN ((p.product_attributes ->> 'bodyTypeGroup'::text) = 'EQMT_OTHER'::text) THEN 16 ELSE 17 END), ((p.product_attributes ->> 'styleColorCode'::text))
  • Sort Method: quicksort Memory: 28kB
3. 0.187 87.201 ↓ 1.4 7 1
→

Nested Loop Left Join (cost=3,104.58..12,583.19 rows=5 width=650) (actual time=85.936..87.201 rows=7 loops=1)

4. 0.012 86.895 ↓ 1.4 7 1
→

Nested Loop Left Join (cost=3,096.13..12,539.67 rows=5 width=1,299) (actual time=85.821..86.895 rows=7 loops=1)

5. 0.010 86.834 ↓ 1.4 7 1
→

Nested Loop (cost=3,095.57..12,496.66 rows=5 width=1,307) (actual time=85.803..86.834 rows=7 loops=1)

6. 0.016 86.663 ↓ 1.4 7 1
→

Nested Loop (cost=3,074.58..12,391.52 rows=5 width=1,275) (actual time=85.743..86.663 rows=7 loops=1)

7. 0.005 86.465 ↓ 1.4 7 1
→

Nested Loop (cost=3,074.02..12,349.59 rows=5 width=57) (actual time=85.701..86.465 rows=7 loops=1)

8. 0.305 0.305 ↑ 1.0 1 1
→

Seq Scan on offering o (cost=0.00..46.95 rows=1 width=20) (actual time=0.207..0.305 rows=1 loops=1)

  • Filter: (offering_id = 3310)
  • Rows Removed by Filter: 1322
9. 0.236 86.155 ↓ 1.4 7 1
→

Hash Join (cost=3,074.02..12,302.59 rows=5 width=45) (actual time=85.491..86.155 rows=7 loops=1)

  • Hash Cond: (op.offering_product_id = pq.offering_product_id)
10. 0.533 0.533 ↑ 4.7 730 1
→

Index Scan using op_unique_prod_idx on offering_product op (cost=0.56..9,216.18 rows=3,439 width=45) (actual time=0.019..0.533 rows=730 loops=1)

  • Index Cond: (offering_id = 3310)
11. 10.741 85.386 ↑ 1.0 10,570 1
→

Hash (cost=2,941.34..2,941.34 rows=10,570 width=8) (actual time=85.386..85.386 rows=10,570 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 541kB
12. 27.168 74.645 ↑ 1.0 10,570 1
→

HashAggregate (cost=2,835.64..2,941.34 rows=10,570 width=8) (actual time=72.301..74.645 rows=10,570 loops=1)

  • Group Key: pq.offering_product_id
13. 47.477 47.477 ↑ 1.0 29,375 1
→

Seq Scan on planned_quantities pq (cost=0.00..2,762.20 rows=29,376 width=8) (actual time=0.126..47.477 rows=29,375 loops=1)

  • Filter: (planned_quantity > 0)
  • Rows Removed by Filter: 1
14. 0.182 0.182 ↑ 1.0 1 7
→

Index Scan using product_pkey on product p (cost=0.56..8.38 rows=1 width=1,265) (actual time=0.026..0.026 rows=1 loops=7)

  • Index Cond: ((product_id)::text = (op.product_id)::text)
15. 0.035 0.161 ↑ 1.0 1 7
→

Aggregate (cost=21.00..21.01 rows=1 width=32) (actual time=0.023..0.023 rows=1 loops=7)

  • Filter: ('Recommended'::text = ANY ((array_agg(t.tag_name))::text[]))
16. 0.056 0.126 ↑ 1.0 2 7
→

Hash Join (cost=8.86..20.98 rows=2 width=516) (actual time=0.015..0.018 rows=2 loops=7)

  • Hash Cond: (t.tag_id = top.tag_id)
17. 0.014 0.014 ↑ 28.0 5 7
→

Seq Scan on tag t (cost=0.00..11.40 rows=140 width=520) (actual time=0.001..0.002 rows=5 loops=7)

18. 0.014 0.056 ↑ 1.0 2 7
→

Hash (cost=8.84..8.84 rows=2 width=4) (actual time=0.008..0.008 rows=2 loops=7)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.042 0.042 ↑ 1.0 2 7
→

Index Scan using tags_op_op_fk_idx on tag_offering_product top (cost=0.43..8.84 rows=2 width=4) (actual time=0.005..0.006 rows=2 loops=7)

  • Index Cond: (offering_product_id = op.offering_product_id)
20. 0.007 0.049 ↓ 0.0 0 7
→

Limit (cost=0.56..8.58 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=7)

21. 0.042 0.042 ↓ 0.0 0 7
→

Index Scan using huy_test_idx on alert (cost=0.56..8.58 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=7)

  • Index Cond: ((((alert_detail ->> 'offeringUuid'::text))::uuid = o.offering_uuid) AND ((alert_detail ->> 'styleColor'::text) = (op.style_color)::text) AND ((alert_detail ->> 'state'::text) = 'PROCESSED'::text))
22. 0.077 0.119 ↑ 1.0 1 7
→

Aggregate (cost=8.45..8.47 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=7)

23. 0.042 0.042 ↓ 2.0 2 7
→

Index Scan using planned_quantities_pk on planned_quantities pq_1 (cost=0.41..8.43 rows=1 width=8) (actual time=0.005..0.006 rows=2 loops=7)

  • Index Cond: ((offering_id = 3310) AND (door_cluster_id = 0) AND (offering_product_id = op.offering_product_id))