explain-subquery-subplan-verbose-on.json
application/json
Filename: explain-subquery-subplan-verbose-on.json
Type: application/json
Part: 0
[
{
"Plan": {
"Node Type": "Aggregate",
"Strategy": "Plain",
"Partial Mode": "Simple",
"Parallel Aware": false,
"Startup Cost": 9720689.23,
"Total Cost": 9720689.24,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 1322.532,
"Actual Total Time": 1322.540,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["count(*)"],
"Shared Hit Blocks": 1101730,
"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": "Limit",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 9720688.52,
"Total Cost": 9720688.96,
"Plan Rows": 22,
"Plan Width": 8,
"Actual Startup Time": 1322.529,
"Actual Total Time": 1322.536,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["NULL::bigint"],
"Shared Hit Blocks": 1101730,
"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": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Alias": "a",
"Startup Cost": 9720688.52,
"Total Cost": 9720688.96,
"Plan Rows": 22,
"Plan Width": 8,
"Actual Startup Time": 1322.528,
"Actual Total Time": 1322.535,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["NULL::bigint"],
"Shared Hit Blocks": 1101730,
"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": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 9720688.52,
"Total Cost": 9720688.74,
"Plan Rows": 22,
"Plan Width": 8,
"Actual Startup Time": 1322.527,
"Actual Total Time": 1322.534,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["groot.id"],
"Group Key": ["groot.id"],
"Planned Partitions": 0,
"HashAgg Batches": 1,
"Peak Memory Usage": 24,
"Disk Usage": 0,
"Shared Hit Blocks": 1101730,
"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": "Append",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 486843.13,
"Total Cost": 9720688.46,
"Plan Rows": 22,
"Plan Width": 8,
"Actual Startup Time": 1322.525,
"Actual Total Time": 1322.532,
"Actual Rows": 0,
"Actual Loops": 1,
"Shared Hit Blocks": 1101730,
"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,
"Subplans Removed": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 486843.13,
"Total Cost": 4860762.26,
"Plan Rows": 11,
"Plan Width": 8,
"Actual Startup Time": 653.087,
"Actual Total Time": 653.092,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["groot.id"],
"Inner Unique": false,
"Shared Hit Blocks": 551099,
"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": "Nested Loop",
"Parent Relationship": "InitPlan",
"Subplan Name": "InitPlan 2 (returns $2)",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 1.39,
"Total Cost": 839.26,
"Plan Rows": 124,
"Plan Width": 8,
"Actual Startup Time": 0.055,
"Actual Total Time": 0.781,
"Actual Rows": 210,
"Actual Loops": 1,
"Output": ["qroots1.branchid"],
"Inner Unique": false,
"Shared Hit Blocks": 632,
"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 Only Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "nameaddrdenorm1",
"Relation Name": "pc_contact",
"Schema": "public",
"Alias": "qroots2",
"Startup Cost": 0.69,
"Total Cost": 3.66,
"Plan Rows": 43,
"Plan Width": 8,
"Actual Startup Time": 0.026,
"Actual Total Time": 0.086,
"Actual Rows": 81,
"Actual Loops": 1,
"Output": ["qroots2.lastnamedenorm", "qroots2.firstnamedenorm", "qroots2.citydenorm", "qroots2.state", "qroots2.postalcodedenorm", "qroots2.country", "qroots2.accountholdercount", "qroots2.subtype", "qroots2.retired", "qroots2.id"],
"Index Cond": "((qroots2.lastnamedenorm = 'hall'::text) AND (qroots2.firstnamedenorm = 'michelle'::text) AND (qroots2.retired = 0))",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 0,
"Shared Hit Blocks": 69,
"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
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "policycont_n_subtype_mcmpkk0fmwot",
"Relation Name": "pc_policycontactrole",
"Schema": "public",
"Alias": "qroots1",
"Startup Cost": 0.70,
"Total Cost": 14.91,
"Plan Rows": 452,
"Plan Width": 16,
"Actual Startup Time": 0.007,
"Actual Total Time": 0.008,
"Actual Rows": 3,
"Actual Loops": 81,
"Output": ["qroots1.subtype", "qroots1.contactdenorm", "qroots1.branchid", "qroots1.effectivedate", "qroots1.expirationdate"],
"Index Cond": "((qroots1.subtype = 20) AND (qroots1.contactdenorm = qroots2.id))",
"Rows Removed by Index Recheck": 0,
"Filter": "((qroots1.effectivedate <> qroots1.expirationdate) OR (qroots1.effectivedate IS NULL) OR (qroots1.expirationdate IS NULL))",
"Rows Removed by Filter": 0,
"Heap Fetches": 2,
"Shared Hit Blocks": 563,
"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
}
]
},
{
"Node Type": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 15.93,
"Total Cost": 16.03,
"Plan Rows": 10,
"Plan Width": 8,
"Actual Startup Time": 1.896,
"Actual Total Time": 1.926,
"Actual Rows": 117,
"Actual Loops": 1,
"Output": ["qroots0.policyid"],
"Group Key": ["qroots0.policyid"],
"Planned Partitions": 0,
"HashAgg Batches": 1,
"Peak Memory Usage": 48,
"Disk Usage": 0,
"Shared Hit Blocks": 1627,
"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 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",
"Startup Cost": 0.57,
"Total Cost": 15.90,
"Plan Rows": 10,
"Plan Width": 8,
"Actual Startup Time": 0.840,
"Actual Total Time": 1.832,
"Actual Rows": 203,
"Actual Loops": 1,
"Output": ["qroots0.id", "qroots0.retired", "qroots0.jobid", "qroots0.policyid", "qroots0.temporarybranch", "qroots0.mostrecentmodel"],
"Index Cond": "((qroots0.id = ANY ($2)) AND (qroots0.retired = 0) AND (qroots0.temporarybranch = false))",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 11,
"Shared Hit Blocks": 1627,
"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
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "ppperf10",
"Relation Name": "pc_policyperiod",
"Schema": "public",
"Alias": "groot",
"Startup Cost": 485987.94,
"Total Cost": 485990.69,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 5.565,
"Actual Total Time": 5.565,
"Actual Rows": 0,
"Actual Loops": 117,
"Output": ["groot.paymentinstrument_wmic", "groot.cipminretainedpremium_wmic", "groot.pendingreindex", "groot.locked", "groot.editeffectivedate", "groot.invoicingmethod", "groot.archivestate", "groot.archiveschemainfo", "groot.prioraddressfk_ext", "groot.locationautonumberseq", "groot.csioid_ext", "groot.updatetime", "groot.multiproddiscapplied_wmic", "groot.paymentdesc_wmic", "groot.id", "groot.singlecheckingpatterncode", "groot.billingmethod", "groot.fleetdiscount_wmic", "groot.createuserid", "groot.cp_auditwrapuplblty_wmic", "groot.totalcostourshare", "groot.allowgapsbefore", "groot.quoteidentifier", "groot.quotehidden", "groot.orphaned", "groot.beanversion", "groot.packagediscount_wmic", "groot.billtoescrow_wmic", "groot.insurerdenieddetail_wmic", "groot.isprimarypayerremoved_wmic", "groot.branchname", "groot.updateuserid", "groot.cancellationdate", "groot.temporarybranch", "groot.segment", "groot.primaryinsuredname", "groot.archivedentitypurgedate", "groot.showtaxexemption_wmic", "groot.vestinginformation_wmic", "groot.depositoverridepct", "groot.policytermid", "groot.othercurrentcarrier_wmic", "groot.periodstart", "groot.livestockclaimscount_wmic", "groot.selectedtermtype", "groot.claimsystemqueried_wmic", "groot.publicid", "groot.cpprogramdetails_wmic", "groot.commission_wmic", "groot.altbillingaccountnumber", "groot.writtendate", "groot.totalcostrpt", "groot.totalcostrpt_cur", "groot.ecollectanddistributedisc_wmic", "groot.suppressdocdistribution_wmic", "groot.mostrecentmodel", "groot.buildingclaimscount_wmic", "groot.ignorestatusforrequote_wmic", "groot.fleetdiscountvalue_wmic", "groot.taxexemptionreason_wmic", "groot.docpreferredlanguage_wmic", "groot.allocationofremainder", "groot.overridebillingallocation", "groot.currentcarrier_wmic", "groot.subscription_wmic", "groot.renewalsafterdefaulttrig_wmic", "groot.archivefailuredetailsid", "groot.modeldate", "groot.leadpolicynumber_wmic", "groot.brokerquotedpremium_wmic", "groot.invoicestreamcode", "groot.frozensetid", "groot.taxsurchargesrpt_cur", "groot.modelnumberindex", "groot.basestate", "groot.machineryclaimscount_wmic", "groot.quotedate_wmic", "groot.firstinsurance_wmic", "groot.minimumpremium_wmic", "groot.mostrecentmodelindex", "groot.archivepartition", "groot.taxexemptionnumber_wmic", "groot.termtype_wmic", "groot.subscriptionourrole_wmic", "groot.depositcollected", "groot.cp_auditbrannualrevenue_wmic", "groot.failedooseevaluation", "groot.branchnumber", "groot.transactioncostrpt", "groot.depositcollected_cur", "groot.busopsdesc_wmic", "groot.transactioncostrpt_cur", "groot.cipcommisionpercentage_wmic", "groot.basedonid", "groot.archivedate", "groot.billimmediatelypercentage", "groot.suppressformdistribution_wmic", "groot.quotecloneoriginalperiod", "groot.depositamount", "groot.periodend", "groot.preferredcoveragecurrency", "groot.waivebrokerfees_wmic", "groot.preferredsettlementcurrency", "groot.transactioncostrptci_ext_amt", "groot.persistency_wmic", "groot.wasperiodquotedbeforeclosed", "groot.maturedriverdiscount_wmic", "groot.basedondate", "groot.totalpremiumrpt", "groot.totalpremiumrpt_cur", "groot.fullyretainedpremium_wmic", "groot.nameofprincipals_wmic", "groot.validreinsurance", "groot.seriescheckingpatterncode", "groot.taxexemption_wmic", "groot.donotdestroy", "groot.pnicontactdenorm", "groot.editlocked", "groot.quotematuritylevel", "groot.rateasofdate", "groot.jobid", "groot.multiproddiscpolicy_wmic", "groot.uwcompany", "groot.estimatedpremium", "groot.addfollowupnotes_wmic", "groot.periodid", "groot.estimatedpremium_cur", "groot.insurerdenied_wmic", "groot.assignedrisk", "groot.transactionpremiumrpt", "groot.sourceofbusiness_wmic", "groot.currentinceptiondate_wmic", "groot.excludereason", "groot.accountorgtype_wmic", "groot.specialhandling", "groot.temporaryclonestatus", "groot.transactionpremiumrpt_cur", "groot.checknumber_wmic", "groot.isconsent_wmic", "groot.certificatenumber", "groot.cipyearsofexperience_wmic", "groot.archivefailureid", "groot.totalcostourshare_cur", "groot.failedoosevalidation", "groot.retired", "groot.personalinsuranceprogram", "groot.quotenumber_wmic", "groot.preempted", "groot.futureperiods", "groot.primaryinsurednamedenorm", "groot.brokerclientid_wmic", "groot.modelnumber", "groot.cipolicytype_ext", "groot.termnumber", "groot.waivedepositchange", "groot.producercodeofrecordid", "groot.cp_auditbdlyinjurypropdmg_wmic", "groot.cp_renewalcount_wmic", "groot.createtime", "groot.industrycode", "groot.cipminretainedamount_wmic", "groot.describesourceofbus_wmic", "groot.policyid", "groot.followupaltaccnum_wmic", "groot.excludedfromarchive", "groot.followbillmethod_wmic", "groot.csioagencyid_ext", "groot.currentpolicynumber_wmic", "groot.taxsurchargesrpt", "groot.currentexpdate_wmic", "groot.otherorgtypedescription_wmic", "groot.overrideprequal_wmic", "groot.yearbusinessstarted_wmic", "groot.quoteclonesequencenumber", "groot.lockingcolumn", "groot.refundcalcmethod", "groot.status", "groot.totalpremiumcostourshare", "groot.transactioncostrptci_ext_cur", "groot.totalpremiumcostourshare_cur", "groot.depositamount_cur", "groot.commissionoverride_wmic", "groot.policynumber", "groot.worksetuid", "groot.appeventsyncstatus", "groot.isfacagreementadded_ext", "groot.bp_lockactive_ext"],
"Index Cond": "((groot.mostrecentmodel = true) AND (groot.temporarybranch = false) AND (groot.retired = 0) AND (groot.policyid = qroots0.policyid))",
"Rows Removed by Index Recheck": 0,
"Filter": "((NOT groot.assignedrisk) AND ((groot.producercodeofrecordid = '10791'::bigint) OR (hashed SubPlan 1)))",
"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 1",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "policy_n_producerco_3e8i0ojsyckhx",
"Relation Name": "pc_policy",
"Schema": "public",
"Alias": "groot3",
"Startup Cost": 0.56,
"Total Cost": 484540.46,
"Plan Rows": 578767,
"Plan Width": 8,
"Actual Startup Time": 0.040,
"Actual Total Time": 474.717,
"Actual Rows": 546045,
"Actual Loops": 1,
"Output": ["groot3.id"],
"Index Cond": "((groot3.producercodeofserviceid = '10791'::bigint) AND (groot3.retired = 0))",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 548968,
"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
}
]
}
]
},
{
"Node Type": "Nested Loop",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 486006.74,
"Total Cost": 4859925.87,
"Plan Rows": 11,
"Plan Width": 8,
"Actual Startup Time": 669.435,
"Actual Total Time": 669.436,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["groot_1.id"],
"Inner Unique": false,
"Shared Hit Blocks": 550631,
"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 Only Scan",
"Parent Relationship": "InitPlan",
"Subplan Name": "InitPlan 4 (returns $4)",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "policycont_n_lastnamein_1ruwhzizsh2ek",
"Relation Name": "pc_policycontactrole",
"Schema": "public",
"Alias": "qroots1_1",
"Startup Cost": 0.70,
"Total Cost": 2.87,
"Plan Rows": 7,
"Plan Width": 8,
"Actual Startup Time": 0.044,
"Actual Total Time": 0.172,
"Actual Rows": 210,
"Actual Loops": 1,
"Output": ["qroots1_1.branchid"],
"Index Cond": "((qroots1_1.lastnameinternaldenorm = 'hall'::text) AND (qroots1_1.firstnameinternaldenorm = 'michelle'::text) AND (qroots1_1.subtype = 20))",
"Rows Removed by Index Recheck": 0,
"Filter": "((qroots1_1.effectivedate <> qroots1_1.expirationdate) OR (qroots1_1.effectivedate IS NULL) OR (qroots1_1.expirationdate IS NULL))",
"Rows Removed by Filter": 4,
"Heap Fetches": 2,
"Shared Hit Blocks": 164,
"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
},
{
"Node Type": "Aggregate",
"Strategy": "Hashed",
"Partial Mode": "Simple",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 15.93,
"Total Cost": 16.03,
"Plan Rows": 10,
"Plan Width": 8,
"Actual Startup Time": 1.286,
"Actual Total Time": 1.316,
"Actual Rows": 117,
"Actual Loops": 1,
"Output": ["qroots0_1.policyid"],
"Group Key": ["qroots0_1.policyid"],
"Planned Partitions": 0,
"HashAgg Batches": 1,
"Peak Memory Usage": 48,
"Disk Usage": 0,
"Shared Hit Blocks": 1159,
"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 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))",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 11,
"Shared Hit Blocks": 1159,
"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
}
]
},
{
"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))",
"Rows Removed by Index Recheck": 0,
"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"],
"Index Cond": "((groot2.producercodeofserviceid = '10791'::bigint) AND (groot2.retired = 0))",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 548968,
"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
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
"Planning": {
"Shared Hit Blocks": 607,
"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
},
"Planning Time": 3.916,
"Triggers": [
],
"Execution Time": 1331.044
}
]