Rodriguez Fernando escribió:
Hugo A. Figueroa Solano escribió:
Nunca había visto un select tan grande, no me extraña que tarde tanto
------------------------------------------------------------------------
*De:* [email protected]
[mailto:[email protected]] *En nombre de *Edwin
Quijada
*Enviado el:* Jueves, 29 de Abril de 2010 02:54 p.m.
*Para:* [email protected]
*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')
-
Enviado a la lista de correo pgsql-es-ayuda ([email protected])
Para cambiar tu suscripción:
http://www.postgresql.org/mailpref/pgsql-es-ayuda
