Thread
-
Questions about "Output" in EXPLAIN ANALYZE VERBOSE
Jerry Brenner <jbrenner@guidewire.com> — 2024-01-02T18:28:31Z
We are currently on 13.9. For each of the questions, I'd also like to know if anything has changed in that area in later releases. NOTE: We are capturing all explain plans via auto_explain and storing them in a database table. One of our longer term goals is to build the relationship between queries and indexes, so we can tell where each of the indexes is used and how it is used (or not used). In the Index Only Scan example below, we think that there are other queries that use the same index AND also access JobID and MostRecentModel, but we want to verify that. *Any node type accessing an index or table* - It looks like "Output" includes more than just the columns with predicates and/or being accessed or returned in other nodes. *Has any thought been given to adding an additional attribute listing the columns that are actually used?* (While it's possible to do this after getting the explain plan, it seems like that information would be available internally in Postgres.) *Index Only Scan* - *Is it safe to assume that the columns listed with "Output" in an Index Only Scan node are the key columns, in order? * That's what we've observed, but I wanted to check if it was safe to make that assumption. - NOTE: IMHO, this is a case where showing all of the key columns, instead of just the ones that are used, is helpful because the person analyzing the query plan doesn't necessarily have direct access to the database schema. - In this example, policyperi_u_id_1mw8mh83lyyd9 is on pc_policyperiod(ID, Retired, JobID, PolicyID, TemporaryBranch, MostRecentModel) - NOTE: PolicyID is referenced in a node above the Index Only Scan, but neither JobID nor MostRecentModel are. "Plans": [ { "Node Type": "Index Only Scan", "Parent Relationship": "Outer", "Parallel Aware": false, "Scan Direction": "Forward", "Index Name": "policyperi_u_id_1mw8mh83lyyd9", "Relation Name": "pc_policyperiod", "Schema": "public", "Alias": "qroots0_1", "Startup Cost": 0.57, "Total Cost": 15.90, "Plan Rows": 10, "Plan Width": 8, "Actual Startup Time": 0.234, "Actual Total Time": 1.223, "Actual Rows": 203, "Actual Loops": 1, * "Output": ["qroots0_1.id <http://qroots0_1.id>", "qroots0_1.retired", "qroots0_1.jobid", "qroots0_1.policyid", "qroots0_1.temporarybranch", "qroots0_1.mostrecentmodel"],* "Index Cond": "((qroots0_1.id = ANY ($4)) AND (qroots0_1.retired = 0) AND (qroots0_1.temporarybranch = false))", *Index Scan* - *Is it safe to assume that the columns listed are all of the columns in the table?* (The table has too many columns to verify.) { "Node Type": "Index Scan", "Parent Relationship": "Inner", "Parallel Aware": false, "Scan Direction": "Forward", "Index Name": "ppperf10", "Relation Name": "pc_policyperiod", "Schema": "public", "Alias": "groot_1", "Startup Cost": 485987.94, "Total Cost": 485990.69, "Plan Rows": 1, "Plan Width": 16, "Actual Startup Time": 5.710, "Actual Total Time": 5.710, "Actual Rows": 0, "Actual Loops": 117, * "Output": ["groot_1.paymentinstrument_wmic", "groot_1.cipminretainedpremium_wmic", "groot_1.pendingreindex", "groot_1.locked", "groot_1.editeffectivedate", "groot_1.invoicingmethod", "groot_1.archivestate", "groot_1.archiveschemainfo", "groot_1.prioraddressfk_ext", "groot_1.locationautonumberseq", "groot_1.csioid_ext", "groot_1.updatetime", "groot_1.multiproddiscapplied_wmic", "groot_1.paymentdesc_wmic", "groot_1.id <http://groot_1.id>", "groot_1.singlecheckingpatterncode", "groot_1.billingmethod", "groot_1.fleetdiscount_wmic", "groot_1.createuserid", "groot_1.cp_auditwrapuplblty_wmic", "groot_1.totalcostourshare", "groot_1.allowgapsbefore", "groot_1.quoteidentifier", "groot_1.quotehidden", "groot_1.orphaned", "groot_1.beanversion", "groot_1.packagediscount_wmic", "groot_1.billtoescrow_wmic", "groot_1.insurerdenieddetail_wmic", "groot_1.isprimarypayerremoved_wmic", "groot_1.branchname", "groot_1.updateuserid", "groot_1.cancellationdate", "groot_1.temporarybranch", "groot_1.segment", "groot_1.primaryinsuredname", "groot_1.archivedentitypurgedate", "groot_1.showtaxexemption_wmic", "groot_1.vestinginformation_wmic", "groot_1.depositoverridepct", "groot_1.policytermid", "groot_1.othercurrentcarrier_wmic", "groot_1.periodstart", "groot_1.livestockclaimscount_wmic", "groot_1.selectedtermtype", "groot_1.claimsystemqueried_wmic", "groot_1.publicid", "groot_1.cpprogramdetails_wmic", "groot_1.commission_wmic", "groot_1.altbillingaccountnumber", "groot_1.writtendate", "groot_1.totalcostrpt", "groot_1.totalcostrpt_cur", "groot_1.ecollectanddistributedisc_wmic", "groot_1.suppressdocdistribution_wmic", "groot_1.mostrecentmodel", "groot_1.buildingclaimscount_wmic", "groot_1.ignorestatusforrequote_wmic", "groot_1.fleetdiscountvalue_wmic", "groot_1.taxexemptionreason_wmic", "groot_1.docpreferredlanguage_wmic", "groot_1.allocationofremainder", "groot_1.overridebillingallocation", "groot_1.currentcarrier_wmic", "groot_1.subscription_wmic", "groot_1.renewalsafterdefaulttrig_wmic", "groot_1.archivefailuredetailsid", "groot_1.modeldate", "groot_1.leadpolicynumber_wmic", "groot_1.brokerquotedpremium_wmic", "groot_1.invoicestreamcode", "groot_1.frozensetid", "groot_1.taxsurchargesrpt_cur", "groot_1.modelnumberindex", "groot_1.basestate", "groot_1.machineryclaimscount_wmic", "groot_1.quotedate_wmic", "groot_1.firstinsurance_wmic", "groot_1.minimumpremium_wmic", "groot_1.mostrecentmodelindex", "groot_1.archivepartition", "groot_1.taxexemptionnumber_wmic", "groot_1.termtype_wmic", "groot_1.subscriptionourrole_wmic", "groot_1.depositcollected", "groot_1.cp_auditbrannualrevenue_wmic", "groot_1.failedooseevaluation", "groot_1.branchnumber", "groot_1.transactioncostrpt", "groot_1.depositcollected_cur", "groot_1.busopsdesc_wmic", "groot_1.transactioncostrpt_cur", "groot_1.cipcommisionpercentage_wmic", "groot_1.basedonid", "groot_1.archivedate", "groot_1.billimmediatelypercentage", "groot_1.suppressformdistribution_wmic", "groot_1.quotecloneoriginalperiod", "groot_1.depositamount", "groot_1.periodend", "groot_1.preferredcoveragecurrency", "groot_1.waivebrokerfees_wmic", "groot_1.preferredsettlementcurrency", "groot_1.transactioncostrptci_ext_amt", "groot_1.persistency_wmic", "groot_1.wasperiodquotedbeforeclosed", "groot_1.maturedriverdiscount_wmic", "groot_1.basedondate", "groot_1.totalpremiumrpt", "groot_1.totalpremiumrpt_cur", "groot_1.fullyretainedpremium_wmic", "groot_1.nameofprincipals_wmic", "groot_1.validreinsurance", "groot_1.seriescheckingpatterncode", "groot_1.taxexemption_wmic", "groot_1.donotdestroy", "groot_1.pnicontactdenorm", "groot_1.editlocked", "groot_1.quotematuritylevel", "groot_1.rateasofdate", "groot_1.jobid", "groot_1.multiproddiscpolicy_wmic", "groot_1.uwcompany", "groot_1.estimatedpremium", "groot_1.addfollowupnotes_wmic", "groot_1.periodid", "groot_1.estimatedpremium_cur", "groot_1.insurerdenied_wmic", "groot_1.assignedrisk", "groot_1.transactionpremiumrpt", "groot_1.sourceofbusiness_wmic", "groot_1.currentinceptiondate_wmic", "groot_1.excludereason", "groot_1.accountorgtype_wmic", "groot_1.specialhandling", "groot_1.temporaryclonestatus", "groot_1.transactionpremiumrpt_cur", "groot_1.checknumber_wmic", "groot_1.isconsent_wmic", "groot_1.certificatenumber", "groot_1.cipyearsofexperience_wmic", "groot_1.archivefailureid", "groot_1.totalcostourshare_cur", "groot_1.failedoosevalidation", "groot_1.retired", "groot_1.personalinsuranceprogram", "groot_1.quotenumber_wmic", "groot_1.preempted", "groot_1.futureperiods", "groot_1.primaryinsurednamedenorm", "groot_1.brokerclientid_wmic", "groot_1.modelnumber", "groot_1.cipolicytype_ext", "groot_1.termnumber", "groot_1.waivedepositchange", "groot_1.producercodeofrecordid", "groot_1.cp_auditbdlyinjurypropdmg_wmic", "groot_1.cp_renewalcount_wmic", "groot_1.createtime", "groot_1.industrycode", "groot_1.cipminretainedamount_wmic", "groot_1.describesourceofbus_wmic", "groot_1.policyid", "groot_1.followupaltaccnum_wmic", "groot_1.excludedfromarchive", "groot_1.followbillmethod_wmic", "groot_1.csioagencyid_ext", "groot_1.currentpolicynumber_wmic", "groot_1.taxsurchargesrpt", "groot_1.currentexpdate_wmic", "groot_1.otherorgtypedescription_wmic", "groot_1.overrideprequal_wmic", "groot_1.yearbusinessstarted_wmic", "groot_1.quoteclonesequencenumber", "groot_1.lockingcolumn", "groot_1.refundcalcmethod", "groot_1.status", "groot_1.totalpremiumcostourshare", "groot_1.transactioncostrptci_ext_cur", "groot_1.totalpremiumcostourshare_cur", "groot_1.depositamount_cur", "groot_1.commissionoverride_wmic", "groot_1.policynumber", "groot_1.worksetuid", "groot_1.appeventsyncstatus", "groot_1.isfacagreementadded_ext", "groot_1.bp_lockactive_ext"]*, "Index Cond": "((groot_1.mostrecentmodel = true) AND (groot_1.temporarybranch = false) AND (groot_1.retired = 0) AND (groot_1.policyid = qroots0_1.policyid))", Thanks, Jerry -
Re: Questions about "Output" in EXPLAIN ANALYZE VERBOSE
Jeff Janes <jeff.janes@gmail.com> — 2024-01-02T19:15:52Z
On Tue, Jan 2, 2024 at 1:29 PM Jerry Brenner <jbrenner@guidewire.com> wrote: > We are currently on 13.9. > Why not just use the latest minor release, 13.13? For security reasons, that is the only minor release of v13 you should be using anyway. I think it is a bit much to hope that people will spend their time for free researching obsolete minor releases. > *Any node type accessing an index or table* > > - It looks like "Output" includes more than just the columns with > predicates and/or being accessed or returned in other nodes. > > Not in my hands. For SELECTs it just lists the columns that are needed. Your example is hard to follow because it appears to be just snippets of a plan, with no example of the query to which it belongs. Cheers, Jeff
-
Re: Questions about "Output" in EXPLAIN ANALYZE VERBOSE
Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-02T19:23:36Z
Jeff Janes <jeff.janes@gmail.com> writes: > On Tue, Jan 2, 2024 at 1:29 PM Jerry Brenner <jbrenner@guidewire.com> wrote: >> - It looks like "Output" includes more than just the columns with >> predicates and/or being accessed or returned in other nodes. > Not in my hands. For SELECTs it just lists the columns that are needed. It depends. The planner may choose to tell a non-top-level scan node to return all columns, in hopes of saving a tuple projection step at runtime. That's heuristic and depends on a number of factors, so you shouldn't count on it happening or not happening. regards, tom lane
-
Re: Questions about "Output" in EXPLAIN ANALYZE VERBOSE
Jerry Brenner <jbrenner@guidewire.com> — 2024-01-02T21:27:23Z
Tom - Thanks for the response. I guess what I am really looking for is a simple way to find all of the columns referenced from a given instance of a table or index from the json file, although it would be even better if it was easy to differentiate between the columns that came from the index vs those that could only come from the table. (We may not have direct access to the database, the indexing may have changed since the plan was captured, ...) I can see that all of the values in "Index Cond" and "Filter" for the given "Alias" are relevant, but it's unclear what portion of the values in "Output" are relevant. Some instances of "Output" contain a superset of the values in "Index Cond" and "Filter", including columns that are not referenced in the query (there are a total of 187 columns in pc_policyperiod and instances in the plan were all of them show up in "Output" but only 7 of them are actually referenced), others contain a mutually exclusive set of values, ... It would be helpful if there was an attribute that contained that information. I now see that groot2.ID is the only column listed in "Output" from pc_policy here and it doesn't show up anywhere else in the plan. It's actually used in the evaluation of "(hashed SubPlan 3)", but I had to look at the SQL to figure that out. NOTE: policy_n_producerco_3e8i0ojsyckhx is an index on pc_policy(producercodeofserviceid, retired). It makes sense for reasons beyond this query to add id as the last key column to the index. "Filter": "((NOT groot_1.assignedrisk) AND ((groot_1.producercodeofrecordid = '10791'::bigint) OR *(hashed SubPlan 3)* ))", "Rows Removed by Filter": 0, "Shared Hit Blocks": 549472, "Shared Read Blocks": 0, "Shared Dirtied Blocks": 0, "Shared Written Blocks": 0, "Local Hit Blocks": 0, "Local Read Blocks": 0, "Local Dirtied Blocks": 0, "Local Written Blocks": 0, "Temp Read Blocks": 0, "Temp Written Blocks": 0, "I/O Read Time": 0.000, "I/O Write Time": 0.000, "Plans": [ { "Node Type": "Index Scan", "Parent Relationship": "SubPlan", "Subplan Name": "SubPlan 3", "Parallel Aware": false, "Scan Direction": "Forward", "Index Name": "policy_n_producerco_3e8i0ojsyckhx", "Relation Name": "pc_policy", "Schema": "public", "Alias": "groot2", "Startup Cost": 0.56, "Total Cost": 484540.46, "Plan Rows": 578767, "Plan Width": 8, "Actual Startup Time": 0.035, "Actual Total Time": 490.349, "Actual Rows": 546045, "Actual Loops": 1, *"Output": ["groot2.id <http://groot2.id>"],* "Index Cond": "((groot2.producercodeofserviceid = '10791'::bigint) AND (groot2.retired = 0))", Here's the SQL: SELECT COUNT(*) FROM ( SELECT * FROM ( SELECT /* ISNULL:pc_policycontactrole.EffectiveDate:, ISNULL:pc_policycontactrole.ExpirationDate:, pc:gw.webservice.pc.pc5000.policysearch.PolicySearchAPI#findPolicies_WMIC; */ gRoot.ID col0 FROM pc_policyperiod gRoot WHERE gRoot.AssignedRisk = $1 AND gRoot.MostRecentModel = $2 AND gRoot.PolicyID IN ( SELECT qRoots0.PolicyID col0 FROM pc_policyperiod qRoots0 WHERE qRoots0.ID = ANY (ARRAY ( SELECT qRoots1.BranchID col0 FROM pc_policycontactrole qRoots1 WHERE qRoots1.Subtype = $3 AND qRoots1.ContactDenorm IN ( SELECT qRoots2.ID col0 FROM pc_contact qRoots2 WHERE qRoots2.FirstNameDenorm = LOWER ($4) AND qRoots2.LastNameDenorm = LOWER ($5) AND qRoots2.Retired = 0) AND ( ( ( (qRoots1.EffectiveDate <> qRoots1.ExpirationDate) OR (qRoots1.EffectiveDate IS NULL) OR (qRoots1.ExpirationDate IS NULL)))))) AND qRoots0.Retired = 0 AND qRoots0.TemporaryBranch = false) AND gRoot.Retired = 0 AND gRoot.TemporaryBranch = false AND ( ( ( (gRoot.ProducerCodeOfRecordID = $6) OR (gRoot.PolicyID IN ( SELECT gRoot3.ID col0 FROM pc_policy gRoot3 WHERE gRoot3.ProducerCodeOfServiceID = $7 AND gRoot3.Retired = 0))))) UNION SELECT /* ISNULL:pc_policycontactrole.EffectiveDate:, ISNULL:pc_policycontactrole.ExpirationDate:, pc:gw.webservice.pc.pc5000.policysearch.PolicySearchAPI#findPolicies_WMIC; */ gRoot.ID col0 FROM pc_policyperiod gRoot WHERE gRoot.AssignedRisk = $8 AND gRoot.MostRecentModel = $9 AND gRoot.PolicyID IN ( SELECT qRoots0.PolicyID col0 FROM pc_policyperiod qRoots0 WHERE qRoots0.ID = ANY (ARRAY ( SELECT qRoots1.BranchID col0 FROM pc_policycontactrole qRoots1 WHERE qRoots1.Subtype = $10 AND qRoots1.FirstNameInternalDenorm = LOWER ($11) AND qRoots1.LastNameInternalDenorm = LOWER ($12) AND ( ( ( (qRoots1.EffectiveDate <> qRoots1.ExpirationDate) OR (qRoots1.EffectiveDate IS NULL) OR (qRoots1.ExpirationDate IS NULL)))))) AND qRoots0.Retired = 0 AND qRoots0.TemporaryBranch = false) AND gRoot.Retired = 0 AND gRoot.TemporaryBranch = false AND ( ( ( (gRoot.ProducerCodeOfRecordID = $13) OR (gRoot.PolicyID IN ( SELECT gRoot2.ID col0 FROM pc_policy gRoot2 WHERE gRoot2.ProducerCodeOfServiceID = $14 AND gRoot2.Retired = 0)))))) a FETCH FIRST 301 ROWS ONLY) countTable I've attached the full json because it is too big to paste (and "Output" doesn't show up in the text output of the tools that I've looked at). Thanks, Jerry On Tue, Jan 2, 2024 at 11:23 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Jeff Janes <jeff.janes@gmail.com> writes: > > On Tue, Jan 2, 2024 at 1:29 PM Jerry Brenner <jbrenner@guidewire.com> > wrote: > >> - It looks like "Output" includes more than just the columns with > >> predicates and/or being accessed or returned in other nodes. > > > Not in my hands. For SELECTs it just lists the columns that are needed. > > It depends. The planner may choose to tell a non-top-level scan node > to return all columns, in hopes of saving a tuple projection step at > runtime. That's heuristic and depends on a number of factors, so you > shouldn't count on it happening or not happening. > > regards, tom lane > >