Query Analyzing - Mailing list pgsql-general
From | Booth, Robert |
---|---|
Subject | Query Analyzing |
Date | |
Msg-id | 419D2EB7B461D411A53B00508B69181D0623261D@sdex02.sd.intuit.com Whole thread Raw |
Responses |
Re: Query Analyzing
|
List | pgsql-general |
I'm trying to figure out how to use explain to analyze my queries and speed them up based on that information. Are there any good resources on this out there? If not could someone look at this explain plan and tell me what I'm looking at and why certain things are performing the way they are. Query: SELECT DISTINCT ALLFORMS.File__no, ALLFORMS.Mod, ALLFORMS.Frm_Wks, ALLFORMS.ddf_type, AANDA.Rev_Date, to_char(AANDA.Prelim_Est, 'MM/DD/YY') AS prelim_est, to_char(AANDA.Prelim_Rec, 'MM/DD/YY') AS prelim_rec, to_char(AANDA.Final_Est, 'MM/DD/YY') AS final_est, to_char(AANDA.Final_Rec, 'MM/DD/YY') AS final_rec, to_char(AANDA.InstrExp, 'MM/DD/YY') AS instrexp, to_char(AANDA.Lsr_to_GWood, 'MM/DD/YY') AS lsr_to_gwood, to_char(AANDA.Instr_Rec, 'MM/DD/YY') AS instr_rec, to_char(AANDA.GWood_Recd, 'MM/DD/YY') AS gwood_recd, to_char(aanda.m_w_inst_to_dev, 'MM/DD/YY') AS m_w_inst_to_dev, AANDA.m_w_inst_status, to_char(AANDA.M_W_Lsr_To_Dev, 'MM/DD/YY') AS m_w_lsr_to_dev, AANDA.M_W_Lsr_Status, to_char(AANDA.Lsr_Sent, 'MM/DD/YY') AS lsr_sent, to_char(AANDA.M_W_Lsr_App, 'MM/DD/YY') AS m_w_lsr_app, to_char(AANDA.InstPrelimExp, 'MM/DD/YY') AS instprelimexp, to_char(AANDA.InstPrelimRec, 'MM/DD/YY') AS instprelimrec, MODINFO.Team_Leader, MODINFO.Forms_Lead, MODINFO.Developer, AANDA.plus, MODINFO.IDGAandAPOC, MODINFO.IDGAandALead, MODINFO.LacerteLead, grdb.teamlead AS grdblacertelead, MODINFO.LacerteResearcher, grdb.developer AS grdblacertedeveloper, MODINFO.EFLead, MODINFO.EFDeveloper, MODINFO.NGILead, MODINFO.NGIDeveloper, to_char(AANDA.LsctoDev, 'MM/DD/YY') AS lsctodev, AANDA.LscStatus, to_char(AANDA.LscSent, 'MM/DD/YY') AS lscsent, to_char(AANDA.LscApp, 'MM/DD/YY') AS lscapp, grdb.busunit || grdb.state AS newlacertemodequiv, ALLFORMS.OSfirstchk, MODINFO.Module, ALLFORMS.Scannablechk, CASE WHEN comchk = true THEN 'C' ELSE '' END AS CT, ALLFORMS.Inactive, aanda_status_types.code, modinfo.proseries_actual_date, modinfo.turbotax_actual_date, modinfo.webturbotax_actual_date, modinfo.lacerte_actual_date, tl.Extension as tl_extension, dev.Extension as dev_extension, ll.Extension as ll_extension, lr.Extension as lr_extension, grdb.lacertefilename AS lacertename, grdb.lacerteformname, gd.extension AS grdblacertedeveloperextension, gl.extension AS grdblacerteleadextension FROM ((((((((MODINFO INNER JOIN (ALLFORMS INNER JOIN AANDA ON ALLFORMS.File__no = AANDA.File__no) ON MODINFO.Module = ALLFORMS.Mod) LEFT JOIN aanda_status_types ON AANDA.aanda_status_type_id = aanda_status_types.aanda_status_type_id) LEFT JOIN Users AS tl ON MODINFO.Team_Leader = tl.name) LEFT JOIN Users AS dev ON MODINFO.Developer = dev.name) LEFT JOIN Users AS ll ON MODINFO.LacerteLead = ll.name) LEFT JOIN Users AS lr ON MODINFO.LacerteResearcher = lr.name) LEFT JOIN grdb ON allforms.file__no = grdb.intuitfilename) LEFT JOIN Users AS gd ON grdb.developer = gd.name) LEFT JOIN Users AS gl ON grdb.teamlead = gl.name WHERE ALLFORMS.Inactive Is Null; Explain Plan: Unique (cost=3561.60..4057.61 rows=342 width=682) -> Sort (cost=3561.60..3561.60 rows=3421 width=682) -> Hash Join (cost=2140.54..2738.33 rows=3421 width=682) -> Hash Join (cost=2132.03..2669.96 rows=3421 width=656) -> Merge Join (cost=2123.52..2601.58 rows=3421 width=630) -> Index Scan using grdb_intuitfilename_idx on grdb (cost=0.00..429.67 rows=5312 width=75) -> Sort (cost=2123.52..2123.52 rows=3421 width=555) -> Hash Join (cost=609.03..1420.19 rows=3421 width=555) -> Hash Join (cost=600.52..1351.81 rows=3421 width=529) -> Hash Join (cost=592.01..1283.44 rows=3421 width=503) -> Hash Join (cost=583.49..1215.06 rows=3421 width=477) -> Hash Join (cost=574.98..1146.69 rows=3421 width=451) -> Hash Join (cost=573.88..1128.39 rows=3421 width=442) -> Hash Join (cost=524.27..993.25 rows=3421 width=232) -> Seq Scan on aanda (cost=0.00..112.29 rows=3529 width=180) -> Hash (cost=422.54..422.54 rows=8292 width=52) -> Seq Scan on allforms (cost=0.00..422.54 rows=8292 width=52) -> Hash (cost=48.69..48.69 rows=369 width=210) -> Seq Scan on modinfo (cost=0.00..48.69 rows=369 width=210) -> Hash (cost=1.08..1.08 rows=8 width=9) -> Seq Scan on aanda_status_types (cost=0.00..1.08 rows=8 width=9) -> Hash (cost=7.81..7.81 rows=281 width=26) -> Seq Scan on users tl (cost=0.00..7.81 rows=281 width=26) -> Hash (cost=7.81..7.81 rows=281 width=26) -> Seq Scan on users dev (cost=0.00..7.81 rows=281 width=26) -> Hash (cost=7.81..7.81 rows=281 width=26) -> Seq Scan on users ll (cost=0.00..7.81 rows=281 width=26) -> Hash (cost=7.81..7.81 rows=281 width=26) -> Seq Scan on users lr (cost=0.00..7.81 rows=281 width=26) -> Hash (cost=7.81..7.81 rows=281 width=26) -> Seq Scan on users gd (cost=0.00..7.81 rows=281 width=26) -> Hash (cost=7.81..7.81 rows=281 width=26) -> Seq Scan on users gl (cost=0.00..7.81 rows=281 width=26) In looking at this I see that my index on the grdb table is getting used, but all of the other tables are being sequentially scanned. All of the joins are being done on primary key fields but they are all getting sequentially scanned, is there something that I'm missing? Again if you can point me to a good resource for learning this I'd appreciate it. Thanks, Rob
pgsql-general by date: