explain.depesz.com

PostgreSQL's explain analyze made readable

Result: z0d

Settings
# exclusive inclusive rows x rows loops node
1. 0.601 0.832 ↑ 1.0 1 1

Nested Loop (cost=19.39..25.23 rows=1 width=32) (actual time=0.825..0.832 rows=1 loops=1)

  • Output: row_to_json(ROW((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), ((stripe.striperesponses -> 'source'::text) -> 'account_holder_name'::text), ((stripe.striperesponses -> 'outcome'::text) -> 'reason'::text), (stripe.striperesponses -> 'status'::text), (payments.pdoc -> 'amountToPay'::text), (payments.pdoc -> 'paymentMethodType'::text), (payments.pdoc -> 'paymentMethodName'::text), ((stripe.striperesponses -> 'source'::text) -> 'account_use'::text), (payments.pdoc -> 'insertDtTm'::text), (payments.pdoc -> 'description'::text)))
2. 0.006 0.213 ↑ 1.0 1 1

Hash Join (cost=18.97..22.49 rows=1 width=32) (actual time=0.207..0.213 rows=1 loops=1)

  • Output: stripe.striperesponses
  • Inner Unique: true
  • Hash Cond: (((stripe.created)::double precision) = (max((((jae.striperesponses -> 'created'::text)))::double precision)))
3. 0.137 0.137 ↑ 33.3 3 1

CTE Scan on stripe (cost=8.72..10.97 rows=100 width=168) (actual time=0.133..0.137 rows=3 loops=1)

  • Output: NULL::jsonb, NULL::character varying, NULL::jsonb, NULL::tsvector, stripe.striperesponses, (stripe.created)::double precision
4.          

CTE stripe

5. 0.027 0.123 ↑ 33.3 3 1

Sort (cost=8.47..8.72 rows=100 width=224) (actual time=0.122..0.123 rows=3 loops=1)

  • Output: ((jae_1.striperesponses -> 'created'::text)), payments_2._id, payments_2.pdoc, payments_2.tsv, jae_1.striperesponses, ((jae_1.striperesponses -> 'created'::text))
  • Sort Key: ((jae_1.striperesponses -> 'created'::text)) DESC
  • Sort Method: quicksort Memory: 37kB
6. 0.009 0.096 ↑ 33.3 3 1

Nested Loop (cost=0.43..5.15 rows=100 width=224) (actual time=0.092..0.096 rows=3 loops=1)

  • Output: (jae_1.striperesponses -> 'created'::text), payments_2._id, payments_2.pdoc, payments_2.tsv, jae_1.striperesponses, (jae_1.striperesponses -> 'created'::text)
7. 0.033 0.033 ↑ 1.0 1 1

Index Scan using payments_pkey on ppxtest.payments payments_2 (cost=0.42..2.64 rows=1 width=128) (actual time=0.033..0.033 rows=1 loops=1)

  • Output: payments_2._id, payments_2.pdoc, payments_2.tsv
  • Index Cond: ((payments_2._id)::text = '5ca4e0b84b566a72dd306f693'::text)
8. 0.054 0.054 ↑ 33.3 3 1

Function Scan on pg_catalog.jsonb_array_elements jae_1 (cost=0.01..1.00 rows=100 width=32) (actual time=0.054..0.054 rows=3 loops=1)

  • Output: jae_1.striperesponses
  • Function Call: jsonb_array_elements((payments_2.pdoc -> 'stripeResponses'::text))
9. 0.002 0.070 ↑ 1.0 1 1

Hash (cost=10.24..10.24 rows=1 width=8) (actual time=0.070..0.070 rows=1 loops=1)

  • Output: (max((((jae.striperesponses -> 'created'::text)))::double precision))
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.007 0.068 ↑ 1.0 1 1

Aggregate (cost=10.22..10.23 rows=1 width=8) (actual time=0.068..0.068 rows=1 loops=1)

  • Output: max((((jae.striperesponses -> 'created'::text)))::double precision)
11. 0.005 0.061 ↑ 33.3 3 1

Sort (cost=8.47..8.72 rows=100 width=192) (actual time=0.060..0.061 rows=3 loops=1)

  • Output: ((jae.striperesponses -> 'created'::text)), NULL::character varying, NULL::jsonb, NULL::tsvector, NULL::jsonb, ((jae.striperesponses -> 'created'::text))
  • Sort Key: ((jae.striperesponses -> 'created'::text)) DESC
  • Sort Method: quicksort Memory: 25kB
12. 0.004 0.056 ↑ 33.3 3 1

Nested Loop (cost=0.43..5.15 rows=100 width=192) (actual time=0.053..0.056 rows=3 loops=1)

  • Output: (jae.striperesponses -> 'created'::text), NULL::character varying, NULL::jsonb, NULL::tsvector, NULL::jsonb, (jae.striperesponses -> 'created'::text)
13. 0.011 0.011 ↑ 1.0 1 1

Index Scan using payments_pkey on ppxtest.payments payments_1 (cost=0.42..2.64 rows=1 width=70) (actual time=0.010..0.011 rows=1 loops=1)

  • Output: payments_1._id, payments_1.pdoc, payments_1.tsv
  • Index Cond: ((payments_1._id)::text = '5ca4e0b84b566a72dd306f693'::text)
14. 0.041 0.041 ↑ 33.3 3 1

Function Scan on pg_catalog.jsonb_array_elements jae (cost=0.01..1.00 rows=100 width=32) (actual time=0.040..0.041 rows=3 loops=1)

  • Output: jae.striperesponses
  • Function Call: jsonb_array_elements((payments_1.pdoc -> 'stripeResponses'::text))
15. 0.018 0.018 ↑ 1.0 1 1

Index Scan using payments_pkey on ppxtest.payments (cost=0.42..2.64 rows=1 width=70) (actual time=0.017..0.018 rows=1 loops=1)

  • Output: payments._id, payments.pdoc, payments.tsv
  • Index Cond: ((payments._id)::text = '5ca4e0b84b566a72dd306f693'::text)