#	exclusive	inclusive	rows x	rows	loops	node
1.	29.020	6,528.316	↓ 0.0	0	1	→ Nested Loop (cost=437,470.95..495,758.10 rows=277 width=88) (actual time=6,528.316..6,528.316 rows=0 loops=1) Join Filter: ((COALESCE(ah_credit.gl_transaction_type_id, 0) <> COALESCE(ah_charge.gl_transaction_type_id, '-1'::integer)) AND (aa.cid = ah_charge.cid)) Rows Removed by Join Filter: 19942
2.						CTE ap_details_ad
3.	190.750	2,072.072	↑ 19.0	10	1	→ Group (cost=317,964.79..318,184.77 rows=190 width=8) (actual time=1,705.618..2,072.072 rows=10 loops=1) Group Key: ad.ap_gl_account_id, ad.cid
4.	405.553	1,881.322	↓ 16.0	468,461	1	→ Sort (cost=317,964.79..318,038.12 rows=29,330 width=8) (actual time=1,705.615..1,881.322 rows=468,461 loops=1) Sort Key: ad.ap_gl_account_id, ad.cid Sort Method: quicksort Memory: 34248kB
5.	1,309.028	1,475.769	↓ 16.0	468,461	1	→ Bitmap Heap Scan on ap_details ad (cost=11,906.86..315,788.49 rows=29,330 width=8) (actual time=172.776..1,475.769 rows=468,461 loops=1) Recheck Cond: (cid = ANY ('{3190,10292,14562,10348,4785,2547,12235,11335,15024,7007}'::integer[])) Filter: ((ap_gl_account_id IS NOT NULL) AND (deleted_by IS NULL) AND (deleted_on IS NULL)) Rows Removed by Filter: 178066 Heap Blocks: exact=36262
6.	166.741	166.741	↑ 1.0	646,527	1	→ Bitmap Index Scan on idx_ap_details_cid_post_month (cost=0.00..11,899.52 rows=654,295 width=0) (actual time=166.741..166.741 rows=646,527 loops=1) Index Cond: (cid = ANY ('{3190,10292,14562,10348,4785,2547,12235,11335,15024,7007}'::integer[]))
7.	255.624	6,439.278	↓ 72.0	20,006	1	→ Nested Loop (cost=119,285.75..176,490.23 rows=278 width=104) (actual time=1,503.047..6,439.278 rows=20,006 loops=1) Join Filter: ((aa.cid = ah_credit.cid) AND ((aa.accrual_debit_gl_account_id IS NULL) OR (aa.accrual_credit_gl_account_id IS NULL) OR (aa.cash_debit_gl_account_id IS NULL) OR (aa.cash_credit_gl_account_id IS NULL) OR (aa.accrual_debit_gl_account_id = aa.accrual_credit_gl_account_id) OR ((ah_credit.gl_transaction_type_id = 3) AND (aa.origin_ap_allocation_id IS NULL) AND (NOT (hashed SubPlan 2))) OR ((ah_credit.gl_transaction_type_id = 2) AND (aa.origin_ap_allocation_id IS NULL) AND (NOT (hashed SubPlan 3))) OR (aa.is_posted AND aa.is_deleted AND (aa.origin_ap_allocation_id IS NOT NULL) AND (((ah_credit.gl_transaction_type_id = 2) AND (NOT (hashed SubPlan 4))) OR ((ah_credit.gl_transaction_type_id = 3) AND (NOT (hashed SubPlan 5))))))) Rows Removed by Join Filter: 187131
8.	404.131	2,020.019	↓ 745.1	207,137	1	→ Nested Loop (cost=8.59..56,122.75 rows=278 width=106) (actual time=32.715..2,020.019 rows=207,137 loops=1)
9.	358.933	1,201.614	↓ 745.1	207,137	1	→ Nested Loop (cost=8.16..54,692.27 rows=278 width=82) (actual time=32.708..1,201.614 rows=207,137 loops=1)
10.	173.983	428.407	↓ 745.1	207,137	1	→ Nested Loop (cost=7.72..53,262.48 rows=278 width=74) (actual time=32.699..428.407 rows=207,137 loops=1)
11.	32.376	32.376	↓ 1.9	96	1	→ Function Scan on load_properties lp_y (cost=0.25..0.75 rows=50 width=8) (actual time=32.315..32.376 rows=96 loops=1)
12.	208.224	222.048	↓ 126.9	2,158	96	→ Bitmap Heap Scan on ap_allocations aa (cost=7.47..1,065.06 rows=17 width=70) (actual time=0.198..2.313 rows=2,158 loops=96) Recheck Cond: ((cid = lp_y.cid) AND (property_id = lp_y.property_id)) Filter: (NOT is_initial_import) Rows Removed by Filter: 0 Heap Blocks: exact=44411
13.	13.824	13.824	↓ 7.1	2,158	96	→ Bitmap Index Scan on idx_ap_allocations_cid_property_id (cost=0.00..7.47 rows=304 width=0) (actual time=0.144..0.144 rows=2,158 loops=96) Index Cond: ((cid = lp_y.cid) AND (property_id = lp_y.property_id))
14.	414.274	414.274	↑ 1.0	1	207,137	→ Index Scan using idx_ap_details on ap_details ad_credit (cost=0.43..5.13 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=207,137) Index Cond: (id = aa.credit_ap_detail_id) Filter: (aa.cid = cid) Rows Removed by Filter: 0
15.	414.274	414.274	↑ 1.0	1	207,137	→ Index Scan using idx_ap_details on ap_details ad_charge (cost=0.43..5.14 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=207,137) Index Cond: (id = aa.charge_ap_detail_id) Filter: ((ap_transaction_type_id <> 2) AND (aa.cid = cid)) Rows Removed by Filter: 0
16.	621.411	621.411	↑ 1.0	1	207,137	→ Index Scan using idx_ap_headers on ap_headers ah_credit (cost=0.43..3.88 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=207,137) Index Cond: (id = ad_credit.ap_header_id) Filter: (ad_credit.cid = cid) Rows Removed by Filter: 0
17.						SubPlan (forNested Loop)
18.	170.606	1,470.137	↑ 36.6	15	1	→ Group (cost=119,255.67..119,262.53 rows=549 width=16) (actual time=1,148.342..1,470.137 rows=15 loops=1) Group Key: ad_1.ap_gl_account_id, ad_1.inter_co_ap_gl_account_id, ad_1.ap_transaction_type_id
19.	382.727	1,299.531	↓ 741.6	407,119	1	→ Sort (cost=119,255.67..119,257.04 rows=549 width=12) (actual time=1,148.339..1,299.531 rows=407,119 loops=1) Sort Key: ad_1.ap_gl_account_id, ad_1.inter_co_ap_gl_account_id, ad_1.ap_transaction_type_id Sort Method: quicksort Memory: 31372kB
20.	302.625	916.804	↓ 741.6	407,119	1	→ Nested Loop (cost=15.14..119,230.69 rows=549 width=12) (actual time=29.337..916.804 rows=407,119 loops=1)
21.	28.387	28.387	↓ 1.9	96	1	→ Function Scan on load_properties lp_x (cost=0.25..0.75 rows=50 width=8) (actual time=28.333..28.387 rows=96 loops=1)
22.	553.632	585.792	↓ 146.2	4,241	96	→ Bitmap Heap Scan on ap_details ad_1 (cost=14.89..2,384.31 rows=29 width=20) (actual time=0.537..6.102 rows=4,241 loops=96) Recheck Cond: ((cid = lp_x.cid) AND (property_id = lp_x.property_id)) Filter: ((ap_gl_account_id IS NOT NULL) AND (deleted_by IS NULL) AND (deleted_on IS NULL)) Rows Removed by Filter: 71 Heap Blocks: exact=152053
23.	32.160	32.160	↓ 6.7	4,312	96	→ Bitmap Index Scan on idx_ap_details_cid_property_id (cost=0.00..14.88 rows=645 width=0) (actual time=0.335..0.335 rows=4,312 loops=96) Index Cond: ((cid = lp_x.cid) AND (property_id = lp_x.property_id))
24.	0.000	0.000	↓ 0.0		0	→ CTE Scan on ap_details_ad (cost=0.00..3.80 rows=190 width=8) (never executed)
25.	0.000	0.000	↓ 0.0		0	→ CTE Scan on ap_details_ad ap_details_ad_1 (cost=0.00..3.80 rows=190 width=8) (never executed)
26.	2,072.087	2,072.087	↑ 19.0	10	1	→ CTE Scan on ap_details_ad ap_details_ad_2 (cost=0.00..3.80 rows=190 width=8) (actual time=1,705.622..2,072.087 rows=10 loops=1)
27.	60.018	60.018	↑ 1.0	1	20,006	→ Index Scan using idx_ap_headers on ap_headers ah_charge (cost=0.43..3.88 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=20,006) Index Cond: (id = ad_charge.ap_header_id) Filter: ((NOT is_initial_import) AND (ad_charge.cid = cid)) Rows Removed by Filter: 0

"QUERY PLAN"
"Nested Loop  (cost=437470.95..495758.10 rows=277 width=88) (actual time=6528.316..6528.316 rows=0 loops=1)"
"  Join Filter: ((COALESCE(ah_credit.gl_transaction_type_id, 0) <> COALESCE(ah_charge.gl_transaction_type_id, '-1'::integer)) AND (aa.cid = ah_charge.cid))"
"  Rows Removed by Join Filter: 19942"
"  CTE ap_details_ad"
"    ->  Group  (cost=317964.79..318184.77 rows=190 width=8) (actual time=1705.618..2072.072 rows=10 loops=1)"
"          Group Key: ad.ap_gl_account_id, ad.cid"
"          ->  Sort  (cost=317964.79..318038.12 rows=29330 width=8) (actual time=1705.615..1881.322 rows=468461 loops=1)"
"                Sort Key: ad.ap_gl_account_id, ad.cid"
"                Sort Method: quicksort  Memory: 34248kB"
"                ->  Bitmap Heap Scan on ap_details ad  (cost=11906.86..315788.49 rows=29330 width=8) (actual time=172.776..1475.769 rows=468461 loops=1)"
"                      Recheck Cond: (cid = ANY ('{3190,10292,14562,10348,4785,2547,12235,11335,15024,7007}'::integer[]))"
"                      Filter: ((ap_gl_account_id IS NOT NULL) AND (deleted_by IS NULL) AND (deleted_on IS NULL))"
"                      Rows Removed by Filter: 178066"
"                      Heap Blocks: exact=36262"
"                      ->  Bitmap Index Scan on idx_ap_details_cid_post_month  (cost=0.00..11899.52 rows=654295 width=0) (actual time=166.741..166.741 rows=646527 loops=1)"
"                            Index Cond: (cid = ANY ('{3190,10292,14562,10348,4785,2547,12235,11335,15024,7007}'::integer[]))"
"  ->  Nested Loop  (cost=119285.75..176490.23 rows=278 width=104) (actual time=1503.047..6439.278 rows=20006 loops=1)"
"        Join Filter: ((aa.cid = ah_credit.cid) AND ((aa.accrual_debit_gl_account_id IS NULL) OR (aa.accrual_credit_gl_account_id IS NULL) OR (aa.cash_debit_gl_account_id IS NULL) OR (aa.cash_credit_gl_account_id IS NULL) OR (aa.accrual_debit_gl_account_id = aa.accrual_credit_gl_account_id) OR ((ah_credit.gl_transaction_type_id = 3) AND (aa.origin_ap_allocation_id IS NULL) AND (NOT (hashed SubPlan 2))) OR ((ah_credit.gl_transaction_type_id = 2) AND (aa.origin_ap_allocation_id IS NULL) AND (NOT (hashed SubPlan 3))) OR (aa.is_posted AND aa.is_deleted AND (aa.origin_ap_allocation_id IS NOT NULL) AND (((ah_credit.gl_transaction_type_id = 2) AND (NOT (hashed SubPlan 4))) OR ((ah_credit.gl_transaction_type_id = 3) AND (NOT (hashed SubPlan 5)))))))"
"        Rows Removed by Join Filter: 187131"
"        ->  Nested Loop  (cost=8.59..56122.75 rows=278 width=106) (actual time=32.715..2020.019 rows=207137 loops=1)"
"              ->  Nested Loop  (cost=8.16..54692.27 rows=278 width=82) (actual time=32.708..1201.614 rows=207137 loops=1)"
"                    ->  Nested Loop  (cost=7.72..53262.48 rows=278 width=74) (actual time=32.699..428.407 rows=207137 loops=1)"
"                          ->  Function Scan on load_properties lp_y  (cost=0.25..0.75 rows=50 width=8) (actual time=32.315..32.376 rows=96 loops=1)"
"                          ->  Bitmap Heap Scan on ap_allocations aa  (cost=7.47..1065.06 rows=17 width=70) (actual time=0.198..2.313 rows=2158 loops=96)"
"                                Recheck Cond: ((cid = lp_y.cid) AND (property_id = lp_y.property_id))"
"                                Filter: (NOT is_initial_import)"
"                                Rows Removed by Filter: 0"
"                                Heap Blocks: exact=44411"
"                                ->  Bitmap Index Scan on idx_ap_allocations_cid_property_id  (cost=0.00..7.47 rows=304 width=0) (actual time=0.144..0.144 rows=2158 loops=96)"
"                                      Index Cond: ((cid = lp_y.cid) AND (property_id = lp_y.property_id))"
"                    ->  Index Scan using idx_ap_details on ap_details ad_credit  (cost=0.43..5.13 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=207137)"
"                          Index Cond: (id = aa.credit_ap_detail_id)"
"                          Filter: (aa.cid = cid)"
"                          Rows Removed by Filter: 0"
"              ->  Index Scan using idx_ap_details on ap_details ad_charge  (cost=0.43..5.14 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=207137)"
"                    Index Cond: (id = aa.charge_ap_detail_id)"
"                    Filter: ((ap_transaction_type_id <> 2) AND (aa.cid = cid))"
"                    Rows Removed by Filter: 0"
"        ->  Index Scan using idx_ap_headers on ap_headers ah_credit  (cost=0.43..3.88 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=207137)"
"              Index Cond: (id = ad_credit.ap_header_id)"
"              Filter: (ad_credit.cid = cid)"
"              Rows Removed by Filter: 0"
"        SubPlan 2"
"          ->  Group  (cost=119255.67..119262.53 rows=549 width=16) (actual time=1148.342..1470.137 rows=15 loops=1)"
"                Group Key: ad_1.ap_gl_account_id, ad_1.inter_co_ap_gl_account_id, ad_1.ap_transaction_type_id"
"                ->  Sort  (cost=119255.67..119257.04 rows=549 width=12) (actual time=1148.339..1299.531 rows=407119 loops=1)"
"                      Sort Key: ad_1.ap_gl_account_id, ad_1.inter_co_ap_gl_account_id, ad_1.ap_transaction_type_id"
"                      Sort Method: quicksort  Memory: 31372kB"
"                      ->  Nested Loop  (cost=15.14..119230.69 rows=549 width=12) (actual time=29.337..916.804 rows=407119 loops=1)"
"                            ->  Function Scan on load_properties lp_x  (cost=0.25..0.75 rows=50 width=8) (actual time=28.333..28.387 rows=96 loops=1)"
"                            ->  Bitmap Heap Scan on ap_details ad_1  (cost=14.89..2384.31 rows=29 width=20) (actual time=0.537..6.102 rows=4241 loops=96)"
"                                  Recheck Cond: ((cid = lp_x.cid) AND (property_id = lp_x.property_id))"
"                                  Filter: ((ap_gl_account_id IS NOT NULL) AND (deleted_by IS NULL) AND (deleted_on IS NULL))"
"                                  Rows Removed by Filter: 71"
"                                  Heap Blocks: exact=152053"
"                                  ->  Bitmap Index Scan on idx_ap_details_cid_property_id  (cost=0.00..14.88 rows=645 width=0) (actual time=0.335..0.335 rows=4312 loops=96)"
"                                        Index Cond: ((cid = lp_x.cid) AND (property_id = lp_x.property_id))"
"        SubPlan 3"
"          ->  CTE Scan on ap_details_ad  (cost=0.00..3.80 rows=190 width=8) (never executed)"
"        SubPlan 4"
"          ->  CTE Scan on ap_details_ad ap_details_ad_1  (cost=0.00..3.80 rows=190 width=8) (never executed)"
"        SubPlan 5"
"          ->  CTE Scan on ap_details_ad ap_details_ad_2  (cost=0.00..3.80 rows=190 width=8) (actual time=1705.622..2072.087 rows=10 loops=1)"
"  ->  Index Scan using idx_ap_headers on ap_headers ah_charge  (cost=0.43..3.88 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=20006)"
"        Index Cond: (id = ad_charge.ap_header_id)"
"        Filter: ((NOT is_initial_import) AND (ad_charge.cid = cid))"
"        Rows Removed by Filter: 0"
"Planning time: 11.336 ms"
"Execution time: 6530.232 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	3	2,070.884 ms	31.7 %
Bitmap Index Scan	3	212.725 ms	3.3 %
CTE Scan	3	2,072.087 ms	31.7 %
Function Scan	2	60.763 ms	0.9 %
Group	2	361.356 ms	5.5 %
Index Scan	4	1,509.977 ms	23.1 %
Nested Loop	6	1,524.316 ms	23.3 %
Sort	2	788.280 ms	12.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
ap_allocations	1	208.224 ms	3.2 %
Bitmap Heap Scan	1	208.224 ms	100.0 %
ap_details	4	2,691.208 ms	41.2 %
Bitmap Heap Scan	2	1,862.660 ms	69.2 %
Index Scan	2	828.548 ms	30.8 %
ap_headers	2	681.429 ms	10.4 %
Index Scan	2	681.429 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BTrs

Color mode:

Visible columns:

Per node type stats

Per table stats