explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bOpD

Settings
# exclusive inclusive rows x rows loops node
1. 13,378.038 143,615.059 ↓ 1.9 911,947 1

Subquery Scan on t (cost=143,736.26..207,327.90 rows=480,900 width=32) (actual time=128,111.010..143,615.059 rows=911,947 loops=1)

  • Output: row_to_json(t.*)
2. 0.000 130,237.021 ↓ 1.9 911,947 1

Gather Merge (cost=143,736.26..201,316.65 rows=480,900 width=896) (actual time=128,110.940..130,237.021 rows=911,947 loops=1)

  • Output: ((payments.pdoc #> '{confirmationNumber}'::text[])), ((payments.pdoc #> '{applicationId}'::text[])), ((payments.pdoc #> '{quoteId}'::text[])), ((payments.pdoc #> '{merchantId}'::text[])), ((payments.pdoc #> '{accountId}'::text[])), ((payments.pdoc #> '{DoB}'::text[])), ((payments.pdoc #> '{firstNm}'::text[])), ((payments.pdoc #> '{middleInitial}'::text[])), ((payments.pdoc #> '{LastNm}'::text[])), ((payments.pdoc #> '{groupNm}'::text[])), ((payments.pdoc #> '{groupNb}'::text[])), ((payments.pdoc #> '{confirmationEmail}'::text[])), ((payments.pdoc #> '{channel}'::text[])), (((payments.pdoc #> '{agentAgency}'::text[]) #> '{agentCode}'::text[])), ((payments.pdoc #> '{invoiceType}'::text[])), ((payments.pdoc #> '{products}'::text[])), ((payments.pdoc #> '{lastSuccessfulProcessedDt}'::text[])), (((payments.pdoc #> '{systemStatus}'::text[]) #>> '{statusDescription}'::text[])), ((payments.pdoc #> '{paymentMethodName}'::text[])), (((jae.striperesponses #> '{source}'::text[]) #> '{account_holder_name}'::text[])), (((jae.striperesponses #> '{outcome}'::text[]) #> '{reason}'::text[])), ((payments.pdoc #> '{amountToPay}'::text[])), ((payments.pdoc #> '{paymentMethodType}'::text[])), ((payments.pdoc #> '{paymentMethodName}'::text[])), (((jae.striperesponses #> '{source}'::text[]) #> '{account_use}'::text[])), ((payments.pdoc #> '{insertDtTm}'::text[])), ((payments.pdoc #> '{description}'::text[])), ((jae.striperesponses #> '{created}'::text[]))
  • Workers Planned: 4
  • Workers Launched: 4
3. 12,512.555 641,563.250 ↓ 1.5 182,389 5

Sort (cost=142,736.20..143,036.77 rows=120,225 width=896) (actual time=128,046.132..128,312.650 rows=182,389 loops=5)

  • Output: ((payments.pdoc #> '{confirmationNumber}'::text[])), ((payments.pdoc #> '{applicationId}'::text[])), ((payments.pdoc #> '{quoteId}'::text[])), ((payments.pdoc #> '{merchantId}'::text[])), ((payments.pdoc #> '{accountId}'::text[])), ((payments.pdoc #> '{DoB}'::text[])), ((payments.pdoc #> '{firstNm}'::text[])), ((payments.pdoc #> '{middleInitial}'::text[])), ((payments.pdoc #> '{LastNm}'::text[])), ((payments.pdoc #> '{groupNm}'::text[])), ((payments.pdoc #> '{groupNb}'::text[])), ((payments.pdoc #> '{confirmationEmail}'::text[])), ((payments.pdoc #> '{channel}'::text[])), (((payments.pdoc #> '{agentAgency}'::text[]) #> '{agentCode}'::text[])), ((payments.pdoc #> '{invoiceType}'::text[])), ((payments.pdoc #> '{products}'::text[])), ((payments.pdoc #> '{lastSuccessfulProcessedDt}'::text[])), (((payments.pdoc #> '{systemStatus}'::text[]) #>> '{statusDescription}'::text[])), ((payments.pdoc #> '{paymentMethodName}'::text[])), (((jae.striperesponses #> '{source}'::text[]) #> '{account_holder_name}'::text[])), (((jae.striperesponses #> '{outcome}'::text[]) #> '{reason}'::text[])), ((payments.pdoc #> '{amountToPay}'::text[])), ((payments.pdoc #> '{paymentMethodType}'::text[])), ((payments.pdoc #> '{paymentMethodName}'::text[])), (((jae.striperesponses #> '{source}'::text[]) #> '{account_use}'::text[])), ((payments.pdoc #> '{insertDtTm}'::text[])), ((payments.pdoc #> '{description}'::text[])), ((jae.striperesponses #> '{created}'::text[]))
  • Sort Key: ((jae.striperesponses #> '{created}'::text[])) DESC
  • Sort Method: external merge Disk: 64040kB
  • Worker 0: Sort Method: external merge Disk: 74816kB
  • Worker 1: Sort Method: external merge Disk: 81552kB
  • Worker 2: Sort Method: external merge Disk: 61528kB
  • Worker 3: Sort Method: external merge Disk: 61088kB
  • Worker 0: actual time=128067.141..128328.889 rows=198888 loops=1
  • Worker 1: actual time=128104.163..128466.221 rows=216875 loops=1
  • Worker 2: actual time=128010.006..128261.751 rows=163569 loops=1
  • Worker 3: actual time=128011.321..128231.049 rows=162390 loops=1
4. 572,067.924 629,050.695 ↓ 1.5 182,389 5

Nested Loop (cost=0.01..49,554.69 rows=120,225 width=896) (actual time=1.231..125,810.139 rows=182,389 loops=5)

  • Output: (payments.pdoc #> '{confirmationNumber}'::text[]), (payments.pdoc #> '{applicationId}'::text[]), (payments.pdoc #> '{quoteId}'::text[]), (payments.pdoc #> '{merchantId}'::text[]), (payments.pdoc #> '{accountId}'::text[]), (payments.pdoc #> '{DoB}'::text[]), (payments.pdoc #> '{firstNm}'::text[]), (payments.pdoc #> '{middleInitial}'::text[]), (payments.pdoc #> '{LastNm}'::text[]), (payments.pdoc #> '{groupNm}'::text[]), (payments.pdoc #> '{groupNb}'::text[]), (payments.pdoc #> '{confirmationEmail}'::text[]), (payments.pdoc #> '{channel}'::text[]), ((payments.pdoc #> '{agentAgency}'::text[]) #> '{agentCode}'::text[]), (payments.pdoc #> '{invoiceType}'::text[]), (payments.pdoc #> '{products}'::text[]), (payments.pdoc #> '{lastSuccessfulProcessedDt}'::text[]), ((payments.pdoc #> '{systemStatus}'::text[]) #>> '{statusDescription}'::text[]), (payments.pdoc #> '{paymentMethodName}'::text[]), ((jae.striperesponses #> '{source}'::text[]) #> '{account_holder_name}'::text[]), ((jae.striperesponses #> '{outcome}'::text[]) #> '{reason}'::text[]), (payments.pdoc #> '{amountToPay}'::text[]), (payments.pdoc #> '{paymentMethodType}'::text[]), (payments.pdoc #> '{paymentMethodName}'::text[]), ((jae.striperesponses #> '{source}'::text[]) #> '{account_use}'::text[]), (payments.pdoc #> '{insertDtTm}'::text[]), (payments.pdoc #> '{description}'::text[]), (jae.striperesponses #> '{created}'::text[])
  • Worker 0: actual time=1.224..125799.138 rows=198888 loops=1
  • Worker 1: actual time=1.149..125775.000 rows=216875 loops=1
  • Worker 2: actual time=1.204..125823.173 rows=163569 loops=1
  • Worker 3: actual time=1.696..125821.168 rows=162390 loops=1
5. 27,797.875 27,797.875 ↓ 151.8 182,406 5

Parallel Seq Scan on ppxstage.payments (cost=0.00..37,232.12 rows=1,202 width=106) (actual time=0.186..5,559.575 rows=182,406 loops=5)

  • Output: payments._id, payments.pdoc
  • Filter: (((payments.pdoc #> '{customerStatus}'::text[]) #>> '{statusCode}'::text[]) = 'C03'::text)
  • Rows Removed by Filter: 12708
  • Worker 0: actual time=0.219..5623.708 rows=198918 loops=1
  • Worker 1: actual time=0.220..5672.435 rows=216883 loops=1
  • Worker 2: actual time=0.202..5513.461 rows=163583 loops=1
  • Worker 3: actual time=0.249..5505.553 rows=162412 loops=1
6. 29,184.896 29,184.896 ↑ 100.0 1 912,028

Function Scan on pg_catalog.jsonb_array_elements jae (cost=0.01..1.00 rows=100 width=32) (actual time=0.031..0.032 rows=1 loops=912,028)

  • Output: jae.striperesponses
  • Function Call: jsonb_array_elements((payments.pdoc #> '{stripeResponses}'::text[]))
  • Worker 0: actual time=0.029..0.029 rows=1 loops=198918
  • Worker 1: actual time=0.027..0.027 rows=1 loops=216883
  • Worker 2: actual time=0.035..0.035 rows=1 loops=163583
  • Worker 3: actual time=0.035..0.035 rows=1 loops=162412
Planning time : 0.352 ms