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.)
- 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", "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", "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
