explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NmIJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 1,714.971 ↓ 5.0 5 1

Subquery Scan on result (cost=55,323.48..55,323.52 rows=1 width=128) (actual time=1,714.956..1,714.971 rows=5 loops=1)

2. 0.010 1,714.946 ↓ 5.0 5 1

Unique (cost=55,323.48..55,323.50 rows=1 width=34) (actual time=1,714.935..1,714.946 rows=5 loops=1)

3. 0.071 1,714.936 ↓ 5.0 5 1

Sort (cost=55,323.48..55,323.48 rows=1 width=34) (actual time=1,714.933..1,714.936 rows=5 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) AND (c.pricing_type <> 5)))) 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.880 1,714.865 ↓ 5.0 5 1

Index Scan using contracts_number_idx on contracts c (cost=0.41..55,323.47 rows=1 width=34) (actual time=1,504.329..1,714.865 rows=5 loops=1)

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

SubPlan (for Index Scan)

6. 0.295 6.190 ↑ 1.0 1 5

Aggregate (cost=142.38..142.39 rows=1 width=5) (actual time=1.238..1.238 rows=1 loops=5)

7. 5.895 5.895 ↓ 10.7 150 5

Index Scan using payments_contract_id_idx on payments pay (cost=0.42..142.34 rows=14 width=5) (actual time=0.260..1.179 rows=150 loops=5)

  • 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: 5
8. 33.720 1,691.920 ↑ 1.0 1 5

Aggregate (cost=1,735.62..1,735.63 rows=1 width=5) (actual time=338.384..338.384 rows=1 loops=5)

9. 38.995 1,658.200 ↓ 175.6 25,114 5

Nested Loop (cost=0.84..1,735.26 rows=143 width=5) (actual time=0.339..331.640 rows=25,114 loops=5)

10. 0.230 0.230 ↓ 5.0 5 5

Index Scan using contracts_number_idx on contracts (cost=0.41..8.43 rows=1 width=17) (actual time=0.026..0.046 rows=5 loops=5)

  • Index Cond: ((number)::text = (c.number)::text)
11. 1,618.975 1,618.975 ↓ 35.1 5,023 25

Index Scan using card_moneybag_history_contract_id_idx on card_moneybag_history cmh (cost=0.43..1,725.39 rows=143 width=19) (actual time=2.155..64.759 rows=5,023 loops=25)

  • 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))
  • Rows Removed by Filter: 952
12. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8,623.02..8,623.03 rows=1 width=8) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=2.06..8,607.59 rows=2,057 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,578.21 rows=2,057 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=17) (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,549.21 rows=2,057 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. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=44,757.91..44,757.92 rows=1 width=5) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Append (cost=0.57..44,755.72 rows=877 width=5) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Index Scan using transactions_new_contract_number_transaction_date_idx on transactions_new (cost=0.57..3,075.74 rows=848 width=5) (never executed)

  • 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.000 0.000 ↓ 0.0 0

Result (cost=7,025.13..41,671.21 rows=29 width=6) (never executed)

  • 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=7,025.13..41,671.21 rows=29 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..7,025.12 rows=285,959 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.090 15.875 ↑ 1.0 1 5

Aggregate (cost=56.04..56.05 rows=1 width=7) (actual time=3.174..3.175 rows=1 loops=5)

26. 15.785 15.785 ↓ 14.0 14 5

Index Scan using invoices_consignee_contract_id_idx on invoices (cost=0.42..56.03 rows=1 width=7) (actual time=0.248..3.157 rows=14 loops=5)

  • 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: 68
Planning time : 8.077 ms