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
