explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jLKv

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 443.698 ↑ 1.0 1 1

Limit (cost=12,308.31..12,308.32 rows=1 width=650) (actual time=443.698..443.698 rows=1 loops=1)

2. 0.014 443.698 ↑ 1.0 1 1

Sort (cost=12,308.31..12,308.32 rows=1 width=650) (actual time=443.698..443.698 rows=1 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: 25kB
3. 0.383 443.684 ↑ 1.0 1 1

Nested Loop Semi Join (cost=9,290.41..12,308.30 rows=1 width=650) (actual time=4.664..443.684 rows=1 loops=1)

4. 0.300 34.321 ↓ 676.0 676 1

Nested Loop Left Join (cost=9,290.00..12,113.07 rows=1 width=1,331) (actual time=4.612..34.321 rows=676 loops=1)

5. 0.462 31.993 ↓ 676.0 676 1

Nested Loop Left Join (cost=9,281.55..12,104.58 rows=1 width=1,299) (actual time=4.603..31.993 rows=676 loops=1)

6. 0.521 28.827 ↓ 676.0 676 1

Nested Loop (cost=9,280.99..12,095.98 rows=1 width=1,307) (actual time=4.592..28.827 rows=676 loops=1)

7. 0.385 20.859 ↓ 677.0 677 1

Nested Loop (cost=9,260.00..12,074.95 rows=1 width=1,275) (actual time=4.558..20.859 rows=677 loops=1)

8. 0.343 8.890 ↓ 724.0 724 1

Nested Loop (cost=9,259.44..12,066.56 rows=1 width=57) (actual time=4.535..8.890 rows=724 loops=1)

9. 2.839 7.099 ↓ 724.0 724 1

Hash Right Join (cost=9,259.16..12,058.26 rows=1 width=45) (actual time=4.528..7.099 rows=724 loops=1)

  • Hash Cond: (pq.offering_product_id = op.offering_product_id)
  • Filter: ((pq.offering_product_id IS NULL) OR (pq.planned_quantity = 0))
  • Rows Removed by Filter: 14
10. 3.727 3.727 ↑ 1.0 29,376 1

Seq Scan on planned_quantities pq (cost=0.00..2,688.76 rows=29,376 width=12) (actual time=0.112..3.727 rows=29,376 loops=1)

11. 0.166 0.533 ↑ 4.7 730 1

Hash (cost=9,216.18..9,216.18 rows=3,439 width=45) (actual time=0.533..0.533 rows=730 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 88kB
12. 0.367 0.367 ↑ 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.017..0.367 rows=730 loops=1)

  • Index Cond: (offering_id = 3310)
13. 1.448 1.448 ↑ 1.0 1 724

Index Scan using offering_pkey on offering o (cost=0.28..8.29 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=724)

  • Index Cond: (offering_id = 3310)
14. 11.584 11.584 ↑ 1.0 1 724

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

  • Index Cond: ((product_id)::text = (op.product_id)::text)
15. 2.031 7.447 ↑ 1.0 1 677

Aggregate (cost=21.00..21.01 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=677)

  • Filter: ('Recommended'::text = ANY ((array_agg(t.tag_name))::text[]))
  • Rows Removed by Filter: 0
16. 2.032 5.416 ↑ 1.0 2 677

Hash Join (cost=8.86..20.98 rows=2 width=516) (actual time=0.007..0.008 rows=2 loops=677)

  • Hash Cond: (t.tag_id = top.tag_id)
17. 0.676 0.676 ↑ 28.0 5 676

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

18. 0.677 2.708 ↑ 1.0 2 677

Hash (cost=8.84..8.84 rows=2 width=4) (actual time=0.004..0.004 rows=2 loops=677)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 2.031 2.031 ↑ 1.0 2 677

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.003..0.003 rows=2 loops=677)

  • Index Cond: (offering_product_id = op.offering_product_id)
20. 0.000 2.704 ↓ 0.0 0 676

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

21. 2.704 2.704 ↓ 0.0 0 676

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

  • 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.676 2.028 ↑ 1.0 1 676

Aggregate (cost=8.45..8.47 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=676)

23. 1.352 1.352 ↓ 0.0 0 676

Index Scan using planned_quantities_pk on planned_quantities pq_2 (cost=0.41..8.43 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=676)

  • Index Cond: ((offering_id = 3310) AND (door_cluster_id = 3279) AND (offering_product_id = op.offering_product_id))
24. 408.980 408.980 ↓ 0.0 0 676

Index Scan using planned_quantities_pk on planned_quantities pq_1 (cost=0.41..226.03 rows=3 width=8) (actual time=0.605..0.605 rows=0 loops=676)

  • Index Cond: (offering_product_id = op.offering_product_id)
  • Filter: (planned_quantity > 0)