explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3uML

Settings
# exclusive inclusive rows x rows loops node
1. 7,628.496 79,354.564 ↓ 2.0 447,848 1

Subquery Scan on t (cost=57,374.64..86,584.10 rows=223,257 width=32) (actual time=70,628.158..79,354.564 rows=447,848 loops=1)

  • Output: row_to_json(t.*)
2. 0.000 71,726.068 ↓ 2.0 447,848 1

Gather Merge (cost=57,374.64..83,793.39 rows=223,257 width=928) (actual time=70,628.111..71,726.068 rows=447,848 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) -> 0) -> '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)), ((jae.striperesponses -> 'status'::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: 3
  • Workers Launched: 3
3. 4,875.368 283,113.364 ↓ 1.5 111,962 4

Sort (cost=56,374.60..56,560.65 rows=74,419 width=928) (actual time=70,608.112..70,778.341 rows=111,962 loops=4)

  • 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) -> 0) -> '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)), ((jae.striperesponses -> 'status'::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: 43840kB
  • Worker 0: Sort Method: external merge Disk: 43488kB
  • Worker 1: Sort Method: external merge Disk: 44304kB
  • Worker 2: Sort Method: external merge Disk: 45144kB
  • Worker 0: actual time=70593.266..70758.043 rows=110201 loops=1
  • Worker 1: actual time=70613.589..70783.129 rows=112263 loops=1
  • Worker 2: actual time=70618.276..70790.663 rows=114357 loops=1
4. 253,299.248 278,237.996 ↓ 1.5 111,962 4

Nested Loop (cost=0.01..32,622.40 rows=74,419 width=928) (actual time=1.000..69,559.499 rows=111,962 loops=4)

  • 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) -> 0) -> '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), (jae.striperesponses -> 'status'::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=0.953..69504.584 rows=110201 loops=1
  • Worker 1: actual time=0.984..69548.356 rows=112263 loops=1
  • Worker 2: actual time=1.045..69610.666 rows=114357 loops=1
5. 12,398.248 12,398.248 ↓ 150.5 111,969 4

Parallel Seq Scan on ppxtest.payments (cost=0.00..24,622.73 rows=744 width=59) (actual time=0.257..3,099.562 rows=111,969 loops=4)

  • Output: payments._id, payments.pdoc, payments.tsv
  • Filter: (((payments.pdoc -> 'customerStatus'::text) ->> 'statusCode'::text) = 'C03'::text)
  • Rows Removed by Filter: 4023
  • Worker 0: actual time=0.261..3113.876 rows=110220 loops=1
  • Worker 1: actual time=0.298..3123.940 rows=112264 loops=1
  • Worker 2: actual time=0.298..3132.258 rows=114358 loops=1
6. 12,540.500 12,540.500 ↑ 100.0 1 447,875

Function Scan on pg_catalog.jsonb_array_elements jae (cost=0.01..1.00 rows=100 width=32) (actual time=0.028..0.028 rows=1 loops=447,875)

  • Output: jae.striperesponses
  • Function Call: jsonb_array_elements((payments.pdoc -> 'stripeResponses'::text))
  • Worker 0: actual time=0.028..0.028 rows=1 loops=110220
  • Worker 1: actual time=0.028..0.028 rows=1 loops=112264
  • Worker 2: actual time=0.027..0.027 rows=1 loops=114358
Planning time : 0.339 ms