explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Op4X

Settings
# exclusive inclusive rows x rows loops node
1. 378.234 7,804.283 ↓ 6.9 598,423 1

WindowAgg (cost=168,149.51..168,540.77 rows=86,946 width=410) (actual time=7,319.565..7,804.283 rows=598,423 loops=1)

2. 2,328.302 7,426.049 ↓ 6.9 598,423 1

Sort (cost=168,149.51..168,192.99 rows=86,946 width=402) (actual time=7,319.543..7,426.049 rows=598,423 loops=1)

  • Sort Key: (CASE WHEN ((a.name)::text = 'ReversalDue_Cancellation'::text) THEN a.reversal_document_number__c ELSE a.sap_message__c END)
  • Sort Method: external merge Disk: 134776kB
3. 1,286.951 5,097.747 ↓ 6.9 598,423 1

HashAggregate (cost=166,462.08..166,722.92 rows=86,946 width=402) (actual time=4,706.421..5,097.747 rows=598,423 loops=1)

  • Group Key: (CASE WHEN ((a.name)::text = 'ReversalDue_Cancellation'::text) THEN a.reversal_document_number__c ELSE a.sap_message__c END), (CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) THEN ''::character varying ELSE b.name END), b.sales_organization__c, a.company_code__c, a.sap_customer_code__c, (CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) THEN ''::character varying ELSE b.propstrength__charge_name__c END), a.revenue_amount_sap__c, a.due_st_cgst__c, a.due_sbc_sgst__c, (CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) THEN ('-'::text || (a.revenue_amount_sap__c)::text) WHEN ((a.propstrength__amount_due_type__c)::text = 'Forfeiture Amount'::text) THEN replace((b.amount_payable_text__c)::text, '-'::text, ''::text) ELSE (b.amount_payable_text__c)::text END), (CASE WHEN (((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) OR ((a.due_st_cgst__c + a.due_sbc_sgst__c) > '0'::double precision)) THEN ('-'::text || ((a.due_st_cgst__c + a.due_sbc_sgst__c))::text) WHEN (((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) AND ((a.due_st_cgst__c + a.due_sbc_sgst__c) = '0'::double precision)) THEN ((a.due_st_cgst__c + a.due_sbc_sgst__c))::text WHEN ((a.propstrength__amount_due_type__c)::text = 'Forfeiture Amount'::text) THEN replace((b.total_tax_on_forfeiture_amount__c)::text, '-'::text, ''::text) ELSE (b.propstrength__total_due_service_tax__c)::text END), c.booking_status__c, b.amount_types__c, a.type_doc__c, (CASE WHEN (a.propstrength__billing_date__c IS NOT NULL) THEN a.propstrength__billing_date__c ELSE a.propstrength__payment_date__c END), a.reversal_document_number__c, a.legacy_data_no_sap_posting__c, a.propstrength__amount_due_type__c, CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Reversal payment due'::text) THEN a.reversal_document_number__c ELSE a.sap_message__c END, a.propstrength__project__c, CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) THEN NULL::text ELSE (b.lastmodifieddate)::text END, b.profit_centres__c, a.property__c
4. 682.147 3,810.796 ↓ 1.4 601,158 1

Group (cost=105,532.35..160,158.44 rows=434,734 width=420) (actual time=2,493.817..3,810.796 rows=601,158 loops=1)

  • Group Key: (CASE WHEN ((a.name)::text = 'ReversalDue_Cancellation'::text) THEN a.reversal_document_number__c ELSE a.sap_message__c END), (CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) THEN ''::character varying ELSE b.name END), b.sales_organization__c, a.company_code__c, a.sap_customer_code__c, (CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) THEN ''::character varying ELSE b.propstrength__charge_name__c END), a.revenue_amount_sap__c, a.due_st_cgst__c, a.due_sbc_sgst__c, (CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) THEN ('-'::text || (a.revenue_amount_sap__c)::text) WHEN ((a.propstrength__amount_due_type__c)::text = 'Forfeiture Amount'::text) THEN replace((b.amount_payable_text__c)::text, '-'::text, ''::text) ELSE (b.amount_payable_text__c)::text END), (CASE WHEN (((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) OR ((a.due_st_cgst__c + a.due_sbc_sgst__c) > '0'::double precision)) THEN ('-'::text || ((a.due_st_cgst__c + a.due_sbc_sgst__c))::text) WHEN (((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) AND ((a.due_st_cgst__c + a.due_sbc_sgst__c) = '0'::double precision)) THEN ((a.due_st_cgst__c + a.due_sbc_sgst__c))::text WHEN ((a.propstrength__amount_due_type__c)::text = 'Forfeiture Amount'::text) THEN replace((b.total_tax_on_forfeiture_amount__c)::text, '-'::text, ''::text) ELSE (b.propstrength__total_due_service_tax__c)::text END), c.booking_status__c, b.amount_types__c, a.type_doc__c, (CASE WHEN (a.propstrength__billing_date__c IS NOT NULL) THEN a.propstrength__billing_date__c ELSE a.propstrength__payment_date__c END), a.reversal_document_number__c, a.legacy_data_no_sap_posting__c, a.propstrength__amount_due_type__c, a.sap_message__c, a.propstrength__project__c, b.lastmodifieddate, b.profit_centres__c, a.property__c
5. 504.413 3,128.649 ↓ 1.7 601,224 1

Gather Merge (cost=105,532.35..149,036.49 rows=362,278 width=356) (actual time=2,493.805..3,128.649 rows=601,224 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 205.927 2,624.236 ↓ 1.1 200,408 3 / 3

Group (cost=104,532.35..109,241.96 rows=181,139 width=356) (actual time=2,395.706..2,624.236 rows=200,408 loops=3)

  • Group Key: (CASE WHEN ((a.name)::text = 'ReversalDue_Cancellation'::text) THEN a.reversal_document_number__c ELSE a.sap_message__c END), (CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) THEN ''::character varying ELSE b.name END), b.sales_organization__c, a.company_code__c, a.sap_customer_code__c, (CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) THEN ''::character varying ELSE b.propstrength__charge_name__c END), a.revenue_amount_sap__c, a.due_st_cgst__c, a.due_sbc_sgst__c, (CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) THEN ('-'::text || (a.revenue_amount_sap__c)::text) WHEN ((a.propstrength__amount_due_type__c)::text = 'Forfeiture Amount'::text) THEN replace((b.amount_payable_text__c)::text, '-'::text, ''::text) ELSE (b.amount_payable_text__c)::text END), (CASE WHEN (((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) OR ((a.due_st_cgst__c + a.due_sbc_sgst__c) > '0'::double precision)) THEN ('-'::text || ((a.due_st_cgst__c + a.due_sbc_sgst__c))::text) WHEN (((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) AND ((a.due_st_cgst__c + a.due_sbc_sgst__c) = '0'::double precision)) THEN ((a.due_st_cgst__c + a.due_sbc_sgst__c))::text WHEN ((a.propstrength__amount_due_type__c)::text = 'Forfeiture Amount'::text) THEN replace((b.total_tax_on_forfeiture_amount__c)::text, '-'::text, ''::text) ELSE (b.propstrength__total_due_service_tax__c)::text END), c.booking_status__c, b.amount_types__c, a.type_doc__c, (CASE WHEN (a.propstrength__billing_date__c IS NOT NULL) THEN a.propstrength__billing_date__c ELSE a.propstrength__payment_date__c END), a.reversal_document_number__c, a.legacy_data_no_sap_posting__c, a.propstrength__amount_due_type__c, a.sap_message__c, a.propstrength__project__c, b.lastmodifieddate, b.profit_centres__c, a.property__c
7. 1,328.235 2,418.309 ↓ 1.1 200,541 3 / 3

Sort (cost=104,532.35..104,622.92 rows=181,139 width=356) (actual time=2,395.693..2,418.309 rows=200,541 loops=3)

  • Sort Key: (CASE WHEN ((a.name)::text = 'ReversalDue_Cancellation'::text) THEN a.reversal_document_number__c ELSE a.sap_message__c END), (CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) THEN ''::character varying ELSE b.name END), b.sales_organization__c, a.company_code__c, a.sap_customer_code__c, (CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) THEN ''::character varying ELSE b.propstrength__charge_name__c END), a.revenue_amount_sap__c, a.due_st_cgst__c, a.due_sbc_sgst__c, (CASE WHEN ((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) THEN ('-'::text || (a.revenue_amount_sap__c)::text) WHEN ((a.propstrength__amount_due_type__c)::text = 'Forfeiture Amount'::text) THEN replace((b.amount_payable_text__c)::text, '-'::text, ''::text) ELSE (b.amount_payable_text__c)::text END), (CASE WHEN (((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) OR ((a.due_st_cgst__c + a.due_sbc_sgst__c) > '0'::double precision)) THEN ('-'::text || ((a.due_st_cgst__c + a.due_sbc_sgst__c))::text) WHEN (((a.propstrength__amount_due_type__c)::text = 'Normal Credit Note'::text) AND ((a.due_st_cgst__c + a.due_sbc_sgst__c) = '0'::double precision)) THEN ((a.due_st_cgst__c + a.due_sbc_sgst__c))::text WHEN ((a.propstrength__amount_due_type__c)::text = 'Forfeiture Amount'::text) THEN replace((b.total_tax_on_forfeiture_amount__c)::text, '-'::text, ''::text) ELSE (b.propstrength__total_due_service_tax__c)::text END), c.booking_status__c, b.amount_types__c, a.type_doc__c, (CASE WHEN (a.propstrength__billing_date__c IS NOT NULL) THEN a.propstrength__billing_date__c ELSE a.propstrength__payment_date__c END), a.reversal_document_number__c, a.legacy_data_no_sap_posting__c, a.propstrength__amount_due_type__c, a.sap_message__c, a.propstrength__project__c, b.lastmodifieddate, b.profit_centres__c, a.property__c
  • Sort Method: quicksort Memory: 52171kB
  • Worker 0: Sort Method: quicksort Memory: 74510kB
  • Worker 1: Sort Method: quicksort Memory: 53890kB
8. 569.774 1,090.074 ↓ 1.1 200,541 3 / 3

Parallel Hash Join (cost=38,330.26..101,368.44 rows=181,139 width=356) (actual time=234.078..1,090.074 rows=200,541 loops=3)

  • Hash Cond: ((b.payment_id_name__c)::text = (a.name)::text)
9. 286.793 286.793 ↑ 1.3 464,907 3 / 3

Parallel Seq Scan on propstrength__other_charge_due_partition__c b (cost=0.00..59,834.66 rows=588,201 width=97) (actual time=0.014..286.793 rows=464,907 loops=3)

  • Filter: ((project_18_digit__c)::text <> 'a1l6F000002dTpoQAE'::text)
  • Rows Removed by Filter: 641
10. 49.477 233.507 ↓ 1.0 62,898 3 / 3

Parallel Hash (cost=38,110.92..38,110.92 rows=62,670 width=183) (actual time=233.507..233.507 rows=62,898 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 34656kB
11. 44.682 184.030 ↓ 1.0 62,898 3 / 3

Parallel Hash Join (cost=6,988.26..38,110.92 rows=62,670 width=183) (actual time=16.309..184.030 rows=62,898 loops=3)

  • Hash Cond: ((a.propstrength__application_booking__c)::text = (c.sfid)::text)
12. 123.158 123.158 ↑ 1.0 62,898 3 / 3

Parallel Seq Scan on propstrength__received_payment__c a (cost=0.00..31,089.15 rows=63,796 width=188) (actual time=0.018..123.158 rows=62,898 loops=3)

  • Filter: ((NOT legacy_data_no_sap_posting__c) AND ((sap_message__c)::text <> ''::text) AND ((sap_message__c)::text <> ''::text) AND ((type_doc__c)::text <> 'BR'::text) AND ((propstrength__amount_due_type__c)::text = ANY ('{"Credit Note","Forfeiture Amount","Input Tax Credit",Installment,"Reversal Input Tax Credit","Reversal payment due",ReversalDue_Cancellation,VAT,"VAT Reversal",Interest,"Cheque Bouncing Charges","Normal Credit Note","Reinstatement Fees"}'::text[])))
  • Rows Removed by Filter: 100906
13. 2.894 16.190 ↑ 1.3 8,699 3 / 3

Parallel Hash (cost=6,949.48..6,949.48 rows=11,081 width=33) (actual time=16.189..16.190 rows=8,699 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 2176kB
14. 13.296 13.296 ↑ 1.3 8,699 3 / 3

Parallel Seq Scan on propstrength__application_booking__c c (cost=0.00..6,949.48 rows=11,081 width=33) (actual time=0.014..13.296 rows=8,699 loops=3)

  • Filter: ((booking_status__c)::text = ANY ('{Booking_Cancelled,"Deal Approved"}'::text[]))
  • Rows Removed by Filter: 154
Planning time : 1.310 ms
Execution time : 7,855.923 ms