explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h3M4 : Optimization for: plan #WRVt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.003 0.535 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.00..1,046.67 rows=1 width=24) (actual time=0.535..0.535 rows=0 loops=1)

  • Join Filter: (((bp.contract_transaction_id = ct.contract_transaction_id) OR (bp.contract_transaction_id IS NULL)) AND ((bp.rule_id = r.rule_id) OR (bp.rule_id IS NULL)))
2. 0.000 0.532 ↓ 0.0 0 1

Nested Loop (cost=0.00..1,044.66 rows=1 width=28) (actual time=0.532..0.532 rows=0 loops=1)

  • Join Filter: ((m.agent_id = (-1)) OR (m.agent_id = ct.contract_agent_id))
3. 0.011 0.532 ↓ 0.0 0 1

Nested Loop (cost=0.00..1,044.33 rows=1 width=24) (actual time=0.532..0.532 rows=0 loops=1)

4. 0.047 0.346 ↓ 25.0 25 1

Nested Loop (cost=0.00..1,040.01 rows=1 width=47) (actual time=0.061..0.346 rows=25 loops=1)

  • Join Filter: (((r.applicable_record_type)::text ~~ (('%'::text || (ct.record_type_id)::text) || '%'::text)) OR (r.applicable_record_type IS NULL))
5. 0.031 0.274 ↓ 6.2 25 1

Nested Loop (cost=0.00..1,038.67 rows=4 width=41) (actual time=0.047..0.274 rows=25 loops=1)

6. 0.018 0.018 ↑ 35.0 25 1

Seq Scan on tmp_transaction_validation r_trans (cost=0.00..38.60 rows=876 width=20) (actual time=0.012..0.018 rows=25 loops=1)

  • Filter: (contract_sequence <> '-1'::bpchar)
7. 0.225 0.225 ↑ 1.0 1 25

Index Scan using ix_contract_transaction_contract_seq on contract_transaction ct (cost=0.00..1.11 rows=1 width=30) (actual time=0.008..0.009 rows=1 loops=25)

  • Index Cond: ((contract_number = r_trans.contract_number) AND (transaction_sequence = r_trans.contract_sequence))
8. 0.025 0.025 ↑ 1.0 1 25

Index Scan using pk_validation_rule on validation_rule r (cost=0.00..0.29 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=25)

  • Index Cond: (rule_id = 1001)
9. 0.175 0.175 ↓ 0.0 0 25

Index Scan using eval_sg_con_m1_pkey on sg_con_m1 m1 (cost=0.00..4.29 rows=1 width=13) (actual time=0.007..0.007 rows=0 loops=25)

  • Index Cond: (sg_con_contract = r_trans.contract_number)
  • Filter: ((sg_con_status)::text <> ALL ('{P,A,C,X}'::text[]))
10. 0.000 0.000 ↓ 0.0 0

Index Scan using ixfk_validation_rule_agent_mapping_validation_rule on validation_rule_agent_mapping m (cost=0.00..0.29 rows=1 width=12) (never executed)

  • Index Cond: (rule_id = 1001)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using ixfk_error_queue_bypass_agent on error_queue_bypass bp (cost=0.00..0.29 rows=1 width=16) (never executed)

  • Index Cond: (contract_agent_id = ct.contract_agent_id)
  • Filter: bypass_flag
12.          

SubPlan (for Nested Loop Anti Join)

13. 0.000 0.000 ↓ 0.0 0

Limit (cost=1.39..1.39 rows=1 width=6) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Sort (cost=1.39..1.39 rows=1 width=6) (never executed)

  • Sort Key: dep_map.priority
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on department_agent_resolution_owner_mapping dep_map (cost=0.00..1.38 rows=1 width=6) (never executed)

  • Filter: ((resolution_department_id = r.resolution_department_id) AND ((agent_id = (-1)) OR (agent_id = ct.contract_agent_id)))
Total runtime : 0.660 ms