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.
I managed to speed things up with "set enable_nestloop = false;", but is
that the only choice I have ? Should I report a bug ?
The view is this :
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,'/MM') AS datemes_mm,
(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,'') 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;
And the query is : select * from export_contract_par_region where codecoop
= 'BRZH';
Here is the default plan :
QUERY
PLAN
Re: Bad plan
2018-01-23 16:18 GMT+01:00 Justin Pryzby <pry...@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..1
Re: Bad plan
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 <martellilaur...@gmail.com>: > 2018-01-23 16:18 GMT+01:00 Justin Pryzby <pry...@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) >
