explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OvlP

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 4,386.833 ↓ 3.0 3 1

Subquery Scan on result (cost=51,719.75..51,719.79 rows=1 width=128) (actual time=4,386.819..4,386.833 rows=3 loops=1)

2. 0.012 4,386.810 ↓ 3.0 3 1

Unique (cost=51,719.75..51,719.77 rows=1 width=34) (actual time=4,386.799..4,386.810 rows=3 loops=1)

3. 0.039 4,386.798 ↓ 3.0 3 1

Sort (cost=51,719.75..51,719.76 rows=1 width=34) (actual time=4,386.798..4,386.798 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.231 4,386.759 ↓ 3.0 3 1

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

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

SubPlan (forIndex Scan)

6. 0.447 9.138 ↑ 1.0 1 3

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

7. 8.691 8.691 ↓ 11.6 151 3

Index Scan using payments_contract_id_idx on payments pay (cost=0.42..138.93 rows=13 width=5) (actual time=0.275..2.897 rows=151 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. 72.801 4,374.840 ↑ 1.0 1 3

Aggregate (cost=41,217.27..41,217.28 rows=1 width=5) (actual time=1,458.280..1,458.280 rows=1 loops=3)

20. 17.019 4,302.039 ↓ 35.5 31,024 3

Append (cost=0.57..41,215.09 rows=873 width=5) (actual time=0.480..1,434.013 rows=31,024 loops=3)

21. 4,285.014 4,285.014 ↓ 36.6 31,024 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=0.473..1,428.338 rows=31,024 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.006 0.006 ↓ 0.0 0 3

Result (cost=6,460.74..38,134.62 rows=25 width=6) (actual time=0.002..0.002 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,460.74..38,134.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,460.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.135 2.550 ↑ 1.0 1 3

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

26. 2.415 2.415 ↓ 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.223..0.805 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
Planning time : 4.459 ms