#	exclusive	inclusive	rows x	rows	loops	node
1.	155.735	14476.388	↑ 1.2	4	1	→ Merge Join (cost=82.52..494708.51 rows=5 width=3108) (actual time=11573.868..14476.388 rows=4 loops=1) Merge Cond: (applicant.id = aa.applicant_id)
2.	1138.716	14320.567	↑ 1.0	599961	1	→ Merge Left Join (cost=45.59..493159.39 rows=604845 width=3108) (actual time=0.118..14320.567 rows=599961 loops=1) Merge Cond: (applicant.id = current_address_contact.applicant_id)
3.	1097.011	12078.964	↑ 1.0	599961	1	→ Merge Left Join (cost=37.65..410539.75 rows=602331 width=3050) (actual time=0.108..12078.964 rows=599961 loops=1) Merge Cond: (applicant.id = secondary_vehicle.applicant_id)
4.	1452.379	9978.522	↑ 1.0	599961	1	→ Merge Left Join (cost=31.46..349738.66 rows=601124 width=2976) (actual time=0.096..9978.522 rows=599961 loops=1) Merge Cond: (applicant.id = detail.applicant_id) Join Filter: (detail.management_company_id = applicant.management_company_id)
5.	998.874	7962.095	↑ 1.0	599961	1	→ Merge Left Join (cost=27.54..307505.64 rows=601124 width=1128) (actual time=0.081..7962.095 rows=599961 loops=1) Merge Cond: (applicant.id = current_address.applicant_id)
6.	869.608	6014.121	↑ 1.0	599961	1	→ Merge Left Join (cost=20.86..245204.60 rows=601124 width=987) (actual time=0.070..6014.121 rows=599961 loops=1) Merge Cond: (applicant.id = previous_address_contact.applicant_id)
7.	895.868	4306.451	↑ 1.0	599961	1	→ Merge Left Join (cost=12.85..162812.89 rows=601124 width=929) (actual time=0.054..4306.451 rows=599961 loops=1) Merge Cond: (applicant.id = previous_address.applicant_id)
8.	1266.874	2652.705	↑ 1.0	599961	1	→ Merge Left Join (cost=6.21..100488.46 rows=601124 width=788) (actual time=0.038..2652.705 rows=599961 loops=1) Merge Cond: (applicant.id = primary_vehicle.applicant_id)
9.	661.053	661.053	↑ 1.0	599961	1	→ Index Scan using pk_applicants on applicants applicant (cost=0.00..39716.03 rows=601124 width=714) (actual time=0.016..661.053 rows=599961 loops=1)
10.	724.778	724.778	↑ 1.0	599961	1	→ Index Scan using idx_applicant_vehicles_applicant_id on applicant_vehicles primary_vehicle (cost=0.00..51769.15 rows=600038 width=78) (actual time=0.015..724.778 rows=599961 loops=1) Filter: (primary_vehicle.customer_vehicle_type_id = 1)
11.	757.878	757.878	↑ 1.0	599961	1	→ Index Scan using idx_applicant_addresses_applicant_id on applicant_addresses previous_address (cost=0.00..53313.86 rows=602686 width=145) (actual time=0.014..757.878 rows=599961 loops=1) Filter: (previous_address.address_type_id = 9)
12.	838.062	838.062	↓ 1.0	599961	1	→ Index Scan using idx_applicant_contacts_applicant_id on applicant_contacts previous_address_contact (cost=0.00..73418.04 rows=598640 width=62) (actual time=0.014..838.062 rows=599961 loops=1) Filter: (previous_address_contact.customer_contact_type_id = 2)
13.	949.100	949.100	↓ 1.0	599961	1	→ Index Scan using idx_applicant_addresses_applicant_id on applicant_addresses current_address (cost=0.00..53313.86 rows=599561 width=145) (actual time=0.008..949.100 rows=599961 loops=1) Filter: (current_address.address_type_id = 12)
14.	564.048	564.048	↑ 1.0	600715	1	→ Index Scan using idx_applicant_details_applicant_id on applicant_details detail (cost=0.00..31711.47 rows=601879 width=1856) (actual time=0.011..564.048 rows=600715 loops=1)
15.	1003.431	1003.431	↑ 1.0	599961	1	→ Index Scan using idx_applicant_vehicles_applicant_id on applicant_vehicles secondary_vehicle (cost=0.00..51769.15 rows=602331 width=78) (actual time=0.010..1003.431 rows=599961 loops=1) Filter: (secondary_vehicle.customer_vehicle_type_id = 2)
16.	1102.887	1102.887	↑ 1.0	599961	1	→ Index Scan using idx_applicant_contacts_applicant_id on applicant_contacts current_address_contact (cost=0.00..73418.04 rows=603633 width=62) (actual time=0.009..1102.887 rows=599961 loops=1) Filter: (current_address_contact.customer_contact_type_id = 1)
17.	0.025	0.086	↑ 1.2	4	1	→ Sort (cost=36.93..36.94 rows=5 width=4) (actual time=0.081..0.086 rows=4 loops=1) Sort Key: aa.applicant_id Sort Method: quicksort Memory: 25kB
18.	0.016	0.061	↑ 1.2	4	1	→ Bitmap Heap Scan on applicant_applications aa (cost=17.10..36.87 rows=5 width=4) (actual time=0.053..0.061 rows=4 loops=1) Recheck Cond: (application_id = ANY ('{1254504,1254503,1125307,1254391}'::integer[])) Filter: (lease_customer_type_id = 1)
19.	0.045	0.045	↑ 1.2	4	1	→ Bitmap Index Scan on idx_applicant_applications_application_id (cost=0.00..17.10 rows=5 width=0) (actual time=0.045..0.045 rows=4 loops=1) Index Cond: (application_id = ANY ('{1254504,1254503,1125307,1254391}'::integer[]))

QUERY PLAN                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=82.52..494708.51 rows=5 width=3108) (actual time=11573.868..14476.388 rows=4 loops=1)
   Merge Cond: (applicant.id = aa.applicant_id)
   ->  Merge Left Join  (cost=45.59..493159.39 rows=604845 width=3108) (actual time=0.118..14320.567 rows=599961 loops=1)
         Merge Cond: (applicant.id = current_address_contact.applicant_id)
         ->  Merge Left Join  (cost=37.65..410539.75 rows=602331 width=3050) (actual time=0.108..12078.964 rows=599961 loops=1)
               Merge Cond: (applicant.id = secondary_vehicle.applicant_id)
               ->  Merge Left Join  (cost=31.46..349738.66 rows=601124 width=2976) (actual time=0.096..9978.522 rows=599961 loops=1)
                     Merge Cond: (applicant.id = detail.applicant_id)
                     Join Filter: (detail.management_company_id = applicant.management_company_id)
                     ->  Merge Left Join  (cost=27.54..307505.64 rows=601124 width=1128) (actual time=0.081..7962.095 rows=599961 loops=1)
                           Merge Cond: (applicant.id = current_address.applicant_id)
                           ->  Merge Left Join  (cost=20.86..245204.60 rows=601124 width=987) (actual time=0.070..6014.121 rows=599961 loops=1)
                                 Merge Cond: (applicant.id = previous_address_contact.applicant_id)
                                 ->  Merge Left Join  (cost=12.85..162812.89 rows=601124 width=929) (actual time=0.054..4306.451 rows=599961 loops=1)
                                       Merge Cond: (applicant.id = previous_address.applicant_id)
                                       ->  Merge Left Join  (cost=6.21..100488.46 rows=601124 width=788) (actual time=0.038..2652.705 rows=599961 loops=1)
                                             Merge Cond: (applicant.id = primary_vehicle.applicant_id)
                                             ->  Index Scan using pk_applicants on applicants applicant  (cost=0.00..39716.03 rows=601124 width=714) (actual time=0.016..661.053 rows=599961 loops=1)
                                             ->  Index Scan using idx_applicant_vehicles_applicant_id on applicant_vehicles primary_vehicle  (cost=0.00..51769.15 rows=600038 width=78) (actual time=0.015..724.778 rows=599961 loops=1)
                                                   Filter: (primary_vehicle.customer_vehicle_type_id = 1)
                                       ->  Index Scan using idx_applicant_addresses_applicant_id on applicant_addresses previous_address  (cost=0.00..53313.86 rows=602686 width=145) (actual time=0.014..757.878 rows=599961 loops=1)
                                             Filter: (previous_address.address_type_id = 9)
                                 ->  Index Scan using idx_applicant_contacts_applicant_id on applicant_contacts previous_address_contact  (cost=0.00..73418.04 rows=598640 width=62) (actual time=0.014..838.062 rows=599961 loops=1)
                                       Filter: (previous_address_contact.customer_contact_type_id = 2)
                           ->  Index Scan using idx_applicant_addresses_applicant_id on applicant_addresses current_address  (cost=0.00..53313.86 rows=599561 width=145) (actual time=0.008..949.100 rows=599961 loops=1)
                                 Filter: (current_address.address_type_id = 12)
                     ->  Index Scan using idx_applicant_details_applicant_id on applicant_details detail  (cost=0.00..31711.47 rows=601879 width=1856) (actual time=0.011..564.048 rows=600715 loops=1)
               ->  Index Scan using idx_applicant_vehicles_applicant_id on applicant_vehicles secondary_vehicle  (cost=0.00..51769.15 rows=602331 width=78) (actual time=0.010..1003.431 rows=599961 loops=1)
                     Filter: (secondary_vehicle.customer_vehicle_type_id = 2)
         ->  Index Scan using idx_applicant_contacts_applicant_id on applicant_contacts current_address_contact  (cost=0.00..73418.04 rows=603633 width=62) (actual time=0.009..1102.887 rows=599961 loops=1)
               Filter: (current_address_contact.customer_contact_type_id = 1)
   ->  Sort  (cost=36.93..36.94 rows=5 width=4) (actual time=0.081..0.086 rows=4 loops=1)
         Sort Key: aa.applicant_id
         Sort Method:  quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on applicant_applications aa  (cost=17.10..36.87 rows=5 width=4) (actual time=0.053..0.061 rows=4 loops=1)
               Recheck Cond: (application_id = ANY ('{1254504,1254503,1125307,1254391}'::integer[]))
               Filter: (lease_customer_type_id = 1)
               ->  Bitmap Index Scan on idx_applicant_applications_application_id  (cost=0.00..17.10 rows=5 width=0) (actual time=0.045..0.045 rows=4 loops=1)
                     Index Cond: (application_id = ANY ('{1254504,1254503,1125307,1254391}'::integer[]))
 Total runtime: 14477.316 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	0.016 ms	0.0 %
Bitmap Index Scan	1	0.045 ms	0.0 %
Index Scan	8	6601.237 ms	45.6 %
Merge Join	1	155.735 ms	1.1 %
Merge Left Join	7	7719.330 ms	53.3 %
Sort	1	0.025 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
applicant_addresses	2	1706.978 ms	11.8 %
Index Scan	2	1706.978 ms	100.0 %
applicant_applications	1	0.016 ms	0.0 %
Bitmap Heap Scan	1	0.016 ms	100.0 %
applicant_contacts	2	1940.949 ms	13.4 %
Index Scan	2	1940.949 ms	100.0 %
applicant_details	1	564.048 ms	3.9 %
Index Scan	1	564.048 ms	100.0 %
applicant_vehicles	2	1728.209 ms	11.9 %
Index Scan	2	1728.209 ms	100.0 %
applicants	1	661.053 ms	4.6 %
Index Scan	1	661.053 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: bGJ

Colorize:

Columns visibility:

Per node type stats

Per table stats