explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iWlw

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Nested Loop (cost=619.61..20,881.87 rows=15,523 width=876) (actual rows= loops=)

  • Output: NULL::text, date_trunc('second'::text, endorsement.created), endorsement_invoice.number, endorsement.type, endorsement.short_term_premium_method, endorsement.effective, endorsement.expiration, endorsement.home_state, endorsement.policy_number, endorsement.policy_effective, endorsement.expiration, endorsement.binder_number, endorsement.placement_reason, endorsement_insured.name, endorsement_insured.mailing_address1, endorsement_insured.mailing_address2, endorsement_insured.mailing_city, endorsement_insured.mailing_state, endorsement_insured.mailing_postal_code, endorsement.application_type, endorsement.operations_description, (array_agg(DISTINCT (endorsement_invoice_line_item.line_of_business)::text ORDER BY (endorsement_invoice_line_item.line_of_business)::text)), CASE WHEN ((COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'PREMIUM'::text)))::bigint, '0'::bigint)) >= 0) THEN (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'PREMIUM'::text)))::bigint, '0'::bigint)) ELSE '0'::bigint END, CASE WHEN ((COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'PREMIUM'::text)))::bigint, '0'::bigint)) < 0) THEN (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'PREMIUM'::text)))::bigint, '0'::bigint)) ELSE '0'::bigint END, (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'AGENCY_COMMISSION'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'AGENT_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'CARRIER_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'CARRIER_INSPECTION_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'CLEARINGHOUSE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'EMPA'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'FIRE_MARSHALL_TAX'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'GROSS_COMMISSION'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'HEALTHY_HOMES'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'INSPECTION_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'MGA_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'OTHER'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'RISK_MANAGEMENT_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'STAMPING_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'STATE_TAX'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'WILLCOMPLY_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'WINDPOOL_FEE'::text)))::bigint, '0'::bigint))
  • Inner Unique: true
2. 0.000 0.000 ↓ 0.0

Nested Loop (cost=619.32..20,765.02 rows=1 width=311) (actual rows= loops=)

  • Output: endorsement_invoice.number, endorsement_invoice.endorsement_id, endorsement_invoice.due_to_endorsement_id, (array_agg(DISTINCT (endorsement_invoice_line_item.line_of_business)::text ORDER BY (endorsement_invoice_line_item.line_of_business)::text)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'PREMIUM'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'AGENCY_COMMISSION'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'AGENT_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'CARRIER_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'CARRIER_INSPECTION_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'CLEARINGHOUSE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'EMPA'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'FIRE_MARSHALL_TAX'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'GROSS_COMMISSION'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'HEALTHY_HOMES'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'INSPECTION_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'MGA_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'OTHER'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'RISK_MANAGEMENT_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'STAMPING_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'STATE_TAX'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'WILLCOMPLY_FEE'::text)))::bigint, '0'::bigint)), (COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'WINDPOOL_FEE'::text)))::bigint, '0'::bigint)), endorsement_insured.name, endorsement_insured.mailing_address1, endorsement_insured.mailing_address2, endorsement_insured.mailing_city, endorsement_insured.mailing_state, endorsement_insured.mailing_postal_code, endorsement_insured.endorsement_id, endorsement_insured.due_to_endorsement_id
  • Inner Unique: true
  • Join Filter: (endorsement_invoice.id = endorsement_invoice_line_item.invoice_id)
3. 0.000 0.000 ↓ 0.0

Hash Join (cost=618.90..1,237.50 rows=1 width=151) (actual rows= loops=)

  • Output: endorsement_invoice.number, endorsement_invoice.endorsement_id, endorsement_invoice.due_to_endorsement_id, endorsement_invoice.id, endorsement_insured.name, endorsement_insured.mailing_address1, endorsement_insured.mailing_address2, endorsement_insured.mailing_city, endorsement_insured.mailing_state, endorsement_insured.mailing_postal_code, endorsement_insured.endorsement_id, endorsement_insured.due_to_endorsement_id
  • Hash Cond: ((endorsement_insured.endorsement_id = endorsement_invoice.endorsement_id) AND (endorsement_insured.due_to_endorsement_id = endorsement_invoice.due_to_endorsement_id))
4. 0.000 0.000 ↓ 0.0

Seq Scan on reporting.endorsement_insured (cost=0.00..491.05 rows=17,005 width=96) (actual rows= loops=)

  • Output: endorsement_insured.id, endorsement_insured.endorsement_id, endorsement_insured.first_name, endorsement_insured.name, endorsement_insured.mailing_address1, endorsement_insured.mailing_address2, endorsement_insured.mailing_city, endorsement_insured.mailing_county, endorsement_insured.mailing_state, endorsement_insured.mailing_postal_code, endorsement_insured.mailing_country, endorsement_insured.due_to_endorsement_id
5. 0.000 0.000 ↓ 0.0

Hash (cost=364.56..364.56 rows=16,956 width=55) (actual rows= loops=)

  • Output: endorsement_invoice.number, endorsement_invoice.endorsement_id, endorsement_invoice.due_to_endorsement_id, endorsement_invoice.id
6. 0.000 0.000 ↓ 0.0

Seq Scan on reporting.endorsement_invoice (cost=0.00..364.56 rows=16,956 width=55) (actual rows= loops=)

  • Output: endorsement_invoice.number, endorsement_invoice.endorsement_id, endorsement_invoice.due_to_endorsement_id, endorsement_invoice.id
7. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.42..19,157.51 rows=16,445 width=192) (actual rows= loops=)

  • Output: endorsement_invoice_line_item.invoice_id, array_agg(DISTINCT (endorsement_invoice_line_item.line_of_business)::text ORDER BY (endorsement_invoice_line_item.line_of_business)::text), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'PREMIUM'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'AGENCY_COMMISSION'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'AGENT_FEE'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'CARRIER_FEE'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'CARRIER_INSPECTION_FEE'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'CLEARINGHOUSE'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'EMPA'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'FIRE_MARSHALL_TAX'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'GROSS_COMMISSION'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'HEALTHY_HOMES'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'INSPECTION_FEE'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'MGA_FEE'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'OTHER'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'RISK_MANAGEMENT_FEE'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'STAMPING_FEE'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'STATE_TAX'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'WILLCOMPLY_FEE'::text)))::bigint, '0'::bigint), COALESCE((sum(endorsement_invoice_line_item.amount) FILTER (WHERE ((endorsement_invoice_line_item.type)::text = 'WINDPOOL_FEE'::text)))::bigint, '0'::bigint)
  • Group Key: endorsement_invoice_line_item.invoice_id
8. 0.000 0.000 ↓ 0.0

Index Scan using endorsement_invoice_line_item_invoice_id_idx on reporting.endorsement_invoice_line_item (cost=0.42..8,067.19 rows=98,997 width=39) (actual rows= loops=)

  • Output: endorsement_invoice_line_item.id, endorsement_invoice_line_item.invoice_id, endorsement_invoice_line_item.broad_type, endorsement_invoice_line_item.type, endorsement_invoice_line_item.line_of_business, endorsement_invoice_line_item.description, endorsement_invoice_line_item.amount, endorsement_invoice_line_item.rate
9. 0.000 0.000 ↓ 0.0

Index Scan using endorsement_pkey on reporting.endorsement (cost=0.29..0.43 rows=1 width=617) (actual rows= loops=)

  • Output: endorsement.id, endorsement.policy_id, endorsement.created, endorsement.posted, endorsement.number, endorsement.type, endorsement.status, endorsement.description, endorsement.effective, endorsement.expiration, endorsement.bind, endorsement.issuance, endorsement.cancellation_code, endorsement.cancellation_description, endorsement.short_term_premium_method, endorsement.policy_effective, endorsement.policy_number, endorsement.home_state, endorsement.binder_number, endorsement.placement_reason, endorsement.application_type, endorsement.admitted, endorsement.operations_description, endorsement.due_to_endorsement_id, endorsement.invalidates_endorsement_id, endorsement.invoiced
  • Index Cond: ((endorsement.id = endorsement_invoice.endorsement_id) AND (endorsement.due_to_endorsement_id = endorsement_invoice.due_to_endorsement_id))
  • Filter: (NOT endorsement.admitted)