explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iBiz

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 4,075.159 ↓ 3.0 3 1

Subquery Scan on result (cost=51,715.75..51,715.79 rows=1 width=128) (actual time=4,075.147..4,075.159 rows=3 loops=1)

2. 0.011 4,075.140 ↓ 3.0 3 1

Unique (cost=51,715.75..51,715.77 rows=1 width=34) (actual time=4,075.130..4,075.140 rows=3 loops=1)

3. 0.029 4,075.129 ↓ 3.0 3 1

Sort (cost=51,715.75..51,715.76 rows=1 width=34) (actual time=4,075.128..4,075.129 rows=3 loops=1)

  • Sort Key: c.date_begin, (CASE WHEN (c.work_scheme = 3) THEN 0::numeric ELSE c.initial_balance END), (COALESCE((SubPlan 1), 0::numeric)), (COALESCE(CASE WHEN ((c.work_scheme = 1) AND ((c.charge_type <> 1) OR ((c.pricing_type <> 3) AND (c.pricing_type <> 4)))) THEN (SubPlan 2) WHEN (c.work_scheme = 3) THEN (SubPlan 3) ELSE (SubPlan 4) END, 0::numeric)), (COALESCE((SubPlan 5), 0::numeric))
  • Sort Method: quicksort Memory: 25kB
4. 0.182 4,075.100 ↓ 3.0 3 1

Index Scan using contracts_number_idx on contracts c (cost=0.41..51,715.74 rows=1 width=34) (actual time=3,639.396..4,075.100 rows=3 loops=1)

  • Index Cond: ((number)::text = 'ДК18-ЭМКТ01951'::text)
  • Filter: (emitent = 3)
5.          

SubPlan (forIndex Scan)

6. 0.312 18.453 ↑ 1.0 1 3

Aggregate (cost=138.96..138.97 rows=1 width=5) (actual time=6.151..6.151 rows=1 loops=3)

7. 18.141 18.141 ↓ 11.5 149 3

Index Scan using payments_contract_id_idx on payments pay (cost=0.42..138.93 rows=13 width=5) (actual time=3.371..6.047 rows=149 loops=3)

  • Index Cond: ((contract_id)::text = (c.number)::text)
  • Filter: ((state = 1) AND (posting_date >= (CASE WHEN ((c.initial_balance IS NOT NULL) AND (c.work_scheme <> 3)) THEN '2014-07-10'::text ELSE '1970-01-01'::text END)::date))
  • Rows Removed by Filter: 2
8. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=1,699.12..1,699.13 rows=1 width=5) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..1,698.77 rows=140 width=5) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Index Scan using contracts_number_idx on contracts (cost=0.41..8.43 rows=1 width=16) (never executed)

  • Index Cond: ((number)::text = (c.number)::text)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using card_moneybag_history_contract_id_idx on card_moneybag_history cmh (cost=0.43..1,688.93 rows=140 width=19) (never executed)

  • Index Cond: ((contract_id)::text = (contracts.id)::text)
  • Filter: ((action = ANY ('{9,10}'::integer[])) AND (date_start >= (CASE WHEN (c.initial_balance IS NOT NULL) THEN '2014-07-10'::text ELSE '1970-01-01'::text END)::date))
12. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8,611.99..8,612.00 rows=1 width=8) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=2.06..8,596.58 rows=2,054 width=8) (never executed)

  • Hash Cond: (cou.nominal_id = son.id)
14. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.98..8,567.25 rows=2,054 width=14) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Index Scan using contracts_number_idx on contracts contracts_1 (cost=0.41..8.43 rows=1 width=16) (never executed)

  • Index Cond: ((number)::text = (c.number)::text)
16. 0.000 0.000 ↓ 0.0 0

Index Scan using coupons_new_contract_id_idx on coupons_new cou (cost=0.56..8,538.28 rows=2,054 width=31) (never executed)

  • Index Cond: ((contract_id)::text = (contracts_1.id)::text)
  • Filter: (state <> 3)
17. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.04..1.04 rows=4 width=4) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Seq Scan on set_of_nominal son (cost=0.00..1.04 rows=4 width=4) (never executed)

19. 60.948 4,054.080 ↑ 1.0 1 3

Aggregate (cost=41,213.27..41,213.28 rows=1 width=5) (actual time=1,351.360..1,351.360 rows=1 loops=3)

20. 15.090 3,993.132 ↓ 35.3 30,831 3

Append (cost=0.57..41,211.09 rows=873 width=5) (actual time=3.527..1,331.044 rows=30,831 loops=3)

21. 3,978.033 3,978.033 ↓ 36.4 30,831 3

Index Scan using transactions_new_contract_number_transaction_date_idx on transactions_new (cost=0.57..3,071.74 rows=848 width=5) (actual time=3.521..1,326.011 rows=30,831 loops=3)

  • Index Cond: (((contract_number)::text = (c.number)::text) AND (transaction_date >= (CASE WHEN (c.initial_balance IS NOT NULL) THEN '2014-07-01'::text ELSE '1970-01-01'::text END)::date))
  • Filter: (state_id = ANY ('{1,3}'::integer[]))
22. 0.009 0.009 ↓ 0.0 0 3

Result (cost=6,456.74..38,130.62 rows=25 width=6) (actual time=0.003..0.003 rows=0 loops=3)

  • One-Time Filter: ((c.pricing_type = 4) AND (c.work_scheme = 1))
23. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on divided_transactions_new (cost=6,456.74..38,130.62 rows=25 width=6) (never executed)

  • Recheck Cond: (transaction_date >= (CASE WHEN (c.initial_balance IS NOT NULL) THEN '2014-07-01'::text ELSE '1970-01-01'::text END)::date)
  • Filter: ((state_id = ANY ('{1,3}'::integer[])) AND ((contract_number)::text = (c.number)::text))
24. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on divided_transactions_new_transaction_date_transaction_time_idx (cost=0.00..6,456.73 rows=244,306 width=0) (never executed)

  • Index Cond: (transaction_date >= (CASE WHEN (c.initial_balance IS NOT NULL) THEN '2014-07-01'::text ELSE '1970-01-01'::text END)::date)
25. 0.099 2.385 ↑ 1.0 1 3

Aggregate (cost=43.90..43.91 rows=1 width=7) (actual time=0.795..0.795 rows=1 loops=3)

26. 2.286 2.286 ↓ 25.0 25 3

Index Scan using invoices_consignee_contract_id_idx on invoices (cost=0.42..43.89 rows=1 width=7) (actual time=0.251..0.762 rows=25 loops=3)

  • Index Cond: ((consignee_contract_id)::text = (c.number)::text)
  • Filter: ((document_type > 2) AND (deleted = 0) AND (posting_date >= (CASE WHEN (c.initial_balance IS NOT NULL) THEN '2014-07-10'::text ELSE '1970-01-01'::text END)::date))
  • Rows Removed by Filter: 9