explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jSOH

Settings
# exclusive inclusive rows x rows loops node
1. 0.057 1.145 ↓ 41.0 41 1

Nested Loop (cost=6.57..135.96 rows=1 width=16) (actual time=0.153..1.145 rows=41 loops=1)

2.          

CTE existing_retailer_pos

3. 0.002 0.029 ↑ 1.0 1 1

Nested Loop (cost=0.57..5.02 rows=1 width=16) (actual time=0.027..0.029 rows=1 loops=1)

4. 0.013 0.013 ↑ 1.0 1 1

Index Scan using retailer_poses_pkey on retailer_poses rp (cost=0.29..2.51 rows=1 width=12) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (id = 902057)
5. 0.014 0.014 ↑ 1.0 1 1

Index Scan using retailer_shop_pkey on retailer_shop rs (cost=0.28..2.50 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (id = rp.retailer_shop_id)
6. 0.081 1.006 ↓ 41.0 41 1

Nested Loop (cost=1.41..130.78 rows=1 width=24) (actual time=0.142..1.006 rows=41 loops=1)

  • Join Filter: (((c.loyalty_version)::text >= (erp.min_supported_loyalty_version)::text) AND ((c.loyalty_version)::text <= (erp.max_supported_loyalty_version)::text) AND (c.retailer_subsidiary_country_id = erp.retailer_country_id))
7. 0.050 0.843 ↓ 41.0 41 1

Nested Loop (cost=1.41..130.74 rows=1 width=24) (actual time=0.100..0.843 rows=41 loops=1)

8. 0.028 0.219 ↓ 41.0 41 1

Nested Loop (cost=0.99..111.09 rows=1 width=16) (actual time=0.065..0.219 rows=41 loops=1)

9. 0.037 0.037 ↑ 4.5 2 1

Index Scan using customer_loyalty_card_idx10 on customer_loyalty_card lc (cost=0.43..4.98 rows=9 width=4) (actual time=0.031..0.037 rows=2 loops=1)

  • Index Cond: (user_id = 2741820)
10. 0.154 0.154 ↓ 2.9 20 2

Index Scan using coupon_instances_loyalty_card_id_idx on coupon_instances ci (cost=0.55..11.72 rows=7 width=20) (actual time=0.023..0.077 rows=20 loops=2)

  • Index Cond: (loyalty_card_id = lc.id)
  • Filter: ((status)::text = 'READY'::text)
  • Rows Removed by Filter: 2
11. 0.574 0.574 ↑ 1.0 1 41

Index Scan using coupon_pk on coupons c (cost=0.42..19.64 rows=1 width=12) (actual time=0.014..0.014 rows=1 loops=41)

  • Index Cond: (id = ci.coupon_id)
  • Filter: (registered_user_active AND (now() >= valid_from) AND (now() <= valid_till) AND (((value_unit)::text <> ALL ('{ADDITIONAL_POINT,MULTIPLE_POINT}'::text[])) OR (((point_calculation_destination)::text = 'POS'::text) AND ((value_unit)::text = ANY ('{ADDITIONAL_POINT,MULTIPLE_POINT}'::text[])))) AND ((NOT retailer_shop_associable) OR (retailer_shop_associable AND (alternatives: SubPlan 2 or hashed SubPlan 3))))
12.          

SubPlan (forIndex Scan)

13. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.40..19.15 rows=1 width=0) (never executed)

14. 0.000 0.000 ↓ 0.0 0

CTE Scan on existing_retailer_pos erp_1 (cost=0.00..0.02 rows=1 width=4) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Index Only Scan using retailer_shop_coupons_pkey on loyalty_coupon_retailer_shops lcrs (cost=0.40..19.12 rows=1 width=4) (never executed)

  • Index Cond: ((retailer_shop_id = erp_1.retailer_shop_id) AND (loyalty_coupon_id = c.id))
  • Heap Fetches: 0
16. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.40..19.15 rows=1 width=4) (never executed)

17. 0.000 0.000 ↓ 0.0 0

CTE Scan on existing_retailer_pos erp_2 (cost=0.00..0.02 rows=1 width=4) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using retailer_shop_coupons_pkey on loyalty_coupon_retailer_shops lcrs_1 (cost=0.40..19.12 rows=1 width=8) (never executed)

  • Index Cond: (retailer_shop_id = erp_2.retailer_shop_id)
  • Heap Fetches: 0
19. 0.082 0.082 ↑ 1.0 1 41

CTE Scan on existing_retailer_pos erp (cost=0.00..0.02 rows=1 width=104) (actual time=0.001..0.002 rows=1 loops=41)

20. 0.082 0.082 ↑ 1.0 1 41

Index Only Scan using retailer_subsidiary_countries_pkey on retailer_subsidiary_countries rsc (cost=0.14..0.16 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=41)

  • Index Cond: (id = c.retailer_subsidiary_country_id)
  • Heap Fetches: 0
Planning time : 5.949 ms
Execution time : 1.440 ms