explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nyMs : Optimization for: Optimization for: edo; plan #hq8Q; plan #y2Xh

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 73.503 155.115 ↓ 15.4 77 1

HashAggregate (cost=15,096.29..15,096.34 rows=5 width=51) (actual time=155.106..155.115 rows=77 loops=1)

  • Group Key: k.codes_keymap, k.keymap_usage, k.rule_type, p.price_id
2. 36.838 81.612 ↓ 9.2 150,764 1

Merge Right Join (cost=14,681.89..14,932.08 rows=16,421 width=51) (actual time=36.117..81.612 rows=150,764 loops=1)

  • Merge Cond: (k.ref_id = r.price_id)
3. 0.663 2.022 ↑ 5.0 157 1

Sort (cost=434.13..436.10 rows=789 width=51) (actual time=2.009..2.022 rows=157 loops=1)

  • Sort Key: k.ref_id
  • Sort Method: quicksort Memory: 95kB
4. 1.359 1.359 ↑ 1.0 789 1

Seq Scan on rt_keymapcombo k (cost=0.00..396.16 rows=789 width=51) (actual time=0.509..1.359 rows=789 loops=1)

  • Filter: ((release_valid_to IS NULL) AND ((ref_object)::text = 'RT_PRODUCT_OFFER_PRICE'::text))
  • Rows Removed by Filter: 9624
5. 20.554 42.752 ↓ 19.1 150,762 1

Sort (cost=14,247.69..14,267.38 rows=7,876 width=10) (actual time=34.102..42.752 rows=150,762 loops=1)

  • Sort Key: r.price_id
  • Sort Method: quicksort Memory: 3731kB
6. 7.116 22.198 ↓ 5.9 46,810 1

Nested Loop Left Join (cost=17.58..13,737.99 rows=7,876 width=10) (actual time=1.184..22.198 rows=46,810 loops=1)

7. 0.022 1.420 ↓ 1.7 27 1

Nested Loop Left Join (cost=0.28..773.81 rows=16 width=5) (actual time=1.166..1.420 rows=27 loops=1)

8. 1.398 1.398 ↓ 1.7 27 1

Seq Scan on rt_product_offer_price p (cost=0.00..622.18 rows=16 width=37) (actual time=1.162..1.398 rows=27 loops=1)

  • Filter: ((release_valid_to IS NULL) AND ((price_type_code)::text <> 'ALW'::text) AND (product_offer_id = 1162::numeric) AND (('now'::cstring)::date >= COALESCE(valid_from, (('now'::cstring)::date)::timesta
  • Rows Removed by Filter: 7888
9. 0.000 0.000 ↓ 0.0 0 27

Index Scan using rt_product_offer_price_price_id on rt_product_offer_price pp (cost=0.28..9.47 rows=1 width=5) (actual time=0.000..0.000 rows=0 loops=27)

  • Index Cond: (p.parent_prod_offer_price_id = price_id)
  • Filter: (release_valid_to IS NULL)
10. 9.045 13.662 ↓ 1.5 1,734 27

Bitmap Heap Scan on rt_product_offer_price_rules r (cost=17.29..798.49 rows=1,177 width=5) (actual time=0.179..0.506 rows=1,734 loops=27)

  • Recheck Cond: (p.price_id = price_id)
  • Filter: (release_valid_to IS NULL)
  • Heap Blocks: exact=1405
11. 4.617 4.617 ↓ 1.5 1,734 27

Bitmap Index Scan on rt_product_offer_price_rules_price_id (cost=0.00..17.00 rows=1,177 width=0) (actual time=0.171..0.171 rows=1,734 loops=27)

  • Index Cond: (p.price_id = price_id)