query-plan-1171

Change label
Query Plan as raw text
Summary
Time Node
Node (ms) Percent
10594.355 100.00% 
 Limit (cost=22459.82..22462.12 rows=10 width=330) (actual time=10346.176..10594.355 rows=10 loops=1)
10594.280 100.00% 
  -> Subquery Scan application_list_archive (cost=22459.82..23123.37 rows=2885 width=330) (actual time=10346.160..10594.280 rows=10 loops=1)
10199.290 96.27% 
        -> Unique (cost=22459.82..23080.1 rows=2885 width=1368) (actual time=10198.871..10199.290 rows=10 loops=1)
10198.976 96.27% 
              -> Sort (cost=22459.82..22467.04 rows=2885 width=1368) (actual time=10198.861..10198.976 rows=10 loops=1)
                    Sort Key: name, first_name, last_name, last_name, first_name, application_id, creator_user_id, inspector_user_id, parent_application_id, product_name, application_type, status, xsus_status, avg_monthly_income, currency, currency_rate, euro_rate, application_source, organization_id, shop_id, vendor, vendor_shop, tariff_id, tariff_downpayment, tariff_downpayment_type, tariff_monthly_payment, price, "?column?", tariff_loan_term, time_submit, time_decision, change_status, product_type_id, product_type_name, application_id, person_id, first_name, last_name, customer_entity, guarantor_name, guarantor_phone_mobile, guarantor_id, first_name, last_name, customer_entity, customer_level, customer_iban, borrower_name, borrower_phone_mobile, guarantor_id, person_id, registration_number, customer_signed, time_last_update, waiting_time, decision_time, time_to_yes, agreement_sign_time, "?column?", inspector_id, agreement_number, agreement_number_rdlg, retailer_name, export_status, export_status_rdlg, export_flag, priority, documents_signed, documents_printed, documents_incomplete_reason, code, origination_shop_name, origination_shop_address, origination_shop_phone, origination_shop_registration, vendor_shop_name, vendor_shop_phone, vendor_shop_registration, civil_status, empl_business_sector, empl_seniority, gender, phone_home, referrer_name, referrer_registration
8357.329 78.88% 
                    -> Append (cost=2970.58..21131.37 rows=2885 width=1368) (actual time=1747.679..8357.329 rows=4944 loops=1)
1955.317 18.46% 
                          -> Hash Left Join (cost=2970.58..4285.4 rows=1092 width=1368) (actual time=1747.668..1955.317 rows=1535 loops=1)
                                Hash Cond: ("outer".referrer_id = "inner".id)
1714.909 16.19% 
                                -> Hash Left Join (cost=2959.2..4104.76 rows=1092 width=722) (actual time=1661.676..1714.909 rows=1535 loops=1)
                                      Hash Cond: ("outer".vendor_shop = "inner".shop_id)
1645.725 15.53% 
                                      -> Hash Left Join (cost=2732.66..3858.24 rows=1092 width=692) (actual time=1603.656..1645.725 rows=1535 loops=1)
                                            Hash Cond: ("outer".shop_id = "inner".shop_id)
1518.839 14.34% 
                                            -> Merge Right Join (cost=2506.13..3601.67 rows=1092 width=620) (actual time=1493.304..1518.839 rows=1535 loops=1)
                                                  Merge Cond: ("outer".id = "inner".campaign_id)
390.129 3.68% 
                                                  -> Index Scan using application_campaign_id_key on application_campaign (cost=0.0..1009.66 rows=32147 width=16) (actual time=37.006..390.129 rows=32110 loops=1)
1023.126 9.66% 
                                                  -> Sort (cost=2506.13..2508.86 rows=1092 width=612) (actual time=1019.481..1023.126 rows=1535 loops=1)
                                                        Sort Key: application_base.campaign_id
990.421 9.35% 
                                                        -> Hash Left Join (cost=2199.3..2451.02 rows=1092 width=612) (actual time=942.676..990.421 rows=1535 loops=1)
                                                              Hash Cond: ("outer".vendor = "inner".organization_id)
951.276 8.98% 
                                                              -> Hash Left Join (cost=2093.95..2333.88 rows=1092 width=575) (actual time=913.360..951.276 rows=1535 loops=1)
                                                                    Hash Cond: ("outer".product_type_id = "inner".product_type_id)
927.102 8.75% 
                                                                    -> Merge Right Join (cost=2092.72..2321.41 rows=1092 width=551) (actual time=898.590..927.102 rows=1535 loops=1)
                                                                          Merge Cond: ("outer".parent_application_id = "inner".application_id)
54.927 0.52% 
                                                                          -> Index Scan using application__parent_application_id_index on application guarantor_app (cost=0.0..2022.47 rows=5521 width=98) (actual time=24.411..54.927 rows=613 loops=1)
850.262 8.03% 
                                                                          -> Sort (cost=2092.72..2095.45 rows=1092 width=461) (actual time=847.204..850.262 rows=1535 loops=1)
                                                                                Sort Key: application_base.application_id
814.932 7.69% 
                                                                                -> Merge Left Join (cost=1995.71..2037.62 rows=1092 width=461) (actual time=764.443..814.932 rows=1535 loops=1)
                                                                                      Merge Cond: ("outer".creator_user_id = "inner".user_id)
671.799 6.34% 
                                                                                      -> Sort (cost=1412.07..1414.8 rows=1092 width=445) (actual time=668.937..671.799 rows=1535 loops=1)
                                                                                            Sort Key: application_base.creator_user_id
636.132 6.00% 
                                                                                            -> Merge Left Join (cost=1316.56..1356.96 rows=1092 width=445) (actual time=587.416..636.132 rows=1535 loops=1)
                                                                                                  Merge Cond: ("outer".inspector_user_id = "inner".user_id)
352.533 3.33% 
                                                                                                  -> Sort (cost=732.92..735.65 rows=1092 width=425) (actual time=349.610..352.533 rows=1535 loops=1)
                                                                                                        Sort Key: application_base.inspector_user_id
314.351 2.97% 
                                                                                                        -> Hash Left Join (cost=116.95..677.82 rows=1092 width=425) (actual time=91.417..314.351 rows=1535 loops=1)
                                                                                                              Hash Cond: ("outer".organization_id = "inner".organization_id)
216.848 2.05% 
                                                                                                              -> Bitmap Heap Scan on application application_base (cost=11.6..550.63 rows=1092 width=388) (actual time=20.297..216.848 rows=1535 loops=1)
                                                                                                                    Recheck Cond: (application_type = 2::smallint)
                                                                                                                    Filter: ((application_type <> 10) AND (status <> 160) AND (time_submit >= '2008-09-29 00:00:00'::timestamp without time zone) AND (time_submit <= '2008-10-29 23:59:59'::timestamp without time zone))
9.484 0.09% 
                                                                                                                    -> Bitmap Index Scan on application__application_type_index (cost=0.0..11.6 rows=1601 width=0) (actual time=9.484..9.484 rows=1601 loops=1)
                                                                                                                          Index Cond: (application_type = 2::smallint)
70.840 0.67% 
                                                                                                              -> Hash (cost=98.48..98.48 rows=2748 width=41) (actual time=70.840..70.840 rows=2748 loops=1)
58.079 0.55% 
                                                                                                                    -> Seq Scan on retailer organization (cost=0.0..98.48 rows=2748 width=41) (actual time=8.925..58.079 rows=2748 loops=1)
248.671 2.35% 
                                                                                                  -> Sort (cost=583.64..596.44 rows=5120 width=28) (actual time=237.733..248.671 rows=5943 loops=1)
                                                                                                        Sort Key: inspector.user_id
161.409 1.52% 
                                                                                                        -> Seq Scan on system_user inspector (cost=0.0..268.2 rows=5120 width=28) (actual time=24.810..161.409 rows=5120 loops=1)
107.528 1.01% 
                                                                                      -> Sort (cost=583.64..596.44 rows=5120 width=24) (actual time=95.417..107.528 rows=6403 loops=1)
                                                                                            Sort Key: creator.user_id
23.326 0.22% 
                                                                                            -> Seq Scan on system_user creator (cost=0.0..268.2 rows=5120 width=24) (actual time=0.030..23.326 rows=5120 loops=1)
14.680 0.14% 
                                                                    -> Hash (cost=1.18..1.18 rows=18 width=28) (actual time=14.680..14.680 rows=18 loops=1)
14.595 0.14% 
                                                                          -> Seq Scan on product_type (cost=0.0..1.18 rows=18 width=28) (actual time=14.527..14.595 rows=18 loops=1)
29.223 0.28% 
                                                              -> Hash (cost=98.48..98.48 rows=2748 width=41) (actual time=29.223..29.223 rows=2748 loops=1)
16.312 0.15% 
                                                                    -> Seq Scan on retailer vendor (cost=0.0..98.48 rows=2748 width=41) (actual time=0.089..16.312 rows=2748 loops=1)
110.181 1.04% 
                                            -> Hash (cost=210.43..210.43 rows=6443 width=76) (actual time=110.181..110.181 rows=6443 loops=1)
74.325 0.70% 
                                                  -> Seq Scan on shop s (cost=0.0..210.43 rows=6443 width=76) (actual time=8.159..74.325 rows=6443 loops=1)
57.879 0.55% 
                                      -> Hash (cost=210.43..210.43 rows=6443 width=34) (actual time=57.879..57.879 rows=6443 loops=1)
28.921 0.27% 
                                            -> Seq Scan on shop vs (cost=0.0..210.43 rows=6443 width=34) (actual time=0.021..28.921 rows=6443 loops=1)
0.009 0.00% 
                                -> Hash (cost=11.1..11.1 rows=110 width=654) (actual time=0.009..0.009 rows=0 loops=1)
0.005 0.00% 
                                      -> Seq Scan on referrer_legal r (cost=0.0..11.1 rows=110 width=654) (actual time=0.005..0.005 rows=0 loops=1)
6377.925 60.20% 
                          -> Hash Left Join (cost=15354.68..16817.12 rows=1793 width=1356) (actual time=5863.268..6377.925 rows=3409 loops=1)
                                Hash Cond: ("outer".referrer_id = "inner".id)
5998.534 56.62% 
                                -> Hash Left Join (cost=15343.31..16527.82 rows=1793 width=710) (actual time=5862.722..5998.534 rows=3409 loops=1)
                                      Hash Cond: ("outer".vendor_shop = "inner".shop_id)
5916.872 55.85% 
                                      -> Hash Left Join (cost=15116.77..16265.13 rows=1793 width=680) (actual time=5805.971..5916.872 rows=3409 loops=1)
                                            Hash Cond: ("outer".shop_id = "inner".shop_id)
5806.299 54.81% 
                                            -> Merge Right Join (cost=14890.23..15989.28 rows=1793 width=608) (actual time=5732.818..5806.299 rows=3409 loops=1)
                                                  Merge Cond: ("outer".id = "inner".campaign_id)
160.116 1.51% 
                                                  -> Index Scan using application_campaign_id_key on application_campaign (cost=0.0..1009.66 rows=32147 width=16) (actual time=0.246..160.116 rows=32147 loops=1)
5517.323 52.08% 
                                                  -> Sort (cost=14890.23..14894.72 rows=1793 width=600) (actual time=5490.961..5517.323 rows=3409 loops=1)
                                                        Sort Key: application_base.campaign_id
5371.230 50.70% 
                                                        -> Hash Left Join (cost=6064.44..14513.4 rows=1793 width=600) (actual time=5124.513..5371.230 rows=3409 loops=1)
                                                              Hash Cond: ("outer".vendor = "inner".organization_id)
5318.911 50.21% 
                                                              -> Hash Left Join (cost=5959.09..14385.34 rows=1793 width=563) (actual time=5095.624..5318.911 rows=3409 loops=1)
                                                                    Hash Cond: ("outer".product_type_id = "inner".product_type_id)
5296.219 49.99% 
                                                                    -> Nested Loop Left Join (cost=5957.86..14368.11 rows=1793 width=539) (actual time=5095.318..5296.219 rows=3409 loops=1)
5153.948 48.65% 
                                                                          -> Merge Left Join (cost=5957.86..6010.12 rows=1793 width=453) (actual time=5054.132..5153.948 rows=3409 loops=1)
                                                                                Merge Cond: ("outer".creator_user_id = "inner".user_id)
4980.488 47.01% 
                                                                                -> Sort (cost=5374.22..5378.7 rows=1793 width=437) (actual time=4959.123..4980.488 rows=3409 loops=1)
                                                                                      Sort Key: application_base.creator_user_id
4819.451 45.49% 
                                                                                      -> Merge Left Join (cost=5229.73..5277.32 rows=1793 width=437) (actual time=4726.943..4819.451 rows=3409 loops=1)
                                                                                            Merge Cond: ("outer".inspector_user_id = "inner".user_id)
4632.368 43.72% 
                                                                                            -> Sort (cost=4646.09..4650.58 rows=1793 width=417) (actual time=4612.491..4632.368 rows=3409 loops=1)
                                                                                                  Sort Key: application_base.inspector_user_id
4433.717 41.85% 
                                                                                                  -> Hash Left Join (cost=105.35..4549.2 rows=1793 width=417) (actual time=80.061..4433.717 rows=3409 loops=1)
                                                                                                        Hash Cond: ("outer".organization_id = "inner".organization_id)
4329.540 40.87% 
                                                                                                        -> Index Scan using application_archive__time_submit_index on application_archive application_base (cost=0.0..4407.99 rows=1793 width=380) (actual time=51.351..4329.540 rows=3409 loops=1)
                                                                                                              Index Cond: ((time_submit >= '2008-09-29 00:00:00'::timestamp without time zone) AND (time_submit <= '2008-10-29 23:59:59'::timestamp without time zone))
                                                                                                              Filter: ((application_type <> 10) AND (status <> 160) AND (application_type = 2::smallint))
28.569 0.27% 
                                                                                                        -> Hash (cost=98.48..98.48 rows=2748 width=41) (actual time=28.569..28.569 rows=2748 loops=1)
15.703 0.15% 
                                                                                                              -> Seq Scan on retailer organization (cost=0.0..98.48 rows=2748 width=41) (actual time=0.071..15.703 rows=2748 loops=1)
129.188 1.22% 
                                                                                            -> Sort (cost=583.64..596.44 rows=5120 width=28) (actual time=114.378..129.188 rows=7853 loops=1)
                                                                                                  Sort Key: inspector.user_id
37.936 0.36% 
                                                                                                  -> Seq Scan on system_user inspector (cost=0.0..268.2 rows=5120 width=28) (actual time=0.146..37.936 rows=5120 loops=1)
110.896 1.05% 
                                                                                -> Sort (cost=583.64..596.44 rows=5120 width=24) (actual time=94.916..110.896 rows=8249 loops=1)
                                                                                      Sort Key: creator.user_id
23.230 0.22% 
                                                                                      -> Seq Scan on system_user creator (cost=0.0..268.2 rows=5120 width=24) (actual time=0.035..23.230 rows=5120 loops=1)
105.679 1.00% 
                                                                          -> Index Scan using application_archive__parent_application_id_index on application_archive guarantor_app (cost=0.0..4.65 rows=1 width=94) (actual time=0.031..0.031 rows=0 loops=3409)
                                                                                Index Cond: ("outer".application_id = guarantor_app.parent_application_id)
0.237 0.00% 
                                                                    -> Hash (cost=1.18..1.18 rows=18 width=28) (actual time=0.237..0.237 rows=18 loops=1)
0.159 0.00% 
                                                                          -> Seq Scan on product_type (cost=0.0..1.18 rows=18 width=28) (actual time=0.094..0.159 rows=18 loops=1)
28.809 0.27% 
                                                              -> Hash (cost=98.48..98.48 rows=2748 width=41) (actual time=28.809..28.809 rows=2748 loops=1)
15.766 0.15% 
                                                                    -> Seq Scan on retailer vendor (cost=0.0..98.48 rows=2748 width=41) (actual time=0.077..15.766 rows=2748 loops=1)
72.952 0.69% 
                                            -> Hash (cost=210.43..210.43 rows=6443 width=76) (actual time=72.952..72.952 rows=6443 loops=1)
37.704 0.36% 
                                                  -> Seq Scan on shop s (cost=0.0..210.43 rows=6443 width=76) (actual time=0.136..37.704 rows=6443 loops=1)
56.620 0.53% 
                                      -> Hash (cost=210.43..210.43 rows=6443 width=34) (actual time=56.620..56.620 rows=6443 loops=1)
28.106 0.27% 
                                            -> Seq Scan on shop vs (cost=0.0..210.43 rows=6443 width=34) (actual time=0.018..28.106 rows=6443 loops=1)
0.013 0.00% 
                                -> Hash (cost=11.1..11.1 rows=110 width=654) (actual time=0.013..0.013 rows=0 loops=1)
0.006 0.00% 
                                      -> Seq Scan on referrer_legal r (cost=0.0..11.1 rows=110 width=654) (actual time=0.006..0.006 rows=0 loops=1)
Run-time
10.624707 seconds (10624.707 ms)