#	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

"Subquery Scan on result  (cost=55323.48..55323.52 rows=1 width=128) (actual time=267.798..267.813 rows=5 loops=1)"
"  ->  Unique  (cost=55323.48..55323.50 rows=1 width=34) (actual time=267.776..267.786 rows=5 loops=1)"
"        ->  Sort  (cost=55323.48..55323.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"
"              ->  Index Scan using contracts_number_idx on contracts c  (cost=0.41..55323.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)"
"                    SubPlan 1"
"                      ->  Aggregate  (cost=142.38..142.39 rows=1 width=5) (actual time=0.463..0.464 rows=1 loops=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"
"                    SubPlan 2"
"                      ->  Aggregate  (cost=1735.62..1735.63 rows=1 width=5) (actual time=52.789..52.789 rows=1 loops=5)"
"                            ->  Nested Loop  (cost=0.84..1735.26 rows=143 width=5) (actual time=0.084..46.867 rows=25114 loops=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)"
"                                  ->  Index Scan using card_moneybag_history_contract_id_idx on card_moneybag_history cmh  (cost=0.43..1725.39 rows=143 width=19) (actual time=0.100..7.874 rows=5023 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"
"                    SubPlan 3"
"                      ->  Aggregate  (cost=8623.02..8623.03 rows=1 width=8) (never executed)"
"                            ->  Hash Left Join  (cost=2.06..8607.59 rows=2057 width=8) (never executed)"
"                                  Hash Cond: (cou.nominal_id = son.id)"
"                                  ->  Nested Loop  (cost=0.98..8578.21 rows=2057 width=14) (never executed)"
"                                        ->  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)"
"                                        ->  Index Scan using coupons_new_contract_id_idx on coupons_new cou  (cost=0.56..8549.21 rows=2057 width=31) (never executed)"
"                                              Index Cond: ((contract_id)::text = (contracts_1.id)::text)"
"                                              Filter: (state <> 3)"
"                                  ->  Hash  (cost=1.04..1.04 rows=4 width=4) (never executed)"
"                                        ->  Seq Scan on set_of_nominal son  (cost=0.00..1.04 rows=4 width=4) (never executed)"
"                    SubPlan 4"
"                      ->  Aggregate  (cost=44757.91..44757.92 rows=1 width=5) (never executed)"
"                            ->  Append  (cost=0.57..44755.72 rows=877 width=5) (never executed)"
"                                  ->  Index Scan using transactions_new_contract_number_transaction_date_idx on transactions_new  (cost=0.57..3075.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[]))"
"                                  ->  Result  (cost=7025.13..41671.21 rows=29 width=6) (never executed)"
"                                        One-Time Filter: ((c.pricing_type = 4) AND (c.work_scheme = 1))"
"                                        ->  Bitmap Heap Scan on divided_transactions_new  (cost=7025.13..41671.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))"
"                                              ->  Bitmap Index Scan on divided_transactions_new_transaction_date_transaction_time_idx  (cost=0.00..7025.12 rows=285959 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)"
"                    SubPlan 5"
"                      ->  Aggregate  (cost=56.04..56.05 rows=1 width=7) (actual time=0.268..0.269 rows=1 loops=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"
"Execution time: 268.144 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	5	29.940 ms	11.2 %
Append	1	0.000 ms	0.0 %
Bitmap Heap Scan	1	0.000 ms	0.0 %
Bitmap Index Scan	1	0.000 ms	0.0 %
Hash	1	0.000 ms	0.0 %
Hash Left Join	1	0.000 ms	0.0 %
Index Scan	8	200.501 ms	74.9 %
Nested Loop	2	37.310 ms	13.9 %
Result	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
Sort	1	0.024 ms	0.0 %
Subquery Scan	1	0.027 ms	0.0 %
Unique	1	0.011 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
card_moneybag_history	1	196.850 ms	73.5 %
Index Scan	1	196.850 ms	100.0 %
contracts	3	0.316 ms	0.1 %
Index Scan	3	0.316 ms	100.0 %
coupons_new	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
divided_transactions_new	1	0.000 ms	0.0 %
Bitmap Heap Scan	1	0.000 ms	0.0 %
invoices	1	1.255 ms	0.5 %
Index Scan	1	1.255 ms	100.0 %
payments	1	2.080 ms	0.8 %
Index Scan	1	2.080 ms	100.0 %
set_of_nominal	1	0.000 ms	0.0 %
Seq Scan	1	0.000 ms	0.0 %
transactions_new	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ql4h

Color mode:

Visible columns:

Per node type stats

Per table stats