Rodriguez Fernando escribió:
> Hugo A. Figueroa Solano escribió:
>>
>>
>>
>> Nunca había visto un select tan grande, no me extraña que tarde tanto
>>
>> ------------------------------------------------------------------------
>>
>> *De:* pgsql-es-ayuda-owner(at)postgresql(dot)org
>> [mailto:pgsql-es-ayuda-owner(at)postgresql(dot)org] *En nombre de *Edwin
>> Quijada
>> *Enviado el:* Jueves, 29 de Abril de 2010 02:54 p.m.
>> *Para:* pgsql-es-ayuda(at)postgresql(dot)org
>> *Asunto:* [pgsql-es-ayuda] FW: Select de 11 horas
>>
>>
>>
>>
>> Estoy teniendo problemas con un select que me esta durando 11 horas y
>> su salida es de aproximada 200 records he revisado varias cosas pero
>> no entender bien el explain me ha ralentizado.
>>
>> PD:Alguieun que sepa interpretar bien los explain podria hacer un
>> webmeeting o algo para poder interpretar bien esta infromacion? Creo
>> que Postgres da bastante informacion pero muchas veces no sabemos
>> interpretar estos numeros , como mi caso.
>>
>> Ejemplo esta es una alerta que me envio mi server de monitoreo,munin,
>> con el plugin de Postgres
>>
>> uno.com.do :: talio.uno.com.do :: Postgres locks
>> CRITICALs: Locks is 10.97 (outside range [:10]).
>> WARNINGs: Exclusive locks is 9.97 (outside range [:5]).
>>
>>
>>
>>
>> Este es el select en cuestion
>>
>>
>> http://pastebin.com/rrDPrxUB
>>
>>
>>
>>
>>
>>
>>
>>
>> Este es el explain del select mostrado
>>
>>
>>
>> http://explain.depesz.com/s/Gxk
>>
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>>
>>
>> __________ Información de NOD32, revisión 5073 (20100429) __________
>>
>> Este mensaje ha sido analizado con NOD32 antivirus system
>> http://www.nod32.com
>>
> Hola a todos.
> 1- El query es horrible
> 2- podrias usar left o inner con esta parte del query (mezclas
> producto cartesiano y joins), por lo cual enloqueces al optimizador.
> aegon.toriginal c, (supongo que podria ser *inner join
> **aegon.toriginal c on (a.funoid=c.unoid **)*)
> aegon.tdata_address e, (supongo que podria ser * inner join
> **aegon.tdata_address e on (a.fid_address=e.fid_address )*)
> aegon.tdata_t3 h (supongo que podria ser *inner join
> **aegon.tdata_t3 h on (a.funoid=h.funoid )*)
> left outer join aegon.toffer f ON
> (h.foffer_id_record=f.id_record)
> LEFT OUTER join aegon.tdata_persons q ON(q.fid_person =
> h.fid_primary)
> LEFT OUTER join aegon.tdata_persons i ON (i.fid_person =
> h.fid_secundary)
> LEFT OUTER join aegon.tdata_persons j ON (j.fid_person =
> h.fid_child)
> LEFT OUTER join aegon.tdata_payments n ON (n.fid_payment =
> h.fid_payment) ,
> aegon.ttransaction_head k , (supongo que podria ser * inner join
> **aegon.ttransaction_head k on (a.funoid=k.funoid and
> a.fcall_sec_doc=k.fsec_doc and a.fcall_type_doc=k.ftype_doc**)*)
> PUBLIC.uno_employes l, (supongo que podria ser *inner join
> **PUBLIC.uno_employes l on (k.fmade_by = l.id_employee )** *)
> aegon.ttransaction_det m, (supongo que podria ser *inner join
> **aegon.ttransaction_det m** on (m.funoid=h.funoid and
> m.fproductcode= h.fproduct_code**)*)
> aegon.tdata_main s (supongo que podria ser *inner join
> **aegon.tdata_main s** on (*a.funoid=s.funoid*)*)
>
> probá reemplazar los productos cartesianos por los inners
>
>
> Saludos Fernando
>
>
hola, perdon, te la hago facil
SELECT
c.campaignuno,
a.flead_type,
a.funoid,
'T3' as recordtype,
'24' as agencyid,
f.locationcode as locationcode,
f.solicitationid as solicitationid,
'' as wave,
c.maincampaignnbr as maincampaignnbr,
c.mainkeycode,
f.offercampaignnbr as offercampaignnum,
f.offerkeycode as offerkeycode,
f.fileownercode,
h.fsaleseq as saleseq,
h.fsalescount as salescount,
f.propofferorder as actualofferorder,
c.idtype1 as idtype1,
c.idnumber1 as idnumber1,
'' as banktransit1,
'' as idtype2,
'' as idnumber2,
'' as banktransit2,
'' as idtype3,
'' as idnumber3,
'' as banktransit3,
'' as idtype4,
'' as idnumber4,
'' as banktransit4,
upper(q.fprefix) as primaryinsurendnametitle,
upper(q.ffirstname) as primaryinsurendnamefist,
upper(q.fmiddle) as primaryinsurendnamemi,
upper(q.flastname) as primaryinsurendnamelast,
upper(q.fsuffix) as primaryinsurendnamesuffix,
upper(e.fsecondary) as secondaryaddress,
upper(e.fprimary) as primaryaddress,
upper(e.fcity) as city,
e.fstate as stateprovince,
e.fzipcode as zippostalcode,
e.fzipcode4 as zip4postalcode,
c.phonenumber as phonenumber,
'' as preauthperformedind,
q.fdob as primarydob,
q.fgender as primarygender,
upper(o.fhometown) as verificationfield,
'' as verified4digits,
case when a.fid_language=1 then 'E'
else 'H'
end as languagepref ,
upper(i.fprefix) as spousenametitle,
upper(i.ffirstname) as spousenamefirst,
upper(i.fmiddle) as spousenamemi,
upper(i.flastname) as spousenamelast,
upper(i.fsuffix) as spousenamesuffix,
i.fdob as spousedob,
i.fgender as spousegender,
upper(j.ffirstname) as child1namefirst,
upper(j.fmiddle) as child1namemi,
upper(j.flastname) as child1namelast,
upper(j.fsuffix) as child1namesiffix,
j.fdob as child1dob,
j.fgender as child1gender,
'00' as numberofchildren,
'' as relationshipofchildren,
'' as question1party,
'' as question1,
'' as question2party,
'' as question2,
'' as question3party,
'' as question3,
'' as question4party,
'' as question4,
'' as question5party,
'' as question5,
'' as question6party,
'' as question6,
'' as question7party,
'' as question7,
'' as question8party,
'' as question8,
'' as question9party,
'' as question9,
'' as question10party,
'' as question10,
'' as question11party,
'' as question11,
'' as question12party,
'' as question12,
'' as question13party,
'' as question13,
'' as question14party,
'' as question14,
'' as question15party,
'' as question15,
'' as question16party,
'' as question16,
'' as oiptitle,
'' as oipfirstname1,
'' as oipmi1,
'' as oiplastname1,
'' as oipsuffix1,
--null as oipdateofbirth,
'' as oipgender,
'00' as nbrofd1records,
'00' as nbrofp1records,
case when o.femail<>'' then 1
else 0
end as nbrofr1records,
n.fcard_name as altaccttype,
case when n.fcheck_number > 0 THEN
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
substring(n.fcheck_number::varchar,0,
ABS((length(n.fcheck_number::varchar)) ))::varchar
,'0','V'),'1','J'),'2','D'),'3','G'),'4','Y'),'5','F'),'6','B'),'7','C'),'8','P'),'9','N')
|| right(n.fcheck_number::varchar,1)
ELSE
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
substring(n.fcard_number::varchar,0,
ABS((length(n.fcard_number::varchar)) ))::varchar
,'0','V'),'1','J'),'2','D'),'3','G'),'4','Y'),'5','F'),'6','B'),'7','C'),'8','P'),'9','N')
|| right(n.fcard_number::varchar,1)
end as altacctnumber,
n.fcheck_routing as altbanktransit,
'' as quotedbilldate,
'' as filler1,
case when n.fcard_name='C' then '15'
else ''
end as pacprefbilldate,
f.productlinecode as productlinecode,
'' as canadianriderid,
'' as marketcode,
'' as rdrcoveragetype,
'' as rdrfucode,
--null as ridersequencenum,
f.sequencenumber as soldsequencenum,
'1' as soldaccountseq,
f.optioncode::integer as soldoptioncode,
f.optioncategory as soldoptioncat,
f.premiumfeeamt as soldpremium,
f.benefit as soldbenefit,
f.billingmode as soldbillingmode,
f.billingfrequency as soldbillingfreq,
f.nicasservicepkg as nicassoldservicepkg,
upper(o.ffirstname) as contactfirstname,
upper(o.fmiddle) as contactmi,
upper(o.flastname) as contactlastname,
upper(o.fsuffix) as contactsuffix,
case when a.ftalking_with='S' then 2 else 1 end as contactrelation,
case when r.fmade_by is null then k.fmade_by else p.field1::integer end
as agentcode,
case when r.fmade_by::varchar is null then (select
aegon.f_get_initials(l.first_name,l.last_name)) else p.field2 end as
agentinitials,
n.fcard_exp_date as credictcardexpdate,
(select aegon.f_get_initials(l.first_name,l.last_name)) as repinitials,
$1 as call1date,
k.fdatetime1::time as call1starttime,
k.fmade_by as call1repcode,
h.fid_code as call1outcome,
'' as filler2,
u.fcounter as numberofattempts,
'' as membershipgiftind,
'' as replacementindicator,
'' as optout,
'' as filler3,
'' as reservedforsystems,
'00' as nbrofu3recsunum,
'00' as nbrofi3recsinternet,
k.fdatetime3::time as call1endtime,
'' as infomercialproductcode,
'' as upsellslotnumber,
'' as directmailindicator,
'' as keycode_org
FROM aegon.tdata_main a
left outer join aegon.ttransaction_head r on
(a.fial_sec_doc=r.fsec_doc and a.fial_type_doc=r.ftype_doc)
left outer join aegon.ttpv_user p on (r.fmade_by=p.id_tpv_user)
LEFT OUTER join aegon.tdata_persons o on (o.fid_person =
a.fid_person_talking),
inner join aegon.toriginal c on (a.funoid=c.unoid )
inner join aegon.tdata_address e on (a.fid_address=e.fid_address)
aegon.tdata_t3 h inner join aegon.tdata_t3 h on (a.funoid=h.funoid )
left outer join aegon.toffer f on
(h.foffer_id_record=f.id_record)
LEFT OUTER join aegon.tdata_persons q on(q.fid_person =
h.fid_primary)
LEFT OUTER join aegon.tdata_persons i on(i.fid_person =
h.fid_secundary)
LEFT OUTER join aegon.tdata_persons j on(j.fid_person =
h.fid_child)
LEFT OUTER join aegon.tdata_payments n on (n.fid_payment =
h.fid_payment) ,
inner join aegon.ttransaction_head k on (a.funoid=k.funoid and
a.fcall_sec_doc=k.fsec_doc and a.fcall_type_doc=k.ftype_doc)
inner join PUBLIC.uno_employes l on (k.fmade_by = l.id_employee)
inner join aegon.ttransaction_det m on (m.funoid=h.funoid and
m.fproductcode= h.fproduct_code)
inner join aegon.tdata_main s on (a.funoid=s.funoid)
left outer join aegon.trpt_t3_hist t on (s.funoid=t.unoid )
inner join aegon.tdata_xfr u (a.funoid=u.funoid)
WHERE 1=1
and a.funoid=c.unoid
and a.funoid=h.funoid
and a.funoid=k.funoid
and a.funoid=s.funoid
and t.unoid is NULL
and a.funoid=u.funoid
and a.fstatus='F'
and a.fid_address=e.fid_address
and a.fcall_sec_doc=k.fsec_doc
and a.fcall_type_doc=k.ftype_doc
and k.fmade_by = l.id_employee
and m.flast_disposition=true
and m.funoid=h.funoid
and m.fproductcode= h.fproduct_code
and a.flead_type='DO'
and h.fid_code in('901','99')