explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ql4h

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 267.813 ↓ 5.0 5 1

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

2. 0.011 267.786 ↓ 5.0 5 1

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

3. 0.024 267.775 ↓ 5.0 5 1

Sort (cost=55,323.48..55,323.48 rows=1 width=34) (actual time=267.775..267.775 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.141 267.751 ↓ 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=53.259..267.751 rows=5 loops=1)

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

SubPlan (for Index Scan)

6. 0.240 2.320 ↑ 1.0 1 5

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

7. 2.080 2.080 ↓ 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.042..0.416 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. 29.610 263.945 ↑ 1.0 1 5

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

9. 37.310 234.335 ↓ 175.6 25,114 5

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

10. 0.175 0.175 ↓ 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.035 rows=5 loops=5)

  • Index Cond: ((number)::text = (c.number)::text)
11. 196.850 196.850 ↓ 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=0.100..7.874 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 1.345 ↑ 1.0 1 5

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

26. 1.255 1.255 ↓ 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.056..0.251 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 : 2.331 ms