explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m7mU

Settings
# exclusive inclusive rows x rows loops node
1. 13,317.574 141,526.035 ↓ 1.9 911,947 1

Subquery Scan on t (cost=144,036.82..207,628.46 rows=480,900 width=32) (actual time=126,070.434..141,526.035 rows=911,947 loops=1)

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

Gather Merge (cost=144,036.82..201,617.21 rows=480,900 width=896) (actual time=126,070.377..128,208.461 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) -> 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)), ((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,489.465 631,427.950 ↓ 1.5 182,389 5

Sort (cost=143,036.77..143,337.33 rows=120,225 width=896) (actual time=126,014.290..126,285.590 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) -> 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)), ((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: 64736kB
  • Worker 0: Sort Method: external merge Disk: 66632kB
  • Worker 1: Sort Method: external merge Disk: 60552kB
  • Worker 2: Sort Method: external merge Disk: 67088kB
  • Worker 3: Sort Method: external merge Disk: 84040kB
  • Worker 0: actual time=126042.376..126318.203 rows=177134 loops=1
  • Worker 1: actual time=125856.534..126086.443 rows=160971 loops=1
  • Worker 2: actual time=126061.023..126359.501 rows=178315 loops=1
  • Worker 3: actual time=126064.102..126376.038 rows=223428 loops=1
4. 563,363.932 618,938.485 ↓ 1.5 182,389 5

Nested Loop (cost=0.01..49,855.25 rows=120,225 width=896) (actual time=1.210..123,787.697 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) -> 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), (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.319..123799.920 rows=177134 loops=1
  • Worker 1: actual time=1.156..123819.805 rows=160971 loops=1
  • Worker 2: actual time=1.170..123776.057 rows=178315 loops=1
  • Worker 3: actual time=1.598..123747.126 rows=223428 loops=1
5. 27,301.685 27,301.685 ↓ 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.189..5,460.337 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.269..5469.105 rows=177144 loops=1
  • Worker 1: actual time=0.208..5439.843 rows=160991 loops=1
  • Worker 2: actual time=0.212..5475.831 rows=178330 loops=1
  • Worker 3: actual time=0.219..5613.423 rows=223441 loops=1
6. 28,272.868 28,272.868 ↑ 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.031 rows=1 loops=912,028)

  • Output: jae.striperesponses
  • Function Call: jsonb_array_elements((payments.pdoc -> 'stripeResponses'::text))
  • Worker 0: actual time=0.032..0.032 rows=1 loops=177144
  • Worker 1: actual time=0.035..0.035 rows=1 loops=160991
  • Worker 2: actual time=0.032..0.032 rows=1 loops=178330
  • Worker 3: actual time=0.026..0.026 rows=1 loops=223441