Thread: Bad plan
Hello all,
So I have a view, for which I can select all rows in about 3s (returns ~80k rows), but if I add a where clause on a column, it takes +300s to return the ~8k lines.
From the plan, I see that it expects to return only 1 row and so choose to perform some nested loops. Of course, I did run "ANALYZE", but with no success. So I have a view, for which I can select all rows in about 3s (returns ~80k rows), but if I add a where clause on a column, it takes +300s to return the ~8k lines.
CREATE VIEW export_contract_par_region AS
SELECT
contractLine.id as id_contrat,
partner.id as id_partner,
partner.name,
title.name AS contact_civ,
mc.name AS contact_nom,
mc.first_name AS contact_prenom,
(CASE WHEN is_physique(partner.person_category_select) THEN coalesce(mc.email,mc.email_pro) ELSE coalesce(mc.email_pro,mc.email) END) AS contact_email,
(CASE WHEN is_physique(partner.person_category_select)
THEN concat_ws('/',mc.fixed_phone1,mc.mobile_phone_perso)
ELSE concat_ws('/',mc.fixed_phone_pro,mc.mobile_phone_pro) END) AS contact_phones,
adr_contact.addressl2 AS contact_addressl2,
adr_contact.addressl3 AS contact_addressl3,
adr_contact.addressl4num AS contact_addressl4num,
adr_contact.addressl4street AS contact_addressl4street,
adr_contact.addressl5 AS contact_addressl5,
adr_contact.addressl6zip AS contact_addressl6zip,
adr_contact.addressl6city AS contact_addressl6city,
coalesce(npai.moved_ok,false) AS npai,
coalesce(mc.address,mc.address_pro) IS NULL AS sans_adresse,
amendment.user_sub_segment_select as type_select,
UserSegment.code as user_segment,
contractLine.real_start_date AS date_mise_en_service,
to_char(contractLine.real_start_date,'YYYY/MM') AS datemes_yyyymm,
(ws.created_on::date) AS date_souscription,
status.name AS statut,
power.first AS subscribed_power,
a.addressl2 AS pdl_addressl2,
a.addressl3 AS pdl_addressl3,
a.addressl4num AS pdl_addressl4num,
a.addressl4street AS pdl_addressl4street,
a.addressl5 AS pdl_addressl5,
a.addressl6zip AS pdl_adressel6zip,
a.addressl6city AS pdl_adressel6city,
a.dept AS pdl_code_dept,
a.dept_name AS pdl_nom_dept,
a.region_code AS pdl_code_region,
a.region AS pdl_nom_region,
businessProvider.business_provider_code AS codeCoop,
soc.soc AS company_societaire,
co.code AS connu_enercoop,
ClientNature.name as segment_client,
to_char(ws.created_on,'YYYY') as annee_souscription,
to_char(ws.created_on,'MM') as mois_souscription,
mesProductSubFamily.name as type_mes
FROM contract_contract_line contractLine
JOIN contract_contract contract on contractLine.contract = contract.id
JOIN contact_partner partner on partner.id = contract.main_client_partner
JOIN contact_partner businessProvider on businessProvider.id = contractLine.business_provider_partner
LEFT JOIN contact_client_nature ClientNature on ClientNature.id = partner.client_nature
JOIN contract_amendment amendment on contractLine.amendment = amendment.id
JOIN territory_mpt mpt on contractLine.mpt = mpt.id
LEFT JOIN subscribed_power power ON power.amendment = amendment.id
LEFT JOIN contract_user_segment UserSegment ON UserSegment.id = amendment.user_segment
LEFT JOIN contact_company company on company.id = contract.company
LEFT JOIN address a on mpt.address = a.id
LEFT JOIN administration_status status ON status.id = contractLine.status
LEFT JOIN shareholder_summary soc ON soc.partner = partner.id
LEFT JOIN shareholder_web_subscription ws ON ws.contract_line = contractLine.id
LEFT JOIN crm_origin co ON co.id = ws.how_meet_enercoop
LEFT JOIN contact_contact mc ON partner.main_contact = mc.id
LEFT JOIN contact_title title ON mc.title = title.id
LEFT JOIN contact_address adr_contact ON adr_contact.id = (CASE WHEN is_physique(partner.person_category_select) THEN coalesce(mc.address,mc.address_pro) ELSE coalesce(mc.address_pro,mc.address) END)
LEFT JOIN contact_contact_address cca ON cca.contact = mc.id AND cca.address = adr_contact.id
LEFT JOIN contact_contact_address_status npai ON cca.contact_address_status = npai.id
LEFT JOIN crm_crm_request mesRequest ON mesRequest.original_contract_line = contractLine.id
LEFT JOIN sale_product_sub_family mesProductSubFamily ON mesProductSubFamily.id = mesRequest.product_sub_family AND mesProductSubFamily.new_contract_ok is true
ORDER BY subscribed_power DESC, statut,id_contrat;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=39200.76..39200.76 rows=1 width=1066) (actual time=341273.300..341274.244 rows=7359 loops=1)
Sort Key: ((array_agg(subscribed_power.subscribed_power))[1]) DESC, status.name, contractline.id
Sort Method: quicksort Memory: 3930kB
-> Nested Loop Left Join (cost=32069.19..39200.75 rows=1 width=1066) (actual time=342.806..341203.151 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32069.05..39200.50 rows=1 width=508) (actual time=342.784..341102.848 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32068.77..39200.20 rows=1 width=500) (actual time=342.778..341070.310 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32068.64..39200.04 rows=1 width=507) (actual time=342.776..341058.256 rows=7359 loops=1)
Join Filter: (cca.address = adr_contact.id)
Rows Removed by Join Filter: 2254
-> Nested Loop Left Join (cost=32068.22..39199.55 rows=1 width=515) (actual time=342.767..340997.058 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32067.79..39198.84 rows=1 width=447) (actual time=342.753..340932.286 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32067.65..39198.67 rows=1 width=421) (actual time=342.748..340896.132 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32067.23..39198.01 rows=1 width=279) (actual time=342.739..340821.987 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual time=342.725..340775.031 rows=7359 loops=1)
Join Filter: (sh.share_holder_partner = partner.id)
Rows Removed by Join Filter: 204915707
-> Nested Loop Left Join (cost=28514.61..34092.46 rows=1 width=244) (actual time=287.323..610.192 rows=7359 loops=1)
-> Nested Loop Left Join (cost=28514.47..34092.30 rows=1 width=239) (actual time=287.318..573.234 rows=7359 loops=1)
-> Hash Right Join (cost=28513.48..34090.65 rows=1 width=159) (actual time=287.293..379.564 rows=7359 loops=1)
Hash Cond: (ws.contract_line = contractline.id)
-> Seq Scan on shareholder_web_subscription ws (cost=0.00..5378.84 rows=52884 width=24) (actual time=0.006..12.307 rows=52884 loops=1)
-> Hash (cost=28513.47..28513.47 rows=1 width=143) (actual time=287.243..287.243 rows=7359 loops=1)
Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1173kB
-> Nested Loop Left Join (cost=17456.16..28513.47 rows=1 width=143) (actual time=85.005..284.689 rows=7359 loops=1)
-> Nested Loop (cost=17456.03..28513.31 rows=1 width=148) (actual time=85.000..276.599 rows=7359 loops=1)
-> Nested Loop Left Join (cost=17455.73..28512.84 rows=1 width=148) (actual time=84.993..261.954 rows=7359 loops=1)
-> Nested Loop (cost=17455.60..28512.67 rows=1 width=140) (actual time=84.989..253.715 rows=7359 loops=1)
-> Nested Loop (cost=17455.18..28511.93 rows=1 width=93) (actual time=84.981..230.977 rows=7359 loops=1)
-> Merge Right Join (cost=17454.89..28511.52 rows=1 width=93) (actual time=84.974..211.200 rows=7359 loops=1)
Merge Cond: (subscribed_power.amendment = amendment.id)
-> GroupAggregate (cost=12457.78..22574.03 rows=75229 width=168) (actual time=57.500..175.674 rows=83432 loops=1)
Group Key: subscribed_power.amendment
-> Merge Join (cost=12457.78..20764.08 rows=173917 width=12) (actual time=57.479..129.530 rows=87938 loops=1)
Merge Cond: (subscribed_power.amendment = amendment_1.id)
-> Index Scan using contract_subscribed_power_amendment_idx on contract_subscribed_power subscribed_power (cost=0.42..13523.09 rows=173917 width=12) (actual time=0.009..33.704 rows=87963 loops=1)
-> Sort (cost=12457.36..12666.43 rows=83629 width=8) (actual time=57.467..67.071 rows=88019 loops=1)
Sort Key: amendment_1.id
Sort Method: quicksort Memory: 6988kB
-> Hash Join (cost=10.21..5619.97 rows=83629 width=8) (actual time=0.112..40.965 rows=83532 loops=1)
Hash Cond: (amendment_1.pricing = pricing.id)
-> Seq Scan on contract_amendment amendment_1 (cost=0.00..4460.29 rows=83629 width=16) (actual time=0.004..6.988 rows=83629 loops=1)
-> Hash (cost=8.43..8.43 rows=142 width=8) (actual time=0.095..0.095 rows=141 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 14kB
-> Hash Join (cost=1.07..8.43 rows=142 width=8) (actual time=0.012..0.078 rows=141 loops=1)
Hash Cond: (pricing.elec_range = elec_range.id)
-> Seq Scan on pricing_pricing pricing (cost=0.00..5.42 rows=142 width=16) (actual time=0.003..0.015 rows=142 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.006..0.006 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fluid_elec_range elec_range (cost=0.00..1.03 rows=3 width=8) (actual time=0.003..0.005 rows=3 loops=1)
-> Sort (cost=4997.11..4997.11 rows=1 width=69) (actual time=27.427..28.896 rows=7359 loops=1)
Sort Key: amendment.id
Sort Method: quicksort Memory: 1227kB
-> Nested Loop (cost=183.44..4997.10 rows=1 width=69) (actual time=1.115..24.616 rows=7359 loops=1)
-> Nested Loop (cost=183.15..4996.59 rows=1 width=49) (actual time=1.107..9.091 rows=7360 loops=1)
-> Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on contact_partner businessprovider (cost=0.42..8.44 rows=1 width=13) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: ((business_provider_code)::text = 'BRZH'::text)
-> Bitmap Heap Scan on contract_contract_line contractline (cost=182.73..4907.58 rows=8057 width=52) (actual time=1.086..5.231 rows=7360 loops=1)
Recheck Cond: (business_provider_partner = businessprovider.id)
Heap Blocks: exact=3586
-> Bitmap Index Scan on contract_contract_line_business_provider_partner_idx (cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655 rows=7360 loops=1)
Index Cond: (business_provider_partner = businessprovider.id)
-> Index Scan using contract_amendment_pkey on contract_amendment amendment (cost=0.29..0.50 rows=1 width=28) (actual time=0.001..0.002 rows=1 loops=7360)
Index Cond: (id = contractline.amendment)
-> Index Scan using contract_contract_pkey on contract_contract contract (cost=0.29..0.40 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=7359)
Index Cond: (id = contractline.contract)
-> Index Scan using contact_partner_pkey on contact_partner partner (cost=0.42..0.74 rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=7359)
Index Cond: (id = contract.main_client_partner)
-> Index Scan using contact_client_nature_pkey on contact_client_nature clientnature (cost=0.14..0.15 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=7359)
Index Cond: (id = partner.client_nature)
-> Index Scan using territory_mpt_pkey on territory_mpt mpt (cost=0.29..0.46 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=7359)
Index Cond: (id = contractline.mpt)
-> Index Scan using contract_user_segment_pkey on contract_user_segment usersegment (cost=0.14..0.15 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=7359)
Index Cond: (id = amendment.user_segment)
-> Nested Loop Left Join (cost=0.99..1.64 rows=1 width=96) (actual time=0.021..0.025 rows=1 loops=7359)
-> Nested Loop Left Join (cost=0.85..1.35 rows=1 width=89) (actual time=0.017..0.020 rows=1 loops=7359)
-> Nested Loop Left Join (cost=0.71..1.18 rows=1 width=76) (actual time=0.013..0.014 rows=1 loops=7359)
-> Index Scan using contact_address_pkey on contact_address a (cost=0.42..0.85 rows=1 width=84) (actual time=0.005..0.006 rows=1 loops=7359)
Index Cond: (mpt.address = id)
-> Index Scan using territory_commune_pkey on territory_commune commune (cost=0.29..0.32 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=7359)
Index Cond: (a.commune = id)
-> Index Scan using territory_department_pkey on territory_department dept (cost=0.14..0.16 rows=1 width=37) (actual time=0.003..0.004 rows=1 loops=7359)
Index Cond: (commune.department = id)
-> Index Scan using territory_region_pkey on territory_region reg (cost=0.14..0.27 rows=1 width=23) (actual time=0.003..0.003 rows=1 loops=7359)
Index Cond: (dept.region = id)
-> Index Scan using administration_status_pkey on administration_status status (cost=0.14..0.16 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=7359)
Index Cond: (id = contractline.status)
-> GroupAggregate (cost=3552.48..4479.27 rows=27827 width=80) (actual time=0.006..44.205 rows=27846 loops=7359)
Group Key: sh.share_holder_partner
-> Sort (cost=3552.48..3624.85 rows=28948 width=17) (actual time=0.003..2.913 rows=28946 loops=7359)
Sort Key: sh.share_holder_partner
Sort Method: quicksort Memory: 3030kB
-> Hash Join (cost=2.23..1407.26 rows=28948 width=17) (actual time=0.024..12.296 rows=28946 loops=1)
Hash Cond: (sh.company = sh_coop.id)
-> Seq Scan on shareholder_share_holder sh (cost=0.00..1007.00 rows=28948 width=20) (actual time=0.007..5.495 rows=28946 loops=1)
Filter: (nb_share > 0)
Rows Removed by Filter: 1934
-> Hash (cost=2.10..2.10 rows=10 width=13) (actual time=0.009..0.009 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on contact_company sh_coop (cost=0.00..2.10 rows=10 width=13) (actual time=0.003..0.006 rows=10 loops=1)
-> Index Scan using crm_origin_pkey on crm_origin co (cost=0.14..0.16 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=7359)
Index Cond: (id = ws.how_meet_enercoop)
-> Index Scan using contact_contact_pkey on contact_contact mc (cost=0.42..0.65 rows=1 width=150) (actual time=0.007..0.008 rows=1 loops=7359)
Index Cond: (partner.main_contact = id)
-> Index Scan using contact_title_pkey on contact_title title (cost=0.14..0.16 rows=1 width=42) (actual time=0.003..0.003 rows=1 loops=7359)
Index Cond: (mc.title = id)
-> Index Scan using contact_address_pkey on contact_address adr_contact (cost=0.43..0.70 rows=1 width=68) (actual time=0.005..0.005 rows=1 loops=7359)
Index Cond: (id = CASE WHEN (CASE WHEN ((partner.person_category_select)::text = 'naturalPerson'::text) THEN 'P'::text WHEN ((partner.person_category_select)::text = 'legalPerson'::text) THEN 'M'::text ELSE '?????'::text END = 'P'::text) THEN COALESCE(mc.address, mc.address_pro) ELSE COALESCE(mc.address_pro, mc.address) END)
-> Index Scan using contact_contact_address_contact_idx on contact_contact_address cca (cost=0.42..0.48 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=7359)
Index Cond: (contact = mc.id)
-> Index Scan using contact_contact_address_status_pkey on contact_contact_address_status npai (cost=0.13..0.15 rows=1 width=9) (actual time=0.000..0.000 rows=0 loops=7359)
Index Cond: (cca.contact_address_status = id)
-> Index Scan using crm_crm_request_original_contract_line_idx on crm_crm_request mesrequest (cost=0.28..0.29 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=7359)
Index Cond: (original_contract_line = contractline.id)
-> Index Scan using sale_product_sub_family_pkey on sale_product_sub_family mesproductsubfamily (cost=0.14..0.20 rows=1 width=62) (actual time=0.000..0.000 rows=0 loops=7359)
Index Cond: (id = mesrequest.product_sub_family)
Filter: (new_contract_ok IS TRUE)
Planning time: 21.106 ms
Execution time: 341275.027 ms
(118 lignes)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=144636.25..144837.81 rows=80627 width=1066)
Sort Key: ((array_agg(subscribed_power.subscribed_power))[1]) DESC, status.name, contractline.id
-> Hash Left Join (cost=130533.89..138065.56 rows=80627 width=1066)
Hash Cond: (cca.contact_address_status = npai.id)
-> Hash Right Join (cost=130532.78..135132.88 rows=80627 width=561)
Hash Cond: ((cca.contact = mc.id) AND (cca.address = adr_contact.id))
-> Seq Scan on contact_contact_address cca (cost=0.00..3424.05 rows=156805 width=24)
-> Hash (cost=129323.37..129323.37 rows=80627 width=569)
-> Hash Left Join (cost=127873.96..129323.37 rows=80627 width=569)
Hash Cond: (CASE WHEN (CASE WHEN ((partner.person_category_select)::text = 'naturalPerson'::text) THEN 'P'::text WHEN ((partner.person_category_select)::text = 'legalPerson'::text) THEN 'M'::text ELSE '?????'::text END = 'P'::text) THEN COALESCE(mc.address, mc.address_pro) ELSE COALESCE(mc.address_pro, mc.address) END = adr_contact.id)
-> Hash Right Join (cost=114435.97..114474.41 rows=80627 width=501)
Hash Cond: (mesrequest.original_contract_line = contractline.id)
-> Hash Left Join (cost=7.49..43.37 rows=681 width=62)
Hash Cond: (mesrequest.product_sub_family = mesproductsubfamily.id)
-> Seq Scan on crm_crm_request mesrequest (cost=0.00..32.81 rows=681 width=16)
-> Hash (cost=7.28..7.28 rows=17 width=62)
-> Seq Scan on sale_product_sub_family mesproductsubfamily (cost=0.00..7.28 rows=17 width=62)
Filter: (new_contract_ok IS TRUE)
-> Hash (cost=113420.64..113420.64 rows=80627 width=447)
-> Hash Left Join (cost=98148.14..113420.64 rows=80627 width=447)
Hash Cond: (mc.title = title.id)
-> Hash Left Join (cost=98145.72..112484.37 rows=80627 width=421)
Hash Cond: (contractline.status = status.id)
-> Hash Left Join (cost=98143.30..111373.33 rows=80627 width=416)
Hash Cond: (mpt.address = a.id)
-> Hash Left Join (cost=79299.88..91422.10 rows=80627 width=336)
Hash Cond: (contractline.id = ws.contract_line)
-> Hash Right Join (cost=72530.89..83867.87 rows=80627 width=317)
Hash Cond: (mc.id = partner.main_contact)
-> Seq Scan on contact_contact mc (cost=0.00..8524.65 rows=229265 width=150)
-> Hash (cost=71523.05..71523.05 rows=80627 width=175)
-> Hash Right Join (cost=70040.37..71523.05 rows=80627 width=175)
Hash Cond: (sh.share_holder_partner = partner.id)
-> GroupAggregate (cost=3552.48..4479.27 rows=27827 width=80)
Group Key: sh.share_holder_partner
-> Sort (cost=3552.48..3624.85 rows=28948 width=17)
Sort Key: sh.share_holder_partner
-> Hash Join (cost=2.23..1407.26 rows=28948 width=17)
Hash Cond: (sh.company = sh_coop.id)
-> Seq Scan on shareholder_share_holder sh (cost=0.00..1007.00 rows=28948 width=20)
Filter: (nb_share > 0)
-> Hash (cost=2.10..2.10 rows=10 width=13)
-> Seq Scan on contact_company sh_coop (cost=0.00..2.10 rows=10 width=13)
-> Hash (cost=65480.05..65480.05 rows=80627 width=143)
-> Hash Left Join (cost=47310.33..65480.05 rows=80627 width=143)
Hash Cond: (amendment.user_segment = usersegment.id)
-> Hash Join (cost=47309.02..64370.12 rows=80627 width=148)
Hash Cond: (contractline.mpt = mpt.id)
-> Hash Left Join (cost=42733.67..58686.26 rows=80627 width=148)
Hash Cond: (partner.client_nature = clientnature.id)
-> Hash Join (cost=42732.36..57971.72 rows=80627 width=140)
Hash Cond: (contractline.business_provider_partner = businessprovider.id)
-> Hash Join (cost=35201.74..49333.07 rows=80627 width=143)
Hash Cond: (contractline.contract = contract.id)
-> Hash Join (cost=24290.54..37313.25 rows=80627 width=96)
Hash Cond: (amendment.id = contractline.amendment)
-> Hash Right Join (cost=17963.43..29866.37 rows=83629 width=60)
Hash Cond: (subscribed_power.amendment = amendment.id)
-> GroupAggregate (cost=12457.78..22574.03 rows=75229 width=168)
Group Key: subscribed_power.amendment
-> Merge Join (cost=12457.78..20764.08 rows=173917 width=12)
Merge Cond: (subscribed_power.amendment = amendment_1.id)
-> Index Scan using contract_subscribed_power_amendment_idx on contract_subscribed_power subscribed_power (cost=0.42..13523.09 rows=173917 width=12)
-> Sort (cost=12457.36..12666.43 rows=83629 width=8)
Sort Key: amendment_1.id
-> Hash Join (cost=10.21..5619.97 rows=83629 width=8)
Hash Cond: (amendment_1.pricing = pricing.id)
-> Seq Scan on contract_amendment amendment_1 (cost=0.00..4460.29 rows=83629 width=16)
-> Hash (cost=8.43..8.43 rows=142 width=8)
-> Hash Join (cost=1.07..8.43 rows=142 width=8)
Hash Cond: (pricing.elec_range = elec_range.id)
-> Seq Scan on pricing_pricing pricing (cost=0.00..5.42 rows=142 width=16)
-> Hash (cost=1.03..1.03 rows=3 width=8)
-> Seq Scan on fluid_elec_range elec_range (cost=0.00..1.03 rows=3 width=8)
-> Hash (cost=4460.29..4460.29 rows=83629 width=28)
-> Seq Scan on contract_amendment amendment (cost=0.00..4460.29 rows=83629 width=28)
-> Hash (cost=5319.27..5319.27 rows=80627 width=52)
-> Seq Scan on contract_contract_line contractline (cost=0.00..5319.27 rows=80627 width=52)
-> Hash (cost=10091.85..10091.85 rows=65548 width=63)
-> Hash Join (cost=3038.83..10091.85 rows=65548 width=63)
Hash Cond: (partner.id = contract.main_client_partner)
-> Seq Scan on contact_partner partner (cost=0.00..5911.94 rows=129494 width=55)
-> Hash (cost=2219.48..2219.48 rows=65548 width=24)
-> Seq Scan on contract_contract contract (cost=0.00..2219.48 rows=65548 width=24)
-> Hash (cost=5911.94..5911.94 rows=129494 width=13)
-> Seq Scan on contact_partner businessprovider (cost=0.00..5911.94 rows=129494 width=13)
-> Hash (cost=1.14..1.14 rows=14 width=24)
-> Seq Scan on contact_client_nature clientnature (cost=0.00..1.14 rows=14 width=24)
-> Hash (cost=3602.93..3602.93 rows=77793 width=16)
-> Seq Scan on territory_mpt mpt (cost=0.00..3602.93 rows=77793 width=16)
-> Hash (cost=1.14..1.14 rows=14 width=11)
-> Seq Scan on contract_user_segment usersegment (cost=0.00..1.14 rows=14 width=11)
-> Hash (cost=6107.94..6107.94 rows=52884 width=27)
-> Hash Left Join (cost=1.94..6107.94 rows=52884 width=27)
Hash Cond: (ws.how_meet_enercoop = co.id)
-> Seq Scan on shareholder_web_subscription ws (cost=0.00..5378.84 rows=52884 width=24)
-> Hash (cost=1.42..1.42 rows=42 width=19)
-> Seq Scan on crm_origin co (cost=0.00..1.42 rows=42 width=19)
-> Hash (cost=15431.77..15431.77 rows=272933 width=96)
-> Hash Left Join (cost=2101.31..15431.77 rows=272933 width=96)
Hash Cond: (a.commune = commune.id)
-> Seq Scan on contact_address a (cost=0.00..10026.33 rows=272933 width=84)
-> Hash (cost=1641.83..1641.83 rows=36758 width=36)
-> Hash Left Join (cost=7.27..1641.83 rows=36758 width=36)
Hash Cond: (commune.department = dept.id)
-> Seq Scan on territory_commune commune (cost=0.00..1129.58 rows=36758 width=16)
-> Hash (cost=6.01..6.01 rows=101 width=36)
-> Hash Left Join (cost=1.61..6.01 rows=101 width=36)
Hash Cond: (dept.region = reg.id)
-> Seq Scan on territory_department dept (cost=0.00..3.01 rows=101 width=37)
-> Hash (cost=1.27..1.27 rows=27 width=23)
-> Seq Scan on territory_region reg (cost=0.00..1.27 rows=27 width=23)
-> Hash (cost=1.63..1.63 rows=63 width=21)
-> Seq Scan on administration_status status (cost=0.00..1.63 rows=63 width=21)
-> Hash (cost=1.63..1.63 rows=63 width=42)
-> Seq Scan on contact_title title (cost=0.00..1.63 rows=63 width=42)
-> Hash (cost=10026.33..10026.33 rows=272933 width=68)
-> Seq Scan on contact_address adr_contact (cost=0.00..10026.33 rows=272933 width=68)
-> Hash (cost=1.05..1.05 rows=5 width=9)
-> Seq Scan on contact_contact_address_status npai (cost=0.00..1.05 rows=5 width=9)
(120 lignes)
On Tue, Jan 23, 2018 at 01:03:49PM +0100, Laurent Martelli wrote: > Hello all, > > So I have a view, for which I can select all rows in about 3s (returns ~80k > rows), but if I add a where clause on a column, it takes +300s to return > the ~8k lines. > > From the plan, I see that it expects to return only 1 row and so choose to > perform some nested loops. Of course, I did run "ANALYZE", but with no > success. > > I managed to speed things up with "set enable_nestloop = false;", but is > that the only choice I have ? Should I report a bug ? > Here is the default plan : Can you resend without line breaks or paste a link to explain.depesz? The problem appears to be here: -> Nested Loop Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual time=342.725..340775.031 rows=7359 loops=1) Join Filter: (sh.share_holder_partner = partner.id) Rows Removed by Join Filter: 204915707 Justin
2018-01-23 16:18 GMT+01:00 Justin Pryzby <pryzby@telsasoft.com>:
> On Tue, Jan 23, 2018 at 01:03:49PM +0100, Laurent Martelli wrote:
>
>> Here is the default plan :
>
> Can you resend without line breaks or paste a link to explain.depesz?
I hope it's better like that. I've attached it too, just in case.
>
> The problem appears to be here:
>
> -> Nested Loop Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual time=342.725..340775.031 rows=7359
loops=1)
> Join Filter: (sh.share_holder_partner = partner.id)
> Rows Removed by Join Filter: 204915707
>
> Justin
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=39200.76..39200.76 rows=1 width=1066) (actual
time=341273.300..341274.244 rows=7359 loops=1)
Sort Key: ((array_agg(subscribed_power.subscribed_power))[1]) DESC,
status.name, contractline.id
Sort Method: quicksort Memory: 3930kB
-> Nested Loop Left Join (cost=32069.19..39200.75 rows=1
width=1066) (actual time=342.806..341203.151 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32069.05..39200.50 rows=1
width=508) (actual time=342.784..341102.848 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32068.77..39200.20
rows=1 width=500) (actual time=342.778..341070.310 rows=7359 loops=1)
-> Nested Loop Left Join
(cost=32068.64..39200.04 rows=1 width=507) (actual
time=342.776..341058.256 rows=7359 loops=1)
Join Filter: (cca.address = adr_contact.id)
Rows Removed by Join Filter: 2254
-> Nested Loop Left Join
(cost=32068.22..39199.55 rows=1 width=515) (actual
time=342.767..340997.058 rows=7359 loops=1)
-> Nested Loop Left Join
(cost=32067.79..39198.84 rows=1 width=447) (actual
time=342.753..340932.286 rows=7359 loops=1)
-> Nested Loop Left Join
(cost=32067.65..39198.67 rows=1 width=421) (actual
time=342.748..340896.132 rows=7359 loops=1)
-> Nested Loop Left Join
(cost=32067.23..39198.01 rows=1 width=279) (actual
time=342.739..340821.987 rows=7359 loops=1)
-> Nested Loop
Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual
time=342.725..340775.031 rows=7359 loops=1)
Join Filter:
(sh.share_holder_partner = partner.id)
Rows Removed
by Join Filter: 204915707
-> Nested
Loop Left Join (cost=28514.61..34092.46 rows=1 width=244) (actual
time=287.323..610.192 rows=7359 loops=1)
->
Nested Loop Left Join (cost=28514.47..34092.30 rows=1 width=239)
(actual time=287.318..573.234 rows=7359 loops=1)
-> Hash Right Join (cost=28513.48..34090.65 rows=1 width=159)
(actual time=287.293..379.564 rows=7359 loops=1)
Hash Cond: (ws.contract_line = contractline.id)
-> Seq Scan on shareholder_web_subscription ws
(cost=0.00..5378.84 rows=52884 width=24) (actual time=0.006..12.307
rows=52884 loops=1)
-> Hash (cost=28513.47..28513.47 rows=1 width=143) (actual
time=287.243..287.243 rows=7359 loops=1)
Buckets: 8192 (originally 1024) Batches: 1 (originally 1)
Memory Usage: 1173kB
-> Nested Loop Left Join (cost=17456.16..28513.47 rows=1
width=143) (actual time=85.005..284.689 rows=7359 loops=1)
-> Nested Loop (cost=17456.03..28513.31 rows=1
width=148) (actual time=85.000..276.599 rows=7359 loops=1)
-> Nested Loop Left Join
(cost=17455.73..28512.84 rows=1 width=148) (actual
time=84.993..261.954 rows=7359 loops=1)
-> Nested Loop (cost=17455.60..28512.67
rows=1 width=140) (actual time=84.989..253.715 rows=7359 loops=1)
-> Nested Loop
(cost=17455.18..28511.93 rows=1 width=93) (actual time=84.981..230.977
rows=7359 loops=1)
-> Merge Right Join
(cost=17454.89..28511.52 rows=1 width=93) (actual time=84.974..211.200
rows=7359 loops=1)
Merge Cond:
(subscribed_power.amendment = amendment.id)
-> GroupAggregate
(cost=12457.78..22574.03 rows=75229 width=168) (actual
time=57.500..175.674 rows=83432 loops=1)
Group Key:
subscribed_power.amendment
-> Merge Join
(cost=12457.78..20764.08 rows=173917 width=12) (actual
time=57.479..129.530 rows=87938 loops=1)
Merge Cond:
(subscribed_power.amendment = amendment_1.id)
-> Index
Scan using contract_subscribed_power_amendment_idx on
contract_subscribed_power subscribed_power (cost=0.42..13523.09
rows=173917 width=12) (actual time=0.009..33.704 rows=87963 loops=1)
-> Sort
(cost=12457.36..12666.43 rows=83629 width=8) (actual
time=57.467..67.071 rows=88019 loops=1)
Sort
Key: amendment_1.id
Sort
Method: quicksort Memory: 6988kB
->
Hash Join (cost=10.21..5619.97 rows=83629 width=8) (actual
time=0.112..40.965 rows=83532 loops=1)
Hash Cond: (amendment_1.pricing = pricing.id)
-> Seq Scan on contract_amendment amendment_1 (cost=0.00..4460.29
rows=83629 width=16) (actual time=0.004..6.988 rows=83629 loops=1)
-> Hash (cost=8.43..8.43 rows=142 width=8) (actual time=0.095..0.095
rows=141 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 14kB
-> Hash Join (cost=1.07..8.43 rows=142 width=8) (actual
time=0.012..0.078 rows=141 loops=1)
Hash Cond: (pricing.elec_range = elec_range.id)
-> Seq Scan on pricing_pricing pricing (cost=0.00..5.42
rows=142 width=16) (actual time=0.003..0.015 rows=142 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual
time=0.006..0.006 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fluid_elec_range elec_range
(cost=0.00..1.03 rows=3 width=8) (actual time=0.003..0.005 rows=3
loops=1)
-> Sort
(cost=4997.11..4997.11 rows=1 width=69) (actual time=27.427..28.896
rows=7359 loops=1)
Sort Key:
amendment.id
Sort Method:
quicksort Memory: 1227kB
-> Nested Loop
(cost=183.44..4997.10 rows=1 width=69) (actual time=1.115..24.616
rows=7359 loops=1)
-> Nested
Loop (cost=183.15..4996.59 rows=1 width=49) (actual time=1.107..9.091
rows=7360 loops=1)
->
Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on contact_partner
businessprovider (cost=0.42..8.44 rows=1 width=13) (actual
time=0.010..0.010 rows=1 loops=1)
Index Cond: ((business_provider_code)::text = 'BRZH'::text)
->
Bitmap Heap Scan on contract_contract_line contractline
(cost=182.73..4907.58 rows=8057 width=52) (actual time=1.086..5.231
rows=7360 loops=1)
Recheck Cond: (business_provider_partner = businessprovider.id)
Heap Blocks: exact=3586
-> Bitmap Index Scan on
contract_contract_line_business_provider_partner_idx
(cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655
rows=7360 loops=1)
Index Cond: (business_provider_partner = businessprovider.id)
-> Index
Scan using contract_amendment_pkey on contract_amendment amendment
(cost=0.29..0.50 rows=1 width=28) (actual time=0.001..0.002 rows=1
loops=7360)
Index
Cond: (id = contractline.amendment)
-> Index Scan using
contract_contract_pkey on contract_contract contract (cost=0.29..0.40
rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=7359)
Index Cond: (id =
contractline.contract)
-> Index Scan using
contact_partner_pkey on contact_partner partner (cost=0.42..0.74
rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=7359)
Index Cond: (id =
contract.main_client_partner)
-> Index Scan using
contact_client_nature_pkey on contact_client_nature clientnature
(cost=0.14..0.15 rows=1 width=24) (actual time=0.001..0.001 rows=1
loops=7359)
Index Cond: (id =
partner.client_nature)
-> Index Scan using territory_mpt_pkey on
territory_mpt mpt (cost=0.29..0.46 rows=1 width=16) (actual
time=0.001..0.001 rows=1 loops=7359)
Index Cond: (id = contractline.mpt)
-> Index Scan using contract_user_segment_pkey on
contract_user_segment usersegment (cost=0.14..0.15 rows=1 width=11)
(actual time=0.001..0.001 rows=1 loops=7359)
Index Cond: (id = amendment.user_segment)
-> Nested Loop Left Join (cost=0.99..1.64 rows=1 width=96) (actual
time=0.021..0.025 rows=1 loops=7359)
-> Nested Loop Left Join (cost=0.85..1.35 rows=1 width=89)
(actual time=0.017..0.020 rows=1 loops=7359)
-> Nested Loop Left Join (cost=0.71..1.18 rows=1 width=76)
(actual time=0.013..0.014 rows=1 loops=7359)
-> Index Scan using contact_address_pkey on
contact_address a (cost=0.42..0.85 rows=1 width=84) (actual
time=0.005..0.006 rows=1 loops=7359)
Index Cond: (mpt.address = id)
-> Index Scan using territory_commune_pkey on
territory_commune commune (cost=0.29..0.32 rows=1 width=16) (actual
time=0.005..0.006 rows=1 loops=7359)
Index Cond: (a.commune = id)
-> Index Scan using territory_department_pkey on
territory_department dept (cost=0.14..0.16 rows=1 width=37) (actual
time=0.003..0.004 rows=1 loops=7359)
Index Cond: (commune.department = id)
-> Index Scan using territory_region_pkey on territory_region reg
(cost=0.14..0.27 rows=1 width=23) (actual time=0.003..0.003 rows=1
loops=7359)
Index Cond: (dept.region = id)
->
Index Scan using administration_status_pkey on administration_status
status (cost=0.14..0.16 rows=1 width=21) (actual time=0.003..0.003
rows=1 loops=7359)
Index Cond: (id = contractline.status)
->
GroupAggregate (cost=3552.48..4479.27 rows=27827 width=80) (actual
time=0.006..44.205 rows=27846 loops=7359)
Group
Key: sh.share_holder_partner
->
Sort (cost=3552.48..3624.85 rows=28948 width=17) (actual
time=0.003..2.913 rows=28946 loops=7359)
Sort Key: sh.share_holder_partner
Sort Method: quicksort Memory: 3030kB
-> Hash Join (cost=2.23..1407.26 rows=28948 width=17) (actual
time=0.024..12.296 rows=28946 loops=1)
Hash Cond: (sh.company = sh_coop.id)
-> Seq Scan on shareholder_share_holder sh (cost=0.00..1007.00
rows=28948 width=20) (actual time=0.007..5.495 rows=28946 loops=1)
Filter: (nb_share > 0)
Rows Removed by Filter: 1934
-> Hash (cost=2.10..2.10 rows=10 width=13) (actual
time=0.009..0.009 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on contact_company sh_coop (cost=0.00..2.10
rows=10 width=13) (actual time=0.003..0.006 rows=10 loops=1)
-> Index Scan
using crm_origin_pkey on crm_origin co (cost=0.14..0.16 rows=1
width=19) (actual time=0.004..0.004 rows=1 loops=7359)
Index Cond:
(id = ws.how_meet_enercoop)
-> Index Scan using
contact_contact_pkey on contact_contact mc (cost=0.42..0.65 rows=1
width=150) (actual time=0.007..0.008 rows=1 loops=7359)
Index Cond:
(partner.main_contact = id)
-> Index Scan using
contact_title_pkey on contact_title title (cost=0.14..0.16 rows=1
width=42) (actual time=0.003..0.003 rows=1 loops=7359)
Index Cond: (mc.title = id)
-> Index Scan using
contact_address_pkey on contact_address adr_contact (cost=0.43..0.70
rows=1 width=68) (actual time=0.005..0.005 rows=1 loops=7359)
Index Cond: (id = CASE WHEN
(CASE WHEN ((partner.person_category_select)::text =
'naturalPerson'::text) THEN 'P'::text WHEN
((partner.person_category_select)::text = 'legalPerson'::text) THEN
'M'::text ELSE '?????'::text END = 'P'::text) THEN
COALESCE(mc.address, mc.address_pro) ELSE COALESCE(mc.address_pro,
mc.address) END)
-> Index Scan using
contact_contact_address_contact_idx on contact_contact_address cca
(cost=0.42..0.48 rows=1 width=24) (actual time=0.006..0.006 rows=1
loops=7359)
Index Cond: (contact = mc.id)
-> Index Scan using
contact_contact_address_status_pkey on contact_contact_address_status
npai (cost=0.13..0.15 rows=1 width=9) (actual time=0.000..0.000
rows=0 loops=7359)
Index Cond: (cca.contact_address_status = id)
-> Index Scan using
crm_crm_request_original_contract_line_idx on crm_crm_request
mesrequest (cost=0.28..0.29 rows=1 width=16) (actual
time=0.003..0.003 rows=0 loops=7359)
Index Cond: (original_contract_line = contractline.id)
-> Index Scan using sale_product_sub_family_pkey on
sale_product_sub_family mesproductsubfamily (cost=0.14..0.20 rows=1
width=62) (actual time=0.000..0.000 rows=0 loops=7359)
Index Cond: (id = mesrequest.product_sub_family)
Filter: (new_contract_ok IS TRUE)
Planning time: 21.106 ms
Execution time: 341275.027 ms
(118 lignes)
--
http://www.laurentmartelli.com // http://www.imprimart.fr
Attachment
I've have a look to the plan with pgadmin, and I think the problem is
rather here :
-> Sort (cost=4997.11..4997.11 rows=1 width=69) (actual
time=27.427..28.896 rows=7359 loops=1)
Sort Key: amendment.id
Sort Method: quicksort Memory: 1227kB
-> Nested Loop (cost=183.44..4997.10 rows=1 width=69) (actual
time=1.115..24.616 rows=7359 loops=1)
-> Nested Loop (cost=183.15..4996.59 rows=1 width=49)
(actual time=1.107..9.091 rows=7360 loops=1)
-> Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on
contact_partner businessprovider (cost=0.42..8.44 rows=1 width=13)
(actual time=0.010..0.010 rows=1 loops=1)
Index Cond: ((business_provider_code)::text =
'BRZH'::text)
-> Bitmap Heap Scan on contract_contract_line
contractline (cost=182.73..4907.58 rows=8057 width=52) (actual
time=1.086..5.231 rows=7360 loops=1)
Recheck Cond: (business_provider_partner =
businessprovider.id)
Heap Blocks: exact=3586
-> Bitmap Index Scan on
contract_contract_line_business_provider_partner_idx
(cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655
rows=7360 loops=1)
Index Cond: (business_provider_partner =
businessprovider.id)
-> Index Scan using contract_amendment_pkey on
contract_amendment amendment (cost=0.29..0.50 rows=1 width=28)
(actual time=0.001..0.002 rows=1 loops=7360)
Index Cond: (id = contractline.amendment)
The bitmap scan on contract_contract_line is good (8057 vs 7360 rows),
and so is the index scan (1 row), but the JOIN with "contact_partner
businessProvider" should give the 8057 rows from the bitmap scan,
shouldn't it ?
2018-01-23 16:38 GMT+01:00 Laurent Martelli <martellilaurent@gmail.com>:
> 2018-01-23 16:18 GMT+01:00 Justin Pryzby <pryzby@telsasoft.com>:
>> On Tue, Jan 23, 2018 at 01:03:49PM +0100, Laurent Martelli wrote:
>>
>>> Here is the default plan :
>>
>> Can you resend without line breaks or paste a link to explain.depesz?
>
> I hope it's better like that. I've attached it too, just in case.
>
>>
>> The problem appears to be here:
>>
>> -> Nested Loop Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual time=342.725..340775.031 rows=7359
loops=1)
>> Join Filter: (sh.share_holder_partner = partner.id)
>> Rows Removed by Join Filter: 204915707
>>
>> Justin
>
>
>
> QUERY PLAN
>
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=39200.76..39200.76 rows=1 width=1066) (actual
> time=341273.300..341274.244 rows=7359 loops=1)
> Sort Key: ((array_agg(subscribed_power.subscribed_power))[1]) DESC,
> status.name, contractline.id
> Sort Method: quicksort Memory: 3930kB
> -> Nested Loop Left Join (cost=32069.19..39200.75 rows=1
> width=1066) (actual time=342.806..341203.151 rows=7359 loops=1)
> -> Nested Loop Left Join (cost=32069.05..39200.50 rows=1
> width=508) (actual time=342.784..341102.848 rows=7359 loops=1)
> -> Nested Loop Left Join (cost=32068.77..39200.20
> rows=1 width=500) (actual time=342.778..341070.310 rows=7359 loops=1)
> -> Nested Loop Left Join
> (cost=32068.64..39200.04 rows=1 width=507) (actual
> time=342.776..341058.256 rows=7359 loops=1)
> Join Filter: (cca.address = adr_contact.id)
> Rows Removed by Join Filter: 2254
> -> Nested Loop Left Join
> (cost=32068.22..39199.55 rows=1 width=515) (actual
> time=342.767..340997.058 rows=7359 loops=1)
> -> Nested Loop Left Join
> (cost=32067.79..39198.84 rows=1 width=447) (actual
> time=342.753..340932.286 rows=7359 loops=1)
> -> Nested Loop Left Join
> (cost=32067.65..39198.67 rows=1 width=421) (actual
> time=342.748..340896.132 rows=7359 loops=1)
> -> Nested Loop Left Join
> (cost=32067.23..39198.01 rows=1 width=279) (actual
> time=342.739..340821.987 rows=7359 loops=1)
> -> Nested Loop
> Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual
> time=342.725..340775.031 rows=7359 loops=1)
> Join Filter:
> (sh.share_holder_partner = partner.id)
> Rows Removed
> by Join Filter: 204915707
> -> Nested
> Loop Left Join (cost=28514.61..34092.46 rows=1 width=244) (actual
> time=287.323..610.192 rows=7359 loops=1)
> ->
> Nested Loop Left Join (cost=28514.47..34092.30 rows=1 width=239)
> (actual time=287.318..573.234 rows=7359 loops=1)
>
> -> Hash Right Join (cost=28513.48..34090.65 rows=1 width=159)
> (actual time=287.293..379.564 rows=7359 loops=1)
>
> Hash Cond: (ws.contract_line = contractline.id)
>
> -> Seq Scan on shareholder_web_subscription ws
> (cost=0.00..5378.84 rows=52884 width=24) (actual time=0.006..12.307
> rows=52884 loops=1)
>
> -> Hash (cost=28513.47..28513.47 rows=1 width=143) (actual
> time=287.243..287.243 rows=7359 loops=1)
>
> Buckets: 8192 (originally 1024) Batches: 1 (originally 1)
> Memory Usage: 1173kB
>
> -> Nested Loop Left Join (cost=17456.16..28513.47 rows=1
> width=143) (actual time=85.005..284.689 rows=7359 loops=1)
>
> -> Nested Loop (cost=17456.03..28513.31 rows=1
> width=148) (actual time=85.000..276.599 rows=7359 loops=1)
>
> -> Nested Loop Left Join
> (cost=17455.73..28512.84 rows=1 width=148) (actual
> time=84.993..261.954 rows=7359 loops=1)
>
> -> Nested Loop (cost=17455.60..28512.67
> rows=1 width=140) (actual time=84.989..253.715 rows=7359 loops=1)
>
> -> Nested Loop
> (cost=17455.18..28511.93 rows=1 width=93) (actual time=84.981..230.977
> rows=7359 loops=1)
>
> -> Merge Right Join
> (cost=17454.89..28511.52 rows=1 width=93) (actual time=84.974..211.200
> rows=7359 loops=1)
>
> Merge Cond:
> (subscribed_power.amendment = amendment.id)
>
> -> GroupAggregate
> (cost=12457.78..22574.03 rows=75229 width=168) (actual
> time=57.500..175.674 rows=83432 loops=1)
>
> Group Key:
> subscribed_power.amendment
>
> -> Merge Join
> (cost=12457.78..20764.08 rows=173917 width=12) (actual
> time=57.479..129.530 rows=87938 loops=1)
>
> Merge Cond:
> (subscribed_power.amendment = amendment_1.id)
>
> -> Index
> Scan using contract_subscribed_power_amendment_idx on
> contract_subscribed_power subscribed_power (cost=0.42..13523.09
> rows=173917 width=12) (actual time=0.009..33.704 rows=87963 loops=1)
>
> -> Sort
> (cost=12457.36..12666.43 rows=83629 width=8) (actual
> time=57.467..67.071 rows=88019 loops=1)
>
> Sort
> Key: amendment_1.id
>
> Sort
> Method: quicksort Memory: 6988kB
>
> ->
> Hash Join (cost=10.21..5619.97 rows=83629 width=8) (actual
> time=0.112..40.965 rows=83532 loops=1)
>
>
> Hash Cond: (amendment_1.pricing = pricing.id)
>
>
> -> Seq Scan on contract_amendment amendment_1 (cost=0.00..4460.29
> rows=83629 width=16) (actual time=0.004..6.988 rows=83629 loops=1)
>
>
> -> Hash (cost=8.43..8.43 rows=142 width=8) (actual time=0.095..0.095
> rows=141 loops=1)
>
>
> Buckets: 1024 Batches: 1 Memory Usage: 14kB
>
>
> -> Hash Join (cost=1.07..8.43 rows=142 width=8) (actual
> time=0.012..0.078 rows=141 loops=1)
>
>
> Hash Cond: (pricing.elec_range = elec_range.id)
>
>
> -> Seq Scan on pricing_pricing pricing (cost=0.00..5.42
> rows=142 width=16) (actual time=0.003..0.015 rows=142 loops=1)
>
>
> -> Hash (cost=1.03..1.03 rows=3 width=8) (actual
> time=0.006..0.006 rows=3 loops=1)
>
>
> Buckets: 1024 Batches: 1 Memory Usage: 9kB
>
>
> -> Seq Scan on fluid_elec_range elec_range
> (cost=0.00..1.03 rows=3 width=8) (actual time=0.003..0.005 rows=3
> loops=1)
>
> -> Sort
> (cost=4997.11..4997.11 rows=1 width=69) (actual time=27.427..28.896
> rows=7359 loops=1)
>
> Sort Key:
> amendment.id
>
> Sort Method:
> quicksort Memory: 1227kB
>
> -> Nested Loop
> (cost=183.44..4997.10 rows=1 width=69) (actual time=1.115..24.616
> rows=7359 loops=1)
>
> -> Nested
> Loop (cost=183.15..4996.59 rows=1 width=49) (actual time=1.107..9.091
> rows=7360 loops=1)
>
> ->
> Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on contact_partner
> businessprovider (cost=0.42..8.44 rows=1 width=13) (actual
> time=0.010..0.010 rows=1 loops=1)
>
>
> Index Cond: ((business_provider_code)::text = 'BRZH'::text)
>
> ->
> Bitmap Heap Scan on contract_contract_line contractline
> (cost=182.73..4907.58 rows=8057 width=52) (actual time=1.086..5.231
> rows=7360 loops=1)
>
>
> Recheck Cond: (business_provider_partner = businessprovider.id)
>
>
> Heap Blocks: exact=3586
>
>
> -> Bitmap Index Scan on
> contract_contract_line_business_provider_partner_idx
> (cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655
> rows=7360 loops=1)
>
>
> Index Cond: (business_provider_partner = businessprovider.id)
>
> -> Index
> Scan using contract_amendment_pkey on contract_amendment amendment
> (cost=0.29..0.50 rows=1 width=28) (actual time=0.001..0.002 rows=1
> loops=7360)
>
> Index
> Cond: (id = contractline.amendment)
>
> -> Index Scan using
> contract_contract_pkey on contract_contract contract (cost=0.29..0.40
> rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=7359)
>
> Index Cond: (id =
> contractline.contract)
>
> -> Index Scan using
> contact_partner_pkey on contact_partner partner (cost=0.42..0.74
> rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=7359)
>
> Index Cond: (id =
> contract.main_client_partner)
>
> -> Index Scan using
> contact_client_nature_pkey on contact_client_nature clientnature
> (cost=0.14..0.15 rows=1 width=24) (actual time=0.001..0.001 rows=1
> loops=7359)
>
> Index Cond: (id =
> partner.client_nature)
>
> -> Index Scan using territory_mpt_pkey on
> territory_mpt mpt (cost=0.29..0.46 rows=1 width=16) (actual
> time=0.001..0.001 rows=1 loops=7359)
>
> Index Cond: (id = contractline.mpt)
>
> -> Index Scan using contract_user_segment_pkey on
> contract_user_segment usersegment (cost=0.14..0.15 rows=1 width=11)
> (actual time=0.001..0.001 rows=1 loops=7359)
>
> Index Cond: (id = amendment.user_segment)
>
> -> Nested Loop Left Join (cost=0.99..1.64 rows=1 width=96) (actual
> time=0.021..0.025 rows=1 loops=7359)
>
> -> Nested Loop Left Join (cost=0.85..1.35 rows=1 width=89)
> (actual time=0.017..0.020 rows=1 loops=7359)
>
> -> Nested Loop Left Join (cost=0.71..1.18 rows=1 width=76)
> (actual time=0.013..0.014 rows=1 loops=7359)
>
> -> Index Scan using contact_address_pkey on
> contact_address a (cost=0.42..0.85 rows=1 width=84) (actual
> time=0.005..0.006 rows=1 loops=7359)
>
> Index Cond: (mpt.address = id)
>
> -> Index Scan using territory_commune_pkey on
> territory_commune commune (cost=0.29..0.32 rows=1 width=16) (actual
> time=0.005..0.006 rows=1 loops=7359)
>
> Index Cond: (a.commune = id)
>
> -> Index Scan using territory_department_pkey on
> territory_department dept (cost=0.14..0.16 rows=1 width=37) (actual
> time=0.003..0.004 rows=1 loops=7359)
>
> Index Cond: (commune.department = id)
>
> -> Index Scan using territory_region_pkey on territory_region reg
> (cost=0.14..0.27 rows=1 width=23) (actual time=0.003..0.003 rows=1
> loops=7359)
>
> Index Cond: (dept.region = id)
> ->
> Index Scan using administration_status_pkey on administration_status
> status (cost=0.14..0.16 rows=1 width=21) (actual time=0.003..0.003
> rows=1 loops=7359)
>
> Index Cond: (id = contractline.status)
> ->
> GroupAggregate (cost=3552.48..4479.27 rows=27827 width=80) (actual
> time=0.006..44.205 rows=27846 loops=7359)
> Group
> Key: sh.share_holder_partner
> ->
> Sort (cost=3552.48..3624.85 rows=28948 width=17) (actual
> time=0.003..2.913 rows=28946 loops=7359)
>
> Sort Key: sh.share_holder_partner
>
> Sort Method: quicksort Memory: 3030kB
>
> -> Hash Join (cost=2.23..1407.26 rows=28948 width=17) (actual
> time=0.024..12.296 rows=28946 loops=1)
>
> Hash Cond: (sh.company = sh_coop.id)
>
> -> Seq Scan on shareholder_share_holder sh (cost=0.00..1007.00
> rows=28948 width=20) (actual time=0.007..5.495 rows=28946 loops=1)
>
> Filter: (nb_share > 0)
>
> Rows Removed by Filter: 1934
>
> -> Hash (cost=2.10..2.10 rows=10 width=13) (actual
> time=0.009..0.009 rows=10 loops=1)
>
> Buckets: 1024 Batches: 1 Memory Usage: 9kB
>
> -> Seq Scan on contact_company sh_coop (cost=0.00..2.10
> rows=10 width=13) (actual time=0.003..0.006 rows=10 loops=1)
> -> Index Scan
> using crm_origin_pkey on crm_origin co (cost=0.14..0.16 rows=1
> width=19) (actual time=0.004..0.004 rows=1 loops=7359)
> Index Cond:
> (id = ws.how_meet_enercoop)
> -> Index Scan using
> contact_contact_pkey on contact_contact mc (cost=0.42..0.65 rows=1
> width=150) (actual time=0.007..0.008 rows=1 loops=7359)
> Index Cond:
> (partner.main_contact = id)
> -> Index Scan using
> contact_title_pkey on contact_title title (cost=0.14..0.16 rows=1
> width=42) (actual time=0.003..0.003 rows=1 loops=7359)
> Index Cond: (mc.title = id)
> -> Index Scan using
> contact_address_pkey on contact_address adr_contact (cost=0.43..0.70
> rows=1 width=68) (actual time=0.005..0.005 rows=1 loops=7359)
> Index Cond: (id = CASE WHEN
> (CASE WHEN ((partner.person_category_select)::text =
> 'naturalPerson'::text) THEN 'P'::text WHEN
> ((partner.person_category_select)::text = 'legalPerson'::text) THEN
> 'M'::text ELSE '?????'::text END = 'P'::text) THEN
> COALESCE(mc.address, mc.address_pro) ELSE COALESCE(mc.address_pro,
> mc.address) END)
> -> Index Scan using
> contact_contact_address_contact_idx on contact_contact_address cca
> (cost=0.42..0.48 rows=1 width=24) (actual time=0.006..0.006 rows=1
> loops=7359)
> Index Cond: (contact = mc.id)
> -> Index Scan using
> contact_contact_address_status_pkey on contact_contact_address_status
> npai (cost=0.13..0.15 rows=1 width=9) (actual time=0.000..0.000
> rows=0 loops=7359)
> Index Cond: (cca.contact_address_status = id)
> -> Index Scan using
> crm_crm_request_original_contract_line_idx on crm_crm_request
> mesrequest (cost=0.28..0.29 rows=1 width=16) (actual
> time=0.003..0.003 rows=0 loops=7359)
> Index Cond: (original_contract_line = contractline.id)
> -> Index Scan using sale_product_sub_family_pkey on
> sale_product_sub_family mesproductsubfamily (cost=0.14..0.20 rows=1
> width=62) (actual time=0.000..0.000 rows=0 loops=7359)
> Index Cond: (id = mesrequest.product_sub_family)
> Filter: (new_contract_ok IS TRUE)
> Planning time: 21.106 ms
> Execution time: 341275.027 ms
> (118 lignes)
>
>
> --
> http://www.laurentmartelli.com // http://www.imprimart.fr
--
http://www.laurentmartelli.com // http://www.imprimart.fr
In my opinion this is the Achilles heel of the postgres optimizer. Row estimates should never return 1, unless the estimate is provably <=1. This is particularly a problem with join estimates. A dumb fix for this is to change clamp_join_row_est() to never return a value <2. This fixes most of my observed poor plans. The real fix is to track uniqueness (or provable max rows) along with the selectivity estimate.
Here's the dumb fix.https://github.com/labkey-matthewb/postgres/commit/b1fd99f4deffbbf3db2172ccaba51a34f18d1b1a
On Tue, Jan 23, 2018 at 7:59 AM, Laurent Martelli <martellilaurent@gmail.com> wrote:
I've have a look to the plan with pgadmin, and I think the problem is
rather here :The bitmap scan on contract_contract_line is good (8057 vs 7360 rows),
-> Sort (cost=4997.11..4997.11 rows=1 width=69) (actual
time=27.427..28.896 rows=7359 loops=1)
Sort Key: amendment.id
Sort Method: quicksort Memory: 1227kB
-> Nested Loop (cost=183.44..4997.10 rows=1 width=69) (actual
time=1.115..24.616 rows=7359 loops=1)
-> Nested Loop (cost=183.15..4996.59 rows=1 width=49)
(actual time=1.107..9.091 rows=7360 loops=1)
-> Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on
contact_partner businessprovider (cost=0.42..8.44 rows=1 width=13)
(actual time=0.010..0.010 rows=1 loops=1)
Index Cond: ((business_provider_code)::text =
'BRZH'::text)
-> Bitmap Heap Scan on contract_contract_line
contractline (cost=182.73..4907.58 rows=8057 width=52) (actual
time=1.086..5.231 rows=7360 loops=1)
Recheck Cond: (business_provider_partner =
businessprovider.id)
Heap Blocks: exact=3586
-> Bitmap Index Scan on
contract_contract_line_business_provider_partner_idx
(cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655
rows=7360 loops=1)
Index Cond: (business_provider_partner =
businessprovider.id)
-> Index Scan using contract_amendment_pkey on
contract_amendment amendment (cost=0.29..0.50 rows=1 width=28)
(actual time=0.001..0.002 rows=1 loops=7360)
Index Cond: (id = contractline.amendment)
and so is the index scan (1 row), but the JOIN with "contact_partner
businessProvider" should give the 8057 rows from the bitmap scan,
shouldn't it ?
2018-01-23 16:38 GMT+01:00 Laurent Martelli <martellilaurent@gmail.com>:
> 2018-01-23 16:18 GMT+01:00 Justin Pryzby <pryzby@telsasoft.com>:
>> On Tue, Jan 23, 2018 at 01:03:49PM +0100, Laurent Martelli wrote:
>>
>>> Here is the default plan :
>>
>> Can you resend without line breaks or paste a link to explain.depesz?
>
> I hope it's better like that. I've attached it too, just in case.
>
>>
>> The problem appears to be here:
>>
>> -> Nested Loop Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual time=342.725..340775.031 rows=7359 loops=1)
>> Join Filter: (sh.share_holder_partner = partner.id)
>> Rows Removed by Join Filter: 204915707
>>
>> Justin
>
>
>
> QUERY PLAN
> ------------------------------------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------
> Sort (cost=39200.76..39200.76 rows=1 width=1066) (actual
> time=341273.300..341274.244 rows=7359 loops=1)
> Sort Key: ((array_agg(subscribed_power.subscribed_power))[1]) DESC,
> status.name, contractline.id
> Sort Method: quicksort Memory: 3930kB
> -> Nested Loop Left Join (cost=32069.19..39200.75 rows=1
> width=1066) (actual time=342.806..341203.151 rows=7359 loops=1)
> -> Nested Loop Left Join (cost=32069.05..39200.50 rows=1
> width=508) (actual time=342.784..341102.848 rows=7359 loops=1)
> -> Nested Loop Left Join (cost=32068.77..39200.20
> rows=1 width=500) (actual time=342.778..341070.310 rows=7359 loops=1)
> -> Nested Loop Left Join
> (cost=32068.64..39200.04 rows=1 width=507) (actual
> time=342.776..341058.256 rows=7359 loops=1)
> Join Filter: (cca.address = adr_contact.id)
> Rows Removed by Join Filter: 2254
> -> Nested Loop Left Join
> (cost=32068.22..39199.55 rows=1 width=515) (actual
> time=342.767..340997.058 rows=7359 loops=1)
> -> Nested Loop Left Join
> (cost=32067.79..39198.84 rows=1 width=447) (actual
> time=342.753..340932.286 rows=7359 loops=1)
> -> Nested Loop Left Join
> (cost=32067.65..39198.67 rows=1 width=421) (actual
> time=342.748..340896.132 rows=7359 loops=1)
> -> Nested Loop Left Join
> (cost=32067.23..39198.01 rows=1 width=279) (actual
> time=342.739..340821.987 rows=7359 loops=1)
> -> Nested Loop
> Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual
> time=342.725..340775.031 rows=7359 loops=1)
> Join Filter:
> (sh.share_holder_partner = partner.id)
> Rows Removed
> by Join Filter: 204915707
> -> Nested
> Loop Left Join (cost=28514.61..34092.46 rows=1 width=244) (actual
> time=287.323..610.192 rows=7359 loops=1)
> ->
> Nested Loop Left Join (cost=28514.47..34092.30 rows=1 width=239)
> (actual time=287.318..573.234 rows=7359 loops=1)
>
> -> Hash Right Join (cost=28513.48..34090.65 rows=1 width=159)
> (actual time=287.293..379.564 rows=7359 loops=1)
>
> Hash Cond: (ws.contract_line = contractline.id)
>
> -> Seq Scan on shareholder_web_subscription ws
> (cost=0.00..5378.84 rows=52884 width=24) (actual time=0.006..12.307
> rows=52884 loops=1)
>
> -> Hash (cost=28513.47..28513.47 rows=1 width=143) (actual
> time=287.243..287.243 rows=7359 loops=1)
>
> Buckets: 8192 (originally 1024) Batches: 1 (originally 1)
> Memory Usage: 1173kB
>
> -> Nested Loop Left Join (cost=17456.16..28513.47 rows=1
> width=143) (actual time=85.005..284.689 rows=7359 loops=1)
>
> -> Nested Loop (cost=17456.03..28513.31 rows=1
> width=148) (actual time=85.000..276.599 rows=7359 loops=1)
>
> -> Nested Loop Left Join
> (cost=17455.73..28512.84 rows=1 width=148) (actual
> time=84.993..261.954 rows=7359 loops=1)
>
> -> Nested Loop (cost=17455.60..28512.67
> rows=1 width=140) (actual time=84.989..253.715 rows=7359 loops=1)
>
> -> Nested Loop
> (cost=17455.18..28511.93 rows=1 width=93) (actual time=84.981..230.977
> rows=7359 loops=1)
>
> -> Merge Right Join
> (cost=17454.89..28511.52 rows=1 width=93) (actual time=84.974..211.200
> rows=7359 loops=1)
>
> Merge Cond:
> (subscribed_power.amendment = amendment.id)
>
> -> GroupAggregate
> (cost=12457.78..22574.03 rows=75229 width=168) (actual
> time=57.500..175.674 rows=83432 loops=1)
>
> Group Key:
> subscribed_power.amendment
>
> -> Merge Join
> (cost=12457.78..20764.08 rows=173917 width=12) (actual
> time=57.479..129.530 rows=87938 loops=1)
>
> Merge Cond:
> (subscribed_power.amendment = amendment_1.id)
>
> -> Index
> Scan using contract_subscribed_power_amendment_idx on
> contract_subscribed_power subscribed_power (cost=0.42..13523.09
> rows=173917 width=12) (actual time=0.009..33.704 rows=87963 loops=1)
>
> -> Sort
> (cost=12457.36..12666.43 rows=83629 width=8) (actual
> time=57.467..67.071 rows=88019 loops=1)
>
> Sort
> Key: amendment_1.id
>
> Sort
> Method: quicksort Memory: 6988kB
>
> ->
> Hash Join (cost=10.21..5619.97 rows=83629 width=8) (actual
> time=0.112..40.965 rows=83532 loops=1)
>
>
> Hash Cond: (amendment_1.pricing = pricing.id)
>
>
> -> Seq Scan on contract_amendment amendment_1 (cost=0.00..4460.29
> rows=83629 width=16) (actual time=0.004..6.988 rows=83629 loops=1)
>
>
> -> Hash (cost=8.43..8.43 rows=142 width=8) (actual time=0.095..0.095
> rows=141 loops=1)
>
>
> Buckets: 1024 Batches: 1 Memory Usage: 14kB
>
>
> -> Hash Join (cost=1.07..8.43 rows=142 width=8) (actual
> time=0.012..0.078 rows=141 loops=1)
>
>
> Hash Cond: (pricing.elec_range = elec_range.id)
>
>
> -> Seq Scan on pricing_pricing pricing (cost=0.00..5.42
> rows=142 width=16) (actual time=0.003..0.015 rows=142 loops=1)
>
>
> -> Hash (cost=1.03..1.03 rows=3 width=8) (actual
> time=0.006..0.006 rows=3 loops=1)
>
>
> Buckets: 1024 Batches: 1 Memory Usage: 9kB
>
>
> -> Seq Scan on fluid_elec_range elec_range
> (cost=0.00..1.03 rows=3 width=8) (actual time=0.003..0.005 rows=3
> loops=1)
>
> -> Sort
> (cost=4997.11..4997.11 rows=1 width=69) (actual time=27.427..28.896
> rows=7359 loops=1)
>
> Sort Key:
> amendment.id
>
> Sort Method:
> quicksort Memory: 1227kB
>
> -> Nested Loop
> (cost=183.44..4997.10 rows=1 width=69) (actual time=1.115..24.616
> rows=7359 loops=1)
>
> -> Nested
> Loop (cost=183.15..4996.59 rows=1 width=49) (actual time=1.107..9.091
> rows=7360 loops=1)
>
> ->
> Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on contact_partner
> businessprovider (cost=0.42..8.44 rows=1 width=13) (actual
> time=0.010..0.010 rows=1 loops=1)
>
>
> Index Cond: ((business_provider_code)::text = 'BRZH'::text)
>
> ->
> Bitmap Heap Scan on contract_contract_line contractline
> (cost=182.73..4907.58 rows=8057 width=52) (actual time=1.086..5.231
> rows=7360 loops=1)
>
>
> Recheck Cond: (business_provider_partner = businessprovider.id)
>
>
> Heap Blocks: exact=3586
>
>
> -> Bitmap Index Scan on
> contract_contract_line_business_provider_partner_idx
> (cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655
> rows=7360 loops=1)
>
>
> Index Cond: (business_provider_partner = businessprovider.id)
>
> -> Index
> Scan using contract_amendment_pkey on contract_amendment amendment
> (cost=0.29..0.50 rows=1 width=28) (actual time=0.001..0.002 rows=1
> loops=7360)
>
> Index
> Cond: (id = contractline.amendment)
>
> -> Index Scan using
> contract_contract_pkey on contract_contract contract (cost=0.29..0.40
> rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=7359)
>
> Index Cond: (id =
> contractline.contract)
>
> -> Index Scan using
> contact_partner_pkey on contact_partner partner (cost=0.42..0.74
> rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=7359)
>
> Index Cond: (id =
> contract.main_client_partner)
>
> -> Index Scan using
> contact_client_nature_pkey on contact_client_nature clientnature
> (cost=0.14..0.15 rows=1 width=24) (actual time=0.001..0.001 rows=1
> loops=7359)
>
> Index Cond: (id =
> partner.client_nature)
>
> -> Index Scan using territory_mpt_pkey on
> territory_mpt mpt (cost=0.29..0.46 rows=1 width=16) (actual
> time=0.001..0.001 rows=1 loops=7359)
>
> Index Cond: (id = contractline.mpt)
>
> -> Index Scan using contract_user_segment_pkey on
> contract_user_segment usersegment (cost=0.14..0.15 rows=1 width=11)
> (actual time=0.001..0.001 rows=1 loops=7359)
>
> Index Cond: (id = amendment.user_segment)
>
> -> Nested Loop Left Join (cost=0.99..1.64 rows=1 width=96) (actual
> time=0.021..0.025 rows=1 loops=7359)
>
> -> Nested Loop Left Join (cost=0.85..1.35 rows=1 width=89)
> (actual time=0.017..0.020 rows=1 loops=7359)
>
> -> Nested Loop Left Join (cost=0.71..1.18 rows=1 width=76)
> (actual time=0.013..0.014 rows=1 loops=7359)
>
> -> Index Scan using contact_address_pkey on
> contact_address a (cost=0.42..0.85 rows=1 width=84) (actual
> time=0.005..0.006 rows=1 loops=7359)
>
> Index Cond: (mpt.address = id)
>
> -> Index Scan using territory_commune_pkey on
> territory_commune commune (cost=0.29..0.32 rows=1 width=16) (actual
> time=0.005..0.006 rows=1 loops=7359)
>
> Index Cond: (a.commune = id)
>
> -> Index Scan using territory_department_pkey on
> territory_department dept (cost=0.14..0.16 rows=1 width=37) (actual
> time=0.003..0.004 rows=1 loops=7359)
>
> Index Cond: (commune.department = id)
>
> -> Index Scan using territory_region_pkey on territory_region reg
> (cost=0.14..0.27 rows=1 width=23) (actual time=0.003..0.003 rows=1
> loops=7359)
>
> Index Cond: (dept.region = id)
> ->
> Index Scan using administration_status_pkey on administration_status
> status (cost=0.14..0.16 rows=1 width=21) (actual time=0.003..0.003
> rows=1 loops=7359)
>
> Index Cond: (id = contractline.status)
> ->
> GroupAggregate (cost=3552.48..4479.27 rows=27827 width=80) (actual
> time=0.006..44.205 rows=27846 loops=7359)
> Group
> Key: sh.share_holder_partner
> ->
> Sort (cost=3552.48..3624.85 rows=28948 width=17) (actual
> time=0.003..2.913 rows=28946 loops=7359)
>
> Sort Key: sh.share_holder_partner
>
> Sort Method: quicksort Memory: 3030kB
>
> -> Hash Join (cost=2.23..1407.26 rows=28948 width=17) (actual
> time=0.024..12.296 rows=28946 loops=1)
>
> Hash Cond: (sh.company = sh_coop.id)
>
> -> Seq Scan on shareholder_share_holder sh (cost=0.00..1007.00
> rows=28948 width=20) (actual time=0.007..5.495 rows=28946 loops=1)
>
> Filter: (nb_share > 0)
>
> Rows Removed by Filter: 1934
>
> -> Hash (cost=2.10..2.10 rows=10 width=13) (actual
> time=0.009..0.009 rows=10 loops=1)
>
> Buckets: 1024 Batches: 1 Memory Usage: 9kB
>
> -> Seq Scan on contact_company sh_coop (cost=0.00..2.10
> rows=10 width=13) (actual time=0.003..0.006 rows=10 loops=1)
> -> Index Scan
> using crm_origin_pkey on crm_origin co (cost=0.14..0.16 rows=1
> width=19) (actual time=0.004..0.004 rows=1 loops=7359)
> Index Cond:
> (id = ws.how_meet_enercoop)
> -> Index Scan using
> contact_contact_pkey on contact_contact mc (cost=0.42..0.65 rows=1
> width=150) (actual time=0.007..0.008 rows=1 loops=7359)
> Index Cond:
> (partner.main_contact = id)
> -> Index Scan using
> contact_title_pkey on contact_title title (cost=0.14..0.16 rows=1
> width=42) (actual time=0.003..0.003 rows=1 loops=7359)
> Index Cond: (mc.title = id)
> -> Index Scan using
> contact_address_pkey on contact_address adr_contact (cost=0.43..0.70
> rows=1 width=68) (actual time=0.005..0.005 rows=1 loops=7359)
> Index Cond: (id = CASE WHEN
> (CASE WHEN ((partner.person_category_select)::text =
> 'naturalPerson'::text) THEN 'P'::text WHEN
> ((partner.person_category_select)::text = 'legalPerson'::text) THEN
> 'M'::text ELSE '?????'::text END = 'P'::text) THEN
> COALESCE(mc.address, mc.address_pro) ELSE COALESCE(mc.address_pro,
> mc.address) END)
> -> Index Scan using
> contact_contact_address_contact_idx on contact_contact_address cca
> (cost=0.42..0.48 rows=1 width=24) (actual time=0.006..0.006 rows=1
> loops=7359)
> Index Cond: (contact = mc.id)
> -> Index Scan using
> contact_contact_address_status_pkey on contact_contact_address_status
> npai (cost=0.13..0.15 rows=1 width=9) (actual time=0.000..0.000
> rows=0 loops=7359)
> Index Cond: (cca.contact_address_status = id)
> -> Index Scan using
> crm_crm_request_original_contract_line_idx on crm_crm_request
> mesrequest (cost=0.28..0.29 rows=1 width=16) (actual
> time=0.003..0.003 rows=0 loops=7359)
> Index Cond: (original_contract_line = contractline.id)
> -> Index Scan using sale_product_sub_family_pkey on
> sale_product_sub_family mesproductsubfamily (cost=0.14..0.20 rows=1
> width=62) (actual time=0.000..0.000 rows=0 loops=7359)
> Index Cond: (id = mesrequest.product_sub_family)
> Filter: (new_contract_ok IS TRUE)
> Planning time: 21.106 ms
> Execution time: 341275.027 ms
> (118 lignes)
>
>
> --
> http://www.laurentmartelli.com // http://www.imprimart.fr
--
http://www.laurentmartelli.com // http://www.imprimart.fr